SqlServer–讀取Excel

如何使用Sql讀取Excel2003?

具體例子如下:

如何讀取下面這個Excel?

此表的路徑為:d:\zl\student.xls

其中的活頁簿為info

表格式如下:

使用Sql讀取如下:

select *from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\zl\student.xls','select * from [info$]')

--注意項:

--1.excel處於關閉狀態,即不能處於被開啟狀態

--2.excel檔案所處路徑及檔案名稱、活頁簿的名稱不要出現漢字，盡量以英文命名

--3.注意'select * from [info$]',活頁簿名後的$是必須的.

結果:

為什麼為出現一行null值呢?還出現了列f5,列f6?

原因是出現了合併儲存格,不僅有列方面的合併儲存格,還有行方面的合併儲存格.

但是,我們仍然可以通過where子句的判斷,讀取到需要的資料.

如下:

select 姓名,年齡,班級,成績 as 語文成績,F5 as 數學成績,F6 as 英語成績from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\zl\student.xls','select * from [info$]')where 姓名 is not null

結果:

這正是我們期待的結果.但此方法只能針對這樣一個表,如果其它表的格式和這個表類似,但是卻略有不同時,該如何處理呢?再寫一套sql指令碼?

不太現實,因為我們並不僅僅是讀取excel,還有其他動作,例如,將excel中的資料進行轉換後,再匯入sql.

比較好的方法是準備一個excel模版,將資料盡量複製至該模版中,我們只需按照這個模版,寫一套可以操作該表的sql語句即可.

設定excel模版如下:

然後將表中資料,複製進該表.

再次讀取該表:

select 姓名,年齡,班級,語文成績,數學成績,英語成績from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\zl\student.xls','select * from [info$]')

--建議,盡量讀取列名

結果:

 

時會如下錯誤:

伺服器: 訊息 7399，層級 16，狀態 1，行 1
OLE DB 提供者 'Microsoft.Jet.OLEDB.4.0' 報錯。提供者未給出有關錯誤的任何資訊。
OLE DB 錯誤跟蹤［OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:  提供者未給出有關錯誤的任何資訊。］。

請檢查文章開始處提到的注意項.

 

補充:

讀取Excel時,可能會遇到以下問題:

當列中存在數字行和字串行時,會遇到有一種讀出為null的情況,該如何處理呢?

有兩種解決方案:

第一種方法:在數字行的資料前加'

第二種方法:在連接字串中加入:IMEX=1,如下:

select 姓名,年齡,班級,語文成績,數學成績,英語成績from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=d:\zl\student.xls;IMEX=1','select * from [info$]')

但是,第二種方法不是萬能的.當前八行資料均為數字或字串時,後面再有其他類型資料時,仍然讀不出來.

引用解釋如下:

IMEX是用來告訴驅動程式使用Excel檔案的模式，其值有0、1、2三種，分別代表匯出、匯入、混合模式。當我們設定IMEX＝1時將強制混合資料轉換為文本，

但僅僅這種設定並不可靠，IMEX＝1隻確保在某列前8行資料至少有一個是文本項的時候才起作用，它只是把尋找前8行資料中資料類型佔優選擇的行為作了

略微的改變。例如某列前8行資料全為純數字，那麼它仍然以數字類型作為該列的資料類型，隨後行裡的含有文本的資料仍然變空。 

另一個改進的措施是IMEX＝1與註冊表值TypeGuessRows配合使用，TypeGuessRows 值決定了ISAM 驅動程式從前幾條資料採樣確定資料類型，

預設為“8”。可以通過修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的該註冊表值來更改採樣行數。

但是這種改進還是沒有根本上解決問題，即使我們把IMEX設為“1”， TypeGuessRows設得再大，例如1000，假設資料表有1001行，

某列前1000行全為純數字，該列的第1001行又是一個文本，ISAM驅動的這種機制還是讓這列的資料變成空。