People love to work with Excel, and Crystal does a decent job of reading Excel tables. But there is one huge headache that I have had repeatedly over the years. Excel columns don’t have a data type so Crystal (or more specifically the Excel driver) has to guess the data types of each column. The problems start when you have columns with mix of data types, usually a string column that has some numeric values mixed in. Crystal has to decide (guess) the data type of the column based on the data that it sees. Once it decides on a type, all values that are NOT that type become null.
The problem for me has been that I could never find the rules for the decision. But with a bit of research and testing I now feel I have a handle on it. The Excel driver (by default) will scan the first 8 rows of the spreadsheet and each column will be assigned the data-type of the majority of the values in those 8 rows (excluding the header row). Ties go to numeric. So if the first 8 data rows have 5 numbers and 3 strings, the column will be called a numeric and all strings are NULL.
The problem is usually that people need ALL the values and they want them all to be strings. Unfortunately the only way to get this is to change the data in the column. I did some testing today and found that you can’t simply format the column to TEXT format. The format doesn’t change the value and the Excel driver ignore the format. What worked consistently for me was to write the following formula:
In other words I took the problem column and created a new column that appended an empty string to the front of it. This fixes the numerics by converting them to a string but doesn’t change the strings that are already working as strings.
The same problem comes up when reading text files. To fix in text files you can pull into Excel and use the method above, or you can create a schema.ini file in the same directory as the CSV and use that to define the data types of the fields.
Here are some related links for working with Excel or schema.ini: