在SQL Server的查詢分析器中使用命令匯入Excel表格中的資料

excel檔案中有一時間列，混合了文本與時間，如 傍晚 淩晨 12：15 其中 hh:mm居多，使用DTSWizard匯入雖然方便但是hh:mm都變成了NULL，在匯入設定中設定匯入資料類型不能實現。

需要語句實現，下面的語句都可以實現：

 

Microsoft Jet 資料庫引擎可以通過可安裝的索引循序存取方法 (ISAM) 驅動程式，訪問格式為其他資料庫檔案（例如 Excel 活頁簿）的資料。要開啟 Microsoft Jet 4.0 OLE DB 提供者所支援的外部格式，請在串連的擴充屬性中指定資料庫類型。Jet OLE DB 提供者對於 Microsoft Excel 活頁簿支援下列資料庫類型：

Excel 3.0
Excel 4.0
Excel 5.0
Excel 8.0

注意：對於 Microsoft Excel 5.0 和 7.0 (95) 活頁簿，請使用 Excel 5.0 來源資料庫類型；對於 Microsoft Excel 8.0 (97)、9.0 (2000) 和 10.0 (2002) 活頁簿，請使用 Excel 8.0 來源資料庫類型。本文中的樣本使用的是格式為 Excel 2000 和 Excel 2002 的 Excel 活頁簿。 

代碼

1.

SELECT *  into newtable1

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="C:\yyyy_wind.xls";

User ID=;Password=;Extended properties="Excel 8.0;HDR=YES;IMEX=1"')...sheet1$ 

2.

SELECT *   into newtable2

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\yyyy_wind.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"')...sheet1$

3.

SELECT *  into newtable3

 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\yyyy_wind.xls;HDR=YES;IMEX=1', sheet1$ )

 

 

這sheet指的是excel預設的sheet1，可以修改，檔案路徑取實際路徑。

網上其實好多資料，但是由於使用OpenDataSource和OPENROWSET都可以實現，又加上雙引號" "單引號''混用，難以搞清楚。關鍵問題是這樣的使用OpenDataSource時，Extended Properties的內容分號間隔並用雙引號括起來，sheet1$ 在括弧外，OPENROWSET相對簡潔.

下面介紹HDR和IMEX兩個參數

HDR為YES,excel檔案的首行作為sqlserver表的列名，為NO則作表的第一行。

IMEX＝1 意思是 tells the driver to always read "intermixed" data columns as text

在這裡driver的意思是excel的驅動程式。

Excel 驅動程式讀取指定源中一定數量的行（預設情況下為 8 行）以推測每列的資料類型。如果推測出列可能包含混合資料類型（尤其是混合了文本資料的數值資料時），驅動程式將決定採用佔多數的資料類型，並對包含其他類型資料的單元返回空值。（如果各種資料類型的數量相當，則採用數實值型別。）

Excel 工作表中大部分儲存格格式設定選項不會影響此資料類型判斷。（有說在寫入excel表時就全部設定為文本，可以，囧！）

SQLSERVER2005由於安全性的提高，直接使用上述語句還是有問題的（2000可以直接用）。

按提示進行如下操作：

從開始菜單進入到 組態工具 > sql server 2005 介面區配置器  >  服務和串連的外圍配置器 >  Datebase Engine 啟動服務，然後重啟sqlserver服務。由於sql server 2005沒有了2000那樣工作列右下角的圖表，所以需要通過控制台進入管理工具 >  服務 停止 後啟動 sqlserver 服務。  

然後運行 regedit 進入登錄編輯程式 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0 建立DWORD  名為DisallowAdhocAccess 值為0.