I have a Crystal Report that uses a CSV file as the data source.
In the Field Explorer, every single field is type "String "
Certain fields (like "Order Date") need to be of type 'Date'
Other fields (like "Total Price") need to be Numeric.
Every week I will get a new data file, and it will always be in the same format. So therefore I am not interested in manually hacking the source file (for example, opening it in Excel and then using Excel to format the columns). It's not practical to do this every time I get a new data file and besides, it opens up the possibility for human error. I just want to point Crystal Reports to the new data file each week, but I want CR to know that the "Order Date" field should always be a date, and the "Total Price" field should always be a number.
What is the best way to resolve this?
I know I can write a formula to convert a field to a number (for example), but is this the best way? I was wondering if there was a way to do this further upstream. For example, as Crystal is reading the contents of the data file, can this type casting be part of a query? It would be nice if I could right-click on the field in the Field Explorer and change its data type, but no such option exists.
Here's how I am connecting to the CSV File:
Create New Connection > Access/Excel (DAO) > Text File