You can create Crystal Reports that directly read CSV files using the Access/Excel(DAO) connection. Just keep in mind that CSV files don’t always make an ideal data source. Like spreadsheets, CSV files don’t have set data types for each column. This can cause data type ambiguity which might cause you to lose some data. And in some cases the report will read the first record of the CSV file as the column headings, removing the first record of data from the dataset.
But here CSV files have one advantage over XLS files. CSV files allow you to introduce a Schema.ini file to define the data type for each column in the CSV. This is something you can’t do with spreadsheets. The schema.ini file is a simple text file that sits in the same folder as your CSV. There are many attributes available in schema.ini, but you only need to use the attributes that you need. The other attributes will be set based on defaults stored in the registry. Here are the most common problems I find that can be solved with a schema.ini file.
- The CSV is reading the first row of data as column headings
- Columns read as the wrong data type
- Character column is read as numeric and shows only the numeric values
- The columns are parsed using the wrong character
Here is an example of a schema.ini that defines two different CSV files in the same folder:
As you can see, a single INI file can define multiple CSV files when they are in the same folder. Each file gets it’s own section of the INI file. Both files are set to be read as comma delimited. Both files are set to NOT treat the first row as column headings. In the first file we allow the driver to name the fields (usually A, B, C, etc) and determine the data type automatically. In the second file we name each column and assign each a data type.
So if you are reporting on a CSV file and running into issues, using a Schema.ini file may help solve the problem.