搜尋本站文章

2008-01-03

SSIS 2005 與 Excel 2007 (*.xlsx , *.xlsb)




2010/07/10 更新:
在「Microsoft Office 12.0 Access Database Engine OLE DB Provider」設定視窗,
在「全部」頁面,點選「Extended Properties」,點選「編輯內容值」,請依據附檔名來填寫:

*.xlsx -- > 填入 Excel 12.0 xml
*.xlsb -- > 填入 Excel 12.0




在使用 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