IMEX = 1 and TypeGuessRows = 0 not working for ZipCode

Question

I read dozens of posts about this, but I can't get it to work. I am importing an address spreadsheet. The Postal Codes column contains a combination of 5-digit postal codes, 9-digit postal codes, and 5-4 postal codes (5 digits, dashes, and 4 digits). (I’m sorry that I am in the power of users, and this is not something that I can control).

But no matter what I do, I can not get them all to import through OLEDB. Region 5-4 is always imported as null.

In HKEY_LOCAL_MACHINE \ SOFTWARE [Wow6432Node] Microsoft \ Jet \ 4.0 \ Engines \

ExcelImportMixedTypes=Text

TypeGuessRows=0

I tried both 32-bit and 64-bit mode. Input tables are in .XLS format (sorry, I am at the mercy of users whom I cannot force them to update).

Here is my connection string.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strServerFileName + ";Mode=Read;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1

I will gladly publish the table if this helps.

OLEDB from my experience is EXTREMELY unreliable. Is there any other way to import Excel worksheets that work reliably?

Answer 1

I also had problems getting this to work in Linked Tables in Access pointing to Excel workbooks. However, I got it to work in VBScript. I think you need to use this key if you are using ACE:

HKLM\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

This points to the same key (also change one and the other):

HKLM\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

Hope this helps ...

Answer 2

The connection string you sent is for .xlsx files, not .xls. Try this version instead :

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;
Extended Properties="Excel 8.0;HDR=YES;IMEX=1";