搜尋本站文章

2012-02-04

Excel 有資料,但匯入到資料庫後卻是 NULL;設定登錄機碼 TypeGuessRows、連線字串 IMEX

檢視示範的 Excel 資料:

-- 01_Excel原始資料



-- 02_進一步檢視此儲存格,前面多了單引號_1



-- 03_進一步檢視此儲存格,前面多了單引號_2



在上圖中,可以觀察到儲存格:B3,其所存放的資料前面多了一個單引號(single quotation marks)。

Excel 資料內容的說明:

第一列是存放資料行的名稱。
第二列開始,是存放資料值。

其中,第一筆資料是數值,但第二筆資料的第二個資料行卻是多了單引號,資料類型是無法使用數值的資料類型。



示範環境:
1. Windows Server 2008 R2 x64。


以下使用數種程式,以此 Excel 檔案當做來源使用。

(一) 若使用 SSIS 的「Excel 來源」,搭配 Microsoft.Jet.OLEDB.4.0 資料提供者,連接到 *.xls

使用 Microsoft.Jet.OLEDB.4.0 資料提供者,附檔名是:xls。

-- 04_SSIS_Excel_來源



在上圖4 中,可以觀察到竟然是呈現 NULL 。


(二) 使用 OPENROWSET 資料表函數,搭配 Microsoft.ACE.OLEDB.12.0 資料提供者,連接到 *.xls

-- 05_OPENROWSET 函數_未使用 IMEX



在上圖5 中,可以觀察到竟然是呈現 NULL 。

(三) 使用 OPENDATASOURCE 函數,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xls

-- 06_OPENDATASOURCE 函數_未使用IMEX



在上圖6 中,可以觀察到竟然是呈現 NULL 。

(四) 使用「連結伺服器(Linked Server)」,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xls

-- 07_連結伺服器(Linked Server)_未使用IMEX



在上圖7 中,可以觀察到竟然是呈現 NULL 。

(五) 使用「OLE DB 來源」,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xlsx

使用 Microsoft.ACE.OLEDB.12.0 資料提供者,附檔名是:xlsx。

-- 08_OLE DB 來源_未使用IMEX






認識 Excel 資料提供者

依據不同的 Excel 版本,可以分成為:

(1) Excel 2003 版本(包含之前的版本)
使用的是:Microsoft.Jet.OLEDB,目前最新的版本是:Microsoft.Jet.OLEDB.4.0。

(2) Excel 2007、2010 版本
使用的是:Microsoft.ACE.OLEDB,目前最新的版本是:Microsoft.ACE.OLEDB.14.0。

上述的連線 Excel 資料提供者,預設:採取「自動偵測」的方式來識別資料值的資料類型。

認識登錄機碼:TypeGuessRows

登錄機碼:TypeGuessRows 是指要檢查資料類型的列數。
資料類型是依據所找到的資料種類最大值來決定。

如果其中有關係,資料類型依下列順序決定:數目、貨幣、日期、文字和布林值。

如果碰到的資料不符合猜測的欄資料類型,它會以 Null 值傳回。

匯入時,如果某一欄含有混合的資料類型,則整欄都會依 ImportMixedTypes 設定來轉換。
要檢查的列數預設值為 8。

也就是說,由 Excel 資料提供者去讀取登錄機碼:TypeGuessRows 的設定值。
此登錄機碼:TypeGuessRows 的預設值是:8。

也就是說,此 Excel 資料提供者會去掃描前 8 筆的資料列來,做作為判斷其資料類型。

TypeGuessRows 的有效範圍值是 0 到 16。

如果設定為 0,是表示掃描來源資料列的筆數是 16384。
請留意,使用 0 值,若資料檔又很大時,將造成效能的衝擊。


有關混合資料類型的討論

如前所述,系統必須推測 Excel 工作表或範圍中每一欄的資料類型 (這不會受 Excel 儲存格格式設定所影響)。

如果您在同一欄中混合使用數值和文字值,會發生嚴重的問題。

Jet 和 ODBC 提供者都會傳回主要類型的資料,但是次要資料類型則會傳回 NULL (空) 值。
如果同一欄中兩種類型混合使用的比例相同,提供者會選擇數值而非文字值。

例如:

(1) 在 8 個已掃描的列中,如果欄位中包含 5 個數值和 3 文字值,提供者會傳回 5 個數值和 3 個 Null 值。

(2) 在 8 個已掃描的列中,如果欄位中包含 3 個數值和 5 文字值,提供者會傳回 3 個 Null 值和 5 個文字值。

(3) 在 8 個已掃描的列中,如果欄位中包含 4 個數值和 4 文字值,提供者會傳回 4 個數值和 4 個 Null 值。


以下是此登錄機碼:TypeGuessRows 的路徑:

-- Microsoft.Jet.OLEDB.3.5,例如:Excel 97
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

-- Microsoft.Jet.OLEDB.4.0,例如:Excel 2000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

若是在 Windows Server 2008 R2 x64 平台上,登錄機碼:TypeGuessRows 的路徑:

-- 32位元,Microsoft.Jet.OLEDB.4.0 資料提供者
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

-- 32位元,Microsoft.ACE.OLEDB.14.0 資料提供者
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

-- 09_x64平台_登錄機碼:TypeGuessRows_.Jet.OLEDB.4.0_32位元版本



-- 10_x64平台_登錄機碼:TypeGuessRows_.ACE.OLEDB.14.0_32位元版本



若是在 x64 作業系統上,同時安裝了 32位元與64位元版本的 Microsoft.ACE.OLEDB 資料提供者

-- 32位元,Microsoft.ACE.OLEDB.12.0 資料提供者
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

-- 64位元,Microsoft.ACE.OLEDB.14.0 資料提供者
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

-- 11_x64平台_登錄機碼:TypeGuessRows_.ACE.OLEDB.12.0_32位元版本



-- 12_x64平台_登錄機碼:TypeGuessRows_.ACE.OLEDB.14.0_64位元版本



在上圖11、12中,可以看到是安裝了 32 位元版本的 ACE.OLEDB.12.0 資料提供者,以及 64 位元版本的 ACE.OLEDB.14.0 資料提供者。


認識登錄機碼:ImportMixedTypes

此機碼的預設值是:Text。
可以設定成 MajorityType 或 Text。

如果設成 MajorityType,混合資料類型的欄就會在匯入時轉換為主控資料類型。
如果設定成 Text,混合的資料類型就會在匯入時轉換為 Text。

以下整理了數個登錄機碼:

項目 描述
win32 msexcl40.dll 的位置。完整路徑於安裝時決定。
AppendBlankRows 新增新資料之前,附加至 3.5 版或 4.0 版工作表結尾的空白列數。例如,如果 AppendBlankRows 設成 4,Microsoft Jet 在附加內含資料的列之前,會先附加 4 個空白列至工作表的結尾。這項設定的整數值可以是 0 至 16 的數字;預設值為 01 (新增 1 列)。
FirstRowHasNames 表示資料表第一列是否包含欄名稱的二進位值。01 的值表示,在匯入時,欄名稱由第一列取得。00 的值表示第一列沒有欄名稱,欄名稱顯示為 F1、F2、F3 等等。預設值為 01。



認識資料連線字串的 IMEX 參數

在建立與 Excel 連線用的連線字串上,可以加入設定參數:IMEX = 1。

此參數是告訴系統要使用「匯入模式(Import mode)」驅動程式。

此參數與登錄機碼:ImportMixedTypes = Text 有關連,這會強制轉換成文字的混合的資料。

但是,ISAM 預設的驅動程式查看前 8 個資料列,並從該取樣會判斷資料型別。
如果這八列取樣是所有數字再設定 IMEX = 1 不會將預設資料類型轉換成文字 ; 它會保留數字。

您必須小心該 IMEX = 1。
這是 IMPORT 模式所以結果可能會無法預測,如果您嘗試執行附加或更新這個模式中的資料。

模式
0 Export mode
1 Import mode
2 Linked mode (full update capabilities)


認識資料連線字串的 HDR 參數

欄位標題:依預設,會假設 Excel 資料來源的第一欄包含欄位標題,這個標題可以用來做為欄位名稱。

如果沒有,則必須關閉這項設定,否則第一列的資料將會「消失」,而變成欄位名稱。
如果不指定這個設定參數,其預設值為 HDR=Yes。

因為「擴充屬性(Extended Properties)」字串可以包含數個參數值,必須使用雙引號括住這個字串,雙引號外還要加上另一對額外的雙引號,用以告知系統將第一組雙引號視為常值。



以下提供數個連線字串的範例程式碼:

(一) 使用 OPENROWSET 函數,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xls

--EX1. 使用 OPENROWSET 函數,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xls

-- 未使用 IMEX=1 參數
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0;Database=C:\mySSIS\EX_NULL.xls;HDR=YES','SELECT * FROM [Sheet1$]'); 
GO

-- 使用 IMEX=1 參數
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0;Database=C:\mySSIS\EX_NULL.xls;HDR=YES;IMEX=1','SELECT * FROM [Sheet1$]'); 
GO

-- 13_OPENROWSET 函數_使用 IMEX



(二) 使用 OPENDATASOURCE 函數,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xls

--EX1. 使用 OPENDATASOURCE 函數,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xls

-- 未使用 IMEX=1 參數
SELECT * 
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
 'Data Source="C:\mySSIS\EX_NULL.xls";Extended Properties=EXCEL 12.0')...[Sheet1$];
GO

-- 有使用 IMEX=1 參數
SELECT * 
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
 'Data Source="C:\mySSIS\EX_NULL.xls";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$];
GO

-- 14_OPENDATASOURCE 函數_使用IMEX



(三) 使用「連結伺服器(Linked Server)」,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xls

--EX1. 使用「連結伺服器(Linked Server)」,搭配 Microsoft.ACE.OLEDB.12.0 提供者,連接到 *.xls

-- 未使用 IMEX=1 參數
EXEC sp_addlinkedserver 
   @server = 'EX_NULL', 
   @provider = 'Microsoft.ACE.OLEDB.12.0', 
   @srvproduct=N'ExcelData',
   @provstr='EXCEL 12.0',
   @datasrc = 'C:\mySSIS\EX_NULL.xls';
GO

-- 查詢資料
SELECT * 
FROM [EX_NULL]...[Sheet1$]
GO

-- 使用 IMEX=1 參數
EXEC sp_addlinkedserver 
   @server = 'EX_IMEX', 
   @provider = 'Microsoft.ACE.OLEDB.12.0', 
   @srvproduct=N'ExcelData',
   @provstr='EXCEL 12.0;HDR=Yes;IMEX=1',
   @datasrc = 'C:\mySSIS\EX_NULL.xls';
GO

-- 查詢資料
SELECT * 
FROM [EX_IMEX]...[Sheet1$]
GO

-- 15_連結伺服器(Linked Server)_使用IMEX



-- 16_連結伺服器的設定





可能的作法:

若不考慮偵測資料型態對於效能的影響,可以使用以下的方式來組態:

1. 修改 登錄機碼:TypeGuessRows 的值為 0。
2. 在連線字串上,加入設定參數:IMEX = 1。

讓 Excel 資料提供者掃描來源資料列的筆數,可達到:16384。



參考資料:

PRB: Excel 傳回的值當作 NULL 使用 DAO OpenRecordset
http://support.microsoft.com/kb/194124/zh-tw

PRB: Jet 4.0LEDB 來源資料的傳輸緩衝區溢位錯誤而失敗
http://support.microsoft.com/kb/281517/zh-tw

如何從 Visual Basic 或 VBA 搭配使用 ADO 與 Excel 資料
http://support.microsoft.com/kb/257819/zh-tw

資料被截斷成 255 個字元,Excel ODBC 驅動程式
http://support.microsoft.com/kb/189897/zh-tw

起始 Microsoft Excel 驅動程式
適用: Microsoft Office Access 2003
http://office.microsoft.com/zh-tw/access-help/HP001032159.aspx

Initializing the Microsoft Excel Driver
Applies to: Microsoft Office Access 2003
http://office.microsoft.com/en-us/access-help/initializing-the-microsoft-excel-driver-HP001032159.aspx

ACC2002: Ignored MaxScanRows Setting May Cause Improper Data Types in Linked Tables
http://support.microsoft.com/kb/282263/en-us

Initializing the Microsoft Excel Driver
Access 2007
http://office.microsoft.com/en-us/access-help/HV080756961.aspx

problem with retreving a excel data through excel source component.
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/65a2b606-b7d1-4e7d-8bd5-03c68dc293d2/