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/