要匯入一個內含分號(;)相隔的文字檔
在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
