Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Nov 13, 2012 10:19 AM by DarrellNorton
Nov 12, 2012 04:57 PM|LINK
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:
= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;"
the "IMEX=1" that does the trick)
requires that you have the correct DLL installed on your server or you'll get an "installable ISAM not found" type error.
my question is this: is there any OTHER way to overcome this problem? like.....FORCE a coloum to be treated as TEXT?
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.