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