Excel 匯入 SQL Server 步驟:

iteye_20954發表於2011-12-19
ExcelSQLServer

1. 在登錄檔中按照以下方法設定 Excel 的文字長度。如果不設定， 會導致列中超過255個字元的記錄被截斷；

如果是:Jet引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

如果是:ACE引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
然後找到 TypeGuessRows這個項.預設為8的.修改為0.


2. 在SQL Server中啟用：

--使用前，啟用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--使用完成後，關閉Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure


3. 將Excel 中的檔案插入到 SQL Server 中

--直接查詢Excel
SELECT * into TempTable FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Excel 8.0;HDR=YES;DATABASE=d:\1.xls ',sheet1$)

--HDR=YES 有兩個值:YES/NO,表示第一行是否欄位名,預設是YES,第一行是欄位名
--IMEX=1 解決數字與字元混合時,識別不正常的情況.

值得注意的是:

1. 記住：如果你在Excel中是 sheet1 , 在上面的語句中還是得寫 sheet1$

2. 如果你直接複製某段程式碼過去執行不了, 很可能是中間多了空格什麼的, 有些網站複製過來會多些空格。

3. 還是在本地連線執行查詢xls吧， 外部連線查詢xls可能會有錯誤。

4. 如果你沒有關閉Excel就來查詢xls， 會報以下錯誤：

Msg 7399, Level 16, State 1, Line 1
連結伺服器 "(null)" 的 OLE DB 訪問介面 "MICROSOFT.JET.OLEDB.4.0" 報錯。提供程式未給出有關錯誤的任何資訊。
Msg 7303, Level 16, State 1, Line 1
無法初始化連結伺服器 "(null)" 的 OLE DB 訪問介面 "MICROSOFT.JET.OLEDB.4.0" 的資料來源物件。


4. 雖然採取了這麼多措施， 但還是要防止出現截斷， 如果資料量少於1000條，手工檢查一下全部資料（看一下頭和尾就好）。如果資料量大，至少要檢視LEN大於255的所有記錄。


5. 將臨時表的的欄位去掉回車，換行，Tab鍵, 左右空格。

UPDATE Table1 SET ICD=LTRIM(RTRIM(replace(replace(replace(CONVERT(VARCHAR(max),ICD),char(10),''),char(13),''),CHAR(9),'')))


6. 更新資料表。手工查檢， 如果資料量不超過1000條， 應該每一條都測試；


7. 主要的幾點容易出錯的地方：


  1. 在初次測試時， 應該測試所有的較短的單條資料， 對於較長的資料， 測試最尾一條和中間一條Code即可
  2. 有些資料需要後補0， 如： 995 ==> 995.0
  3. 有些資料需要前補0, 如： 78.11 ==> 078.11
  4. 有些資料中間有空格, 如：7945 V77.0， 其實應該是兩條資料， 應該用逗號讓兩者分開。
  5. 必需測試每一條資料。

相關文章