麥克林的技術隨筆小站: Excel 資料筆數超過 65536 匯入 MS SQL 之方式

在使用 Excel 2003(*.xls)時，有數項條件限制，例如：

最大資料列筆數是 65,536 列。Excel 匯出選項會檢查此數值，若超過此限制，會在 Excel 中顯示錯誤。
最大資料行數是 256 行。Excel 匯出選項會檢查此數值，若超過此限制，會在 Excel 中顯示錯誤。

要解決此問題，可能有幾個方法：

當資料筆數會超過 65,536 筆數時，採用新增工作表的方式，讓每個工作表的資料列筆數，不會超過 65,536 筆數的軟體限制。
改用 Excel 2007 ，存檔成 *.xlsx 的檔案格式，也可以解決此問題。
將資料匯出成 *.csv 檔案格式，分隔符號選擇為：[逗號{,}]，再利用 Excel 2007 開啟存取，也可以正常運作。

以下是使用 SSIS ，將資料匯入到 Excel 2003(*.xls)所遇到錯誤：

Excel 2003(*.xls)至多僅能達到 65,536 列。

改用新版本的 Excel 2007(*.xlsx) 也可以改善這些問題，舉例來說：

可支援到 1,048,576 筆資料列。
可支援到 16,384 資料行。
Excel 2007 讓試算表的容量擴充到超過 1 百萬資料列和 1 萬 6 千資料行。

在 Integration Services 中，要與 Excel 2007 連線時，可能要注意以下幾件事情：

Excel 2007 與 Excel 2003 兩個版本的檔案格式不同，這可能表示我們無法再使用[Microsoft Jet 4.0 OLE DB Provider]來與 Excel 2007 進行連線。
在 Microsoft Office 2007 中，發行了新版本的 OLD DB 驅動程式：[Microsoft Office 12.0 Access Database Engine OLE DB Provider]，縮寫為 ACE。

新版本的 ACE 驅動程式，可以稍作屬性的調整，就可以與 Excel 2007 及 Access 2007 連線，請各位參考以下的整理：

在[SQL Server 匯入和匯出精靈]中，若是要使用 Excel 2007 作來資料來源或是目的地，則需要利用[Microsoft Office 12.0 Access Database Engine OLE DB Provider]，點選[屬性]，在[資料連結內容]視窗中，點選[全部]頁面，點選[Extended Properties]，輸入以下的資料：Excel 12.0 。
在設計 SSIS 封裝時，若是要使用 Excel 2007 作來資料來源或是目的地，需要使用[OLE DB 連接管理員]，然後在[提供者]畫面，選擇[Microsoft Office 12.0 Access Database Engine OLE DB Provider]，請記得，一樣需要設定[Extended Properties]的內容值為：Excel 12.0。
至於[Excel 連接管理員]、[Microsoft Jet 4.0 OLE DB Provider]這類的資料庫驅動程式，則仍然適用於與 Excel 2003 (*.xls)進行連線。

筆者提供與利用[SQL Server 匯入和匯出精靈]與 Excel 2007 連線的設定步驟：

0. 請事先利用 Excel 2007 在 C:\ 中，建立 Ex01.xlsx 的檔案。

1. 在資料來源或是目的地的驅動程式部分，選擇[Microsoft Office 12.0 Access Database Engine OLE DB Provider]。

2. 點選[屬性]，在[資料連結內容]畫面，點選[資料來源]，輸入：C:\Ex01.xlsx。

3. 點選[全部]頁面，點選[Extended Properties]，點選[編輯內容值]，輸入以下的資料：Excel 12.0。

4. 再點選回[連線]頁面，點選[測試連線]，正常應該可以看到測試連線成功的訊息，請按下[確定]\[確定]，完成與 Excel 2007 的連線設定，按[下一步]。

若是沒有事先建立 Excel 2007(*.xlsx)檔案時：

如果您的需求是讓 SSIS 於執行時，當下由系統動態創建此 *.xlsx 檔案，無須事先建立此 *.xlsx 檔案。
請將輸入的副檔名修改為 *.xlsb 檔案格式，就可以讓系統自行動態建立此檔案，也能夠正常開啟運作。

Excel 2007 的檔案格式

Excel 2007 支援多種的檔案格式，依據預設值，副檔名是 *.xlsx，這是一種以 XML 為基礎的全新檔案格式，稱為 Microsoft Office Open XML Formats，具備了數項優點：壓縮檔案、改良的受損檔案復原、輕鬆偵測包含巨集的文件、對個人資訊提供更理想的隱私權保護與更嚴密的控管、更完善地整合並互通商業資料等等，檔案大小也會比(*.xls)來的更小；此外，由於檔案格式屬於 XML 架構格式，再加上開放、免權利金的授權，讓我們開發人員更容易就能建置與採用 Excel 2007 文件內容及中繼資料的解決方案。
Excel 2007 二進位檔案格式(BIFF12)，其副檔名是：*.xlsb，這是 Excel 2007 專屬的二進位檔案格式(BIFF12)；與先前的版本之二進位檔案格式，副檔名是：*.xls 是不相容的，例如：在 Excel 5.0/95 版本是(BIFF5)，在 Excel 97-2003 版本是(BIFF8)。

參考資料：

Excel 2007 中支援的檔案格式
http://office.microsoft.com/zh-tw/excel/HP100141031028.aspx

將 Office Excel 2007 與舊版 Excel 搭配使用
http://office.microsoft.com/zh-hk/excel/HA100775613076.aspx

新副檔名與 Office XML Formats 簡介
http://office.microsoft.com/zh-tw/help/HA100069351028.aspx

Excel 2007 的規格及限制
http://office.microsoft.com/zh-tw/excel/HP100738491028.aspx

Improving Performance in Excel 2007
http://msdn2.microsoft.com/zh-tw/library/aa730921.aspx#Office2007excelPerf_BigGridIncreasedLimitsExcel

無法匯入超過 65,536 列是文字檔到 Excel 97、 Excel 2000、 Excel 2002 和 Excel 2003
http://support.microsoft.com/kb/120596/

Summary of capability limitations in Excel 97
http://support.microsoft.com/kb/296053/en-us

匯出至 Excel (*.xls)的限制
http://technet.microsoft.com/zh-tw/library/aa337207.aspx

Limitations with Exporting to Excel(*.xls)
http://msdn2.microsoft.com/en-us/library/aa337207.aspx

資料來源：http://blog.csdn.net/Johnson_hejun/archive/2008/11/12/3284582.aspx