Ref #729
Problem:
An Excel data source has a notes column that contains BOTH numeric and non-numeric characters. In CHARTrunner, some of this data is missing. The data is missing from rows in the spreadsheet that contain non-numeric characters because the Jet database engine that is reading the data from the spreadsheet is treating this as a numeric column (i.e. a column that contains only numbers) and it ignores any data that it cannot interpret as a valid number
Solution:
To resolve the problem:
- In Excel, format the entire column as Text.
- Enclose each entry in rows 2 through N in parenthesis, e.g. (79864) rather than 79864. What this does is "trick" the Jet database engine (which is what is actually reading the .XLS file) into thinking that the column is of type Text rather than of type Double (i.e. a double precision floating point number).
Evidently Jet is determined to see the column as a Double if it sees ANY valid numeric entry in any row of the column. Placing the parenthesis around the numbers is evidently enough to convince Jet to treat the entire column as a Text column. When Jet treats that as a Double column, it ignores any row having a non-numeric entry, such as 79864a. It may be possible to use another strategy (other than using the parenthesis) to trick Jet into thinking this is a Text column, but the parenthesis does work.
