when reading excel spreadsheets i have discovered that if a column, like "part_number" has parts with all numbers and some parts with numbers and letters.......big problems arise. if there are more part numbers with no letters...then the column will be cast
as NUMERIC (and all the ones with letters will be rejected). if there are more part numbers with letters, then the column will be cast as TEXT (and all the ones with no letters will be rejected!). apparently, the way to overcome this is to use the "allow mixed
types" setting in the connection string like so:
There are some registry settings you can add to "try" to force Excel to treat it as text. See the full blog post here: http://blog.lab49.com/archives/196
If you know what format the files are going to be in, I would use FileHelpers. It makes getting data into and out of delimited files super easy and includes an Excel module. It's open source and free.
threeo
Participant
1156 Points
539 Posts
how to read mix-format Excel columns
Nov 12, 2012 04:57 PM|LINK
man....
what a hassle.....
when reading excel spreadsheets i have discovered that if a column, like "part_number" has parts with all numbers and some parts with numbers and letters.......big problems arise. if there are more part numbers with no letters...then the column will be cast as NUMERIC (and all the ones with letters will be rejected). if there are more part numbers with letters, then the column will be cast as TEXT (and all the ones with no letters will be rejected!). apparently, the way to overcome this is to use the "allow mixed types" setting in the connection string like so:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;"
(it's the "IMEX=1" that does the trick)
however........it requires that you have the correct DLL installed on your server or you'll get an "installable ISAM not found" type error.
so my question is this: is there any OTHER way to overcome this problem? like.....FORCE a coloum to be treated as TEXT?
DarrellNorto...
All-Star
87453 Points
9718 Posts
Moderator
MVP
Re: how to read mix-format Excel columns
Nov 13, 2012 10:19 AM|LINK
There are some registry settings you can add to "try" to force Excel to treat it as text. See the full blog post here: http://blog.lab49.com/archives/196
If you know what format the files are going to be in, I would use FileHelpers. It makes getting data into and out of delimited files super easy and includes an Excel module. It's open source and free.
http://www.filehelpers.com/
Darrell Norton's Blog
Please click "Mark as Answer" if this helped you.