在Excel中,我們時常會碰到這樣的欄位(最常見的就是電話號碼),即有純數字的(如沒有帶區號的電話號碼),又有數字和其它字元混合 (如“區號-電 話號碼”)的數據,在導入SQLServer過程中,會發現要麼純數字的數據導過去之後變成了NULL,要麼就是數字和其它字元混合的數據導過去之後變成 了NUL ...
在Excel中,我們時常會碰到這樣的欄位(最常見的就是電話號碼),即有純數字的(如沒有帶區號的電話號碼),又有數字和其它字元混合 (如“區號-電
話號碼”)的數據,在導入SQLServer過程中,會發現要麼純數字的數據導過去之後變成了NULL,要麼就是數字和其它字元混合的數據導過去之後變成
了NULL。
我首先想到的就是將這個欄位的所有數據在Excel中設置為文本格式,剛纔說了本來就是希望導入SQLServer時成為字元型,但結果令人失望,不起
作用。
最終網上搜索到了答案:混合數據類型列的強制解析——IMEX=1
使用 IMEX=1 選參之後,只要取樣數據里是混合數據類型的列,一律強制解析為 nvarchar/ntext 文本。當然,IMEX=1 對單一數據類型列的解析是不影
響的。
SELECT * INTO Table08
FROM OpenDataSource
('Microsoft.Jet.OLEDB.4.0','Data Source="E:/1.xls";Extended properties="Excel 5.0;HDR=Yes;IMEX=1;"')...[Sheet1$]
註:
1.這條語句是在SQLServer查詢分析器中執行,並且要選擇好資料庫,否則會把要導入的數據往別的資料庫中導了。
2.Table08是數據導入後在SQLServer中的表名,屬於新建,所以請確認在導入數據前資料庫中沒有該表名,否則會提示已存在同一表名。
3.Data Source,不要連在一起寫,中間有一空格。
4.E:/1.xls,為Excel所在的絕對路徑和資料庫名。
5.Excel 5.0,根據不同的Excel版本寫5.0或8.0或其它。
6.Microsoft.Jet.OLEDB.4.0,根據不同的office版本寫4.0或12.0或其它。
7.IMEX=1,是轉換成文本輸入的意思,非常重要,如果沒有,就跟你直接導入效果一樣。
8.Sheet1是表名,千萬別看到語句中有$就在表名後加上$,因為$是語句要加的,別畫蛇添足。
上述語句執行後,可能會報出以下錯誤:
SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的訪問,因為此組件已作為此伺服器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc Distributed Queries'。有關啟用 'Ad Hoc Distributed Queries' 的詳細信息,請參閱 SQL Server 聯機叢書中的 "外圍應用配置器"。
1.開啟Ad Hoc Distributed Queries組件,在sql查詢編輯器中執行如下語句:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
如需關閉則參數1改為0