excel - IMEX=1 and TypeGuessRows=0 is not working for ZipCode -

excel - IMEX=1 and TypeGuessRows=0 is not working for ZipCode -

i've read dozen's of posts on this, can't work. importing spreadsheet of addresses. zip codes column contains mix of 5 digit zips, 9 digit zips, , 5-4 zips (5 digits, dash, , 4 digits). (sorry @ mercy of users , not can control).

but no matter do, can't them import through oledb. 5-4 area imported null.

in hkey_local_machine\software[wow6432node]microsoft\jet\4.0\engines\

excelimportmixedtypes=text typeguessrows=0

i've tried both 32 bit mode , 64 bit mode. input spreadsheets .xls format (sorry, @ mercy of users cannot forcefulness them upgrade).

here connection string.

provider=microsoft.ace.oledb.12.0;data source=" + strserverfilename + ";mode=read;extended properties=\"excel 12.0 xml;hdr=yes;imex=1

i gladly post spreadsheet if helps.

oledb experience extremely unreliable. there way import excel sheets works reliably?

the connection string have posted .xlsx files , not .xls ones. seek adapting this version instead:

provider=microsoft.ace.oledb.12.0;data source=c:\myfolder\myoldexcelfile.xls; extended properties="excel 8.0;hdr=yes;imex=1";

excel import