檢視示範的 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/
_%E6%9C%AA%E4%BD%BF%E7%94%A8IMEX.png)
_%E4%BD%BF%E7%94%A8IMEX.png)
