2014年6月11日 星期三

sql server openrowset 匯入文字檔

要匯入一個內含分號(;)相隔的文字檔
在sql server要匯入文字檔或EXCEL檔當然以openrowset為首選的解決方法
可以使用以下二種方式匯入

--適用在32bit SQL SERVER
select * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};
 DefaultDir=C:\TEMP;','select * from test_1030612.txt')  


適用在32/64 bit SQL SERVER,
但要安裝Microsoft Access Database Engine 2010 可轉散發套件

select *  from OpenRowset( 'Microsoft.ACE.OLEDB.12.0','Text;Database=C:\TEMP;',
 'SELECT * FROM test_1030612.txt' )


過程中,試了多次 ,整理一個比較可行的方法

因為文字檔附檔名是依日期命名,例:test.1030612,openrowset不接受這種副檔名,執行後會有以下錯誤
Msg 7357, Level 16, State 2, Line 1
無法處理物件 "SELECT * FROM
test.1030612"。連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 指出物件沒有資料行,或是目前的使用者沒有使用該物件的權限。

故在讀取前要先將檔案改名為 test_1030612.txt  (試改成 test.1030612.txt 也不行...)
檔名更改後,還得要配合Schema.ini的定義(如果是以逗號(,)相隔的文字檔就不用這個檔案設定)

Schema.ini的內容如下,且必需與文字檔案在相同目錄,第一行的檔名一定要和文字檔名一樣,第二行則是設定檔案是否有表頭,第三行則是設定Delimited符號 ,第四行就看是否要加什麼屬性設定,例如檔案內容是unicode,就可以加入CharacterSet=65001

[test_1030612.txt]
ColNameHeader=False
Format=Delimited(;) 


1.逗號隔開的文字檔不需schema.ini存在,若要忽略表頭,則需在openrowset加入HDR=No; 屬性設定
2.非逗號隔開的文字檔都要有Schema.ini設定,而且皆以ini內的屬性設定為主

最後寫一段程式來自動化轉入
  
declare @cmd nvarchar(500),@filename nvarchar(50),@folder nvarchar(20)
declare @tab table (id int identity(1,1), myfile nvarchar(256)) 
declare @sql nvarchar(4000)
set @folder =  'c:\temp\'  --要讀取的目錄
set @cmd = 'dir ' + @folder + 'test.*'
insert into @tab exec master..xp_cmdshell @cmd  --目錄下要找的檔名


declare curfile cursor for
select substring(myfile,40,256)      
  from @tab
 where myfile not like '%DIR%' and myfile not like ('%個檔案%')
   and myfile not like ('%個目錄%')
   and myfile not like ('%磁碟區%')
   and myfile not like ('%的目錄%')  
   and myfile not like '%.ini%'
open curfile
fetch next from curfile into @filename
while @@fetch_status =0
begin  
    --先換檔名
     set @cmd = 'rename ' + @folder+@filename +  ' ' + replace(@filename,'.','_')+'.txt'
     exec master..xp_cmdshell  @cmd
   
     --產生Schema.ini
     set @filename = replace(@filename,'.','_')+'.txt'
     set @cmd = 'echo [' + @filename+ '] > '+ @folder+'\Schema.ini'
     exec master..xp_cmdshell @cmd
     set @cmd =  'echo ColNameHeader=False >> '+ @folder+'\Schema.ini'
     exec master..xp_cmdshell @cmd
     set @cmd =   'echo Format=Delimited(;) >> '+ @folder+'\Schema.ini'
     exec master..xp_cmdshell @cmd
    

     --openrowset讀檔
     set @sql = N'select *  from OpenRowset(''Microsoft.ACE.OLEDB.12.0'',
     ''Text;Database='+ @folder+';'',
     ''select * from  '+@filename+''') '
     exec sp_executesql @sql


    fetch next from curfile into @filename
end
close curfile
deallocate curfile
 


publish error allowDefinition='MachineToApplication'

一個老舊的aspx web form專案,調了一些功能建置成功,但進行部署時顯示以下錯誤。 在應用程式層級之外使用註冊為 allowDefinition='MachineToApplication' 的區段發生錯誤。錯誤的原因可能是虛擬目錄尚未在 IIS 中設定為...