要匯入一個內含分號(;)相隔的文字檔
在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
訂閱:
文章 (Atom)
publish error allowDefinition='MachineToApplication'
一個老舊的aspx web form專案,調了一些功能建置成功,但進行部署時顯示以下錯誤。 在應用程式層級之外使用註冊為 allowDefinition='MachineToApplication' 的區段發生錯誤。錯誤的原因可能是虛擬目錄尚未在 IIS 中設定為...
-
上網找了免費的白箱檢測工具,找到了 puma scan 因為支援OWASP TOP 10的檢測,所以這是一個符合客戶需求又能與Visual studio結合的工具(白話說...交差了事矣....) 更重要的是這不用錢....檢測工具都貴森森的,尤其是源碼檢測工具。 ht...
-
在一台windows 2016的主機上,某一天,用遠端登入後,出現了以下的訊息.... 遠端工作階段中斷,因為沒有提供授權的遠端桌面授權伺服器可以使用。請連伺服器系統管理員。 這啥啊........... 想不到,在MOBILE01找到了解決...........哈哈...
-
SQL Server 2005 後,提供EXCEP 和INTERSECT, 功能和ORACLE的MINUS, INTERSECT相同 EXCEP 找出存在於第一個子集,但不存在於第二個子集的筆數 INTERSECT 找出皆存在於二個子集中的筆數 這二個方法很方便查找有多...