設定 IMEX=1 透過 OleDb 讀取 Excel 仍然出現 null 值的問題

之前在使用 OleDb 讀取 Excel 時, 發生有些欄位雖然有值, 但是讀出來卻是 Null 的問題, 上網查了一下, 說是在連線字串中加入 Extended Properties, 將 IMEX 設為 1, 就可以強迫 OleDb 的 driver 將內容當成文字讀取, 以避免發生型別錯誤而回傳 null. 如下

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Data.xls";Extended Properties="Excel 8.0;HDR=Yes;IMEX=1;

但是後來發現有些資料可以成功, 有些不行, 但是如果把資料列的順序作一下改變的話(把非數字的資料放到比較前面的幾列), 就又可以順利讀取, 真是怪到極點, 於是再去問了一下 Google, 終於找到問題的源頭... 原來是 OleDb Driver 的問題, 在機碼 HKLM\Software\Microsoft\Jet\4.0\Engines\Excel 有一個 TypeGuessRows 值, 預設是 8, 表示會先讀取前 8 列來決定每一個欄位的型態, 所以如果前 8 列的資料都是數字, 到了第 9 列以後出現的文字資料都會變成 null, 真是聰明得令人哭笑不得..... 所以如果要解決這個問題, 只要把 TypeGuessRows 機碼值改成 0, 就可以解這個問題了!