Excel won'T recognize alphanumeric values in collumn
Dominic asked 5 years ago
I have an excel source file which is a corresponding table of items.
When validating the data, it lists all my numeric items correclty, but all the items in the same columns that are alphanumeric are blank. is there a way to force to read a column as alphanumeric?
When validating the data, it lists all my numeric items correclty, but all the items in the same columns that are alphanumeric are blank. is there a way to force to read a column as alphanumeric?
Answers
Patrick Roth Staff answered 5 years ago
Easiest solution is to open the Excel file and for that column, switch the format from “General” to “Text”.
Now the Excel ODBC driver won’t have to guess at the datatype and treat all as char values.
if you say that you’d rather not modify the file, then we have to switch approaches on the source.
Instead of “Excel 2013” as the source, instead make the source file to be ODBC and then type of “Excel 2013” (or whichever).
Then when setting up the driver, there is a checkbox on the ODBC Microsoft Excel Setup window (picture in one answer here) for Driver: Rows to Scan. Make this a very large number instead of the default 8.
https://stackoverflow.com/questions/15844633/using-excel-as-an-odbc-database
Then your query would be:
select * from [Sheet1$]
(or whatever sheet name).
And since odbc will scan all the rows you tell it to, it should see “hey there are some alpha chars in there – better make the column char type” and will work fine.
Now the Excel ODBC driver won’t have to guess at the datatype and treat all as char values.
if you say that you’d rather not modify the file, then we have to switch approaches on the source.
Instead of “Excel 2013” as the source, instead make the source file to be ODBC and then type of “Excel 2013” (or whichever).
Then when setting up the driver, there is a checkbox on the ODBC Microsoft Excel Setup window (picture in one answer here) for Driver: Rows to Scan. Make this a very large number instead of the default 8.
https://stackoverflow.com/questions/15844633/using-excel-as-an-odbc-database
Then your query would be:
select * from [Sheet1$]
(or whatever sheet name).
And since odbc will scan all the rows you tell it to, it should see “hey there are some alpha chars in there – better make the column char type” and will work fine.