用SQL語句匯入excel資料

iSQlServer發表於2009-10-22
SQLExcel
今天，我的Team Leader讓load一些資料到資料庫中去，之前這樣的事情我也做過。沒有遇到過什麼錯誤，但是今天這個excel讓我吃了不少苦頭。經過我不懈努力，最終解決了所有問題，順利完成任務。下面我把我遇到的問題寫下來和大家探討一下。
一、問題提出
這個excel大概1W條資料，資料量不是很大，開始匯入也很順利。不到一分鐘就完成了，結果我發現有一列資料全部變成了null，並且其他列的資料格式也不正確。然後我就更改了每個列的資料型別，結果導致資料無法匯入。鬱悶！
二、問題深化
於是我想到用SQL語句去試一下，用下面的語句執行了一下。
SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;imex=1;Database=\\surrey-test\GS\GS_UNpaid.xls',
'SELECT * FROM [Sheet1$]')
結果出現，OLE DB 提供程式 'Microsoft.Jet.OLEDB.4.0' 不包含表 'Sheet1$'。該表可能不存在，或當前使用者沒有使用該表的許可權。
OLE DB 錯誤跟蹤［Non-interface error: OLE DB provider does not contain the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='Sheet1$'］。
於是，詳細思考了一下。哦，原來我的excel沒有放到Server上，放上去之後在此執行，資料查出來了。
三、設法解決
資料查出來之後格式依然不正確，不符合我們的要求，於是開始進行資料格式的轉換。開始的時候使用convert和cast把資料轉換為float型別，不行。於是再次轉換convert(float,convert(varchar(50),isnull(gs_guid,0)))，這次格式對了，但是資料卻由於float型別的精度問題而發生了改變，不能滿足要求。於是使用
left(cast(cast(convert(float,convert(varchar(20),confirmation_no)) as decimal(20,7)) as varchar(20)),9)，結果還是不能讓人滿意，資料失真了。苦思冥想，終於想到這條cast(cast(confirmation_no as decimal) as varchar)，Ok。問題解決，欣喜若狂。
四、檢查問題
就在我要Submit的時候，卻發現一個致命的問題，所有資料格式正確的同時，竟然有一列資料發生了很大變化，於是認真查詢，發現了問題的存在，對於這一列使用
cast(convert(bigint,convert(float,convert(varchar(50),isnull(gs_guid,0))))as varchar)，問題終於搞定。
五、問題解決
最後使用
  INSERT INTO temp4
select convert(char(4),car_no) as car_no,convert(datetime,[column name]) as pu_date,
cast(cast([column name] as decimal) as varchar),
--left(cast(cast(convert(float(5),convert(varchar(50),[column name])) as decimal(20,7)) as varchar(20)),10),
convert(decimal(12,2),[column name]),convert(char(4),dr_no),
cast(cast([column name]as decimal) as varchar),
cast(cast([column name] as decimal) as varchar),
--convert(float,convert(varchar(50),isnull([column name],0))),
cast(convert(bigint,convert(float,convert(varchar(50),isnull([column name],0))))as varchar),
cast(cast([column name]as decimal) as varchar),
--left(cast(cast(convert(float,convert(varchar(20),[column name])) as decimal(20,7)) as varchar(20)),10),
--left(cast(cast(convert(float,convert(varchar(20),[column name])) as decimal(20,7)) as varchar(20)),9),
--left(cast(cast( convert(float,convert(varchar(50),isnull([column name],0))) as decimal(20,7)) as varchar(20)),9),
--left(cast(convert(float,convert(char(20),[column name])) as varchar(20)),6),
convert(datetime,[column name]) ,isnull([column name],'')
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;imex=1;Database=\\surrey-test\GS\GS_UNpaid.xls',
'SELECT * FROM [gs_voucher_notpaid$]')
將資料load到Database中去。
六、總結
匯入資料雖然是件很簡單的事情，但是這裡面還是包含了很多知識。比如資料的儲存型別，資料庫中的一些常用函式，等等。希望，這些經驗能夠使我在專案中受益，同時也希望各位多多指點。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-617167/，如需轉載，請註明出處，否則將追究法律責任。

相關文章