Forum Discussion
tristaanogre
13 years agoEsteemed Contributor
The problem you're dealing with is that the driver being used for reading in values tries to "best guess" what the formatting of the ENTIRE column is to determine how to read it.
so, if the first few values in the column are numeric only, it assumes the whole column is numeric and, therefore, when it finds a non-numeric value, returns NULL. Same with the other way around.
What you need to do is make sure you force the proper formatting for all values in that column. So, since you are doing ZIPCodes which may have the +4 value attached, you're column should be formatted as text. Using Excel to format won't work. What you need to do is prefix the values in your cells with the apostrophe character (') to force Excel to treat it as text, even for your purely numeric values.
This, to be honest, is a good rule of thumb when reading values from either Excel or from CSV when you're using the DDT drivers. Force all your values in your table to text only and then use conversion routines within your code to convert to integer or what not. For Excel, that means using the apostrophe when entering numeric or boolean values. For CSV, that means wrapping all data in double quotes (").
so, if the first few values in the column are numeric only, it assumes the whole column is numeric and, therefore, when it finds a non-numeric value, returns NULL. Same with the other way around.
What you need to do is make sure you force the proper formatting for all values in that column. So, since you are doing ZIPCodes which may have the +4 value attached, you're column should be formatted as text. Using Excel to format won't work. What you need to do is prefix the values in your cells with the apostrophe character (') to force Excel to treat it as text, even for your purely numeric values.
This, to be honest, is a good rule of thumb when reading values from either Excel or from CSV when you're using the DDT drivers. Force all your values in your table to text only and then use conversion routines within your code to convert to integer or what not. For Excel, that means using the apostrophe when entering numeric or boolean values. For CSV, that means wrapping all data in double quotes (").