Excel資料匯入Sql Server出現Null

     今天給客戶匯入資料，發現有一列出現很多為null的資料。在使用Sql Server資料匯入的時候，資料類型預設為了float，怎麼都不能改變其類型。只要求助萬能的網路，於是

用google搜到一篇：http://hi.baidu.com/qwwwwqwwww/blog/item/7fa251088eea7ada63d98660.html

         在Excel中，我們時常會碰到這樣的欄位(最常見的就是電話號碼)，即有純數位(如沒有帶區號的電話號碼)，又有數字和其它字元混合 (如“區號-電話號碼”)的資料，在匯入SQLServer過程中，會發現要麼純數位資料導過去之後變成了NULL，要麼就是數字和其它字元混合的資料導過去之後變成了NULL。

為什麼有些是純數位資料導過去之後變成了NULL，有些卻是數字和其它字元混合的資料導過去之後變成了NULL，原來是在將Excel資料匯入

SQLServer過程中，SQLServer會做出判斷，是採用float型還是nvarchar型來接受資料，測試發現(沒有科學依據)，SQLServer採用哪一型取決於將要匯入

的資料中本身具有哪一型的記錄數比例多，如10筆資料，有4筆沒有帶區號的電話號碼，6筆是帶區號的電話號碼，那麼轉到SQLServer就會選擇

nvarchar型，結果就是4筆沒有帶區號的電話號碼導過去之後全成了NULL，反之亦然。不管怎麼樣，我們最終都希望SQLServer是採用nvarchar來接受

資料，畢意我們要匯入的資料中有數字和其它字元混合的資料，用float型來接受是不可能的，這樣只要我們解決了將純數位資料轉換成字元型並讓

SQLServer接受就可以了。

    我首先想到的就是將這個欄位的所有資料在Excel中設定為文字格式設定，剛才說了本來就是希望匯入SQLServer時成為字元型，但結果令人失望，不起作用。

    最終網上搜尋到了答案：混合資料類型列的強制解析——IMEX=1
使用 IMEX=1 選參之後，只要取樣資料裡是混合資料類型的列，一律強制解析為 nvarchar/ntext 文本。當然，IMEX=1 對單一資料類型列的解析是不影響的。

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驅動的這種機制還是讓這列的資料變成空。

select * INTO Table08 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=E:\1.xls;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.IMEX=1，是轉換成文本輸入的意思，非常重要，如果沒有，就跟你直接匯入效果一樣。
    7.Sheet1是表名，千萬別看到語句中有$就在表名後加上$，因為$是語句要加的，別畫蛇添足。

如果出現了錯誤：
    SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此伺服器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用 'Ad Hoc Distributed Queries'。有關啟用 'Ad Hoc Distributed Queries' 的詳細資料，請參閱 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

還有一種比較麻煩的問題就是excel的科學計數法。網上找到瞭解決辦法，

經常碰到的情況是在表格裡面輸入手機號碼、社會安全號碼等等比較長的數字串時，Excel卻自作聰明的將其以科學計數的方式來顯示，更麻煩的是，本想設定以文本顯示，但設定後，還是一成不變。而且即便以文本方式複製，卻將科學計數中的E 也複製過去。想要正常顯示，則需要重新雙擊這個儲存格，或請按F2後再請按斷行符號鍵，才能以真面目示人。實在麻煩！

Excel裡面如何一次性取消所有科學計數顯示方式？

一、一個比較方便的臨時解決辦法是用分列功能。

以Excel2007操作：
1、選擇想要轉換的儲存格，設定屬性為文字格式設定（此步驟可忽略，不過建議操作一次）
2、用Excel選擇一列數字（好像只能是選擇一列），選擇資料--分列--下一步--下一步--選擇文本--完成。
操作後，基本上已經達到目的，不過如果您有些數字串是以0開頭的，可能會被Excel自以為是的去掉了，這種情況需要自己手動重新添加了，至於如何更高效率的添加完畢，可用EditPlus等功能超強的文字編輯器實現。
二、還有一個更快的，設定儲存格屬性類型為0。比如已在單元A1：A100輸入了號碼，請按以下步驟做：選擇單元A1：A100》單擊滑鼠右鍵，設定儲存格式》選擇自訂，在類型中輸入0即可，輕鬆搞定，呵呵，當然這種方式同樣會丟失以0開頭的數字串！

以上均是我搜尋到的相關的結果，但其實用了最後一個去掉科學計數法的方法，我發現這個才是最簡單而有效方法，這樣處理過後，數字都當字串處理了，也就是說，根本就不需要最上邊的修改註冊表，混合資料類型列的強制解析之類的操作。但是為什麼全部選中修改成文字格式設定，不起作用呢？？這個恐怕就是微軟的設計了，不能說是問題，但肯定操作不方便。通過選擇資料--分列--下一步--下一步--選擇文本--完成，這樣的步驟才能得到我們想要的結果。這樣處理過後，不管是什麼數字都是文本，也不會出現科學技術法，而且處理過後，可以直接用sql匯入，也不需要寫sql語句。

 

      我使用第二個方法解決了問題。感謝！