Crystal Reports CSV data source, field types

Posted on 2016-10-11
Last Modified: 2016-10-14
I have a Crystal Report that uses a CSV file as the data source.

In the Field Explorer, every single field is type "String [255]"

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
Question by:ecarbone
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 100

Expert Comment

ID: 41839260
How is the data in the CSV delimited?
DO the fields have ' ' or " " around them?

Can you provide a sample file or even just a couple of records?

Do the date fields always have data in them or can they be NULL?

LVL 10

Author Comment

ID: 41839850
How is the data in the CSV delimited?
Always comma delimited

DO the fields have ' ' or " " around them?
All fields have " " around them (even date and numeric)

Can you provide a sample file or even just a couple of records?
See attached

Do the date fields always have data in them or can they be NULL?
I will assume the date field always has data in it, since it is the 'order date' and this is a data file of all orders in a given date range. That being said, it wouldn't hurt to add some quick validation.

Thanks for your help.
LVL 10

Author Comment

ID: 41839900
For this report, there are 8 fields that need to be formatted as 'Number'.
Crystal Report sees them all as text fields.

So I created 8 separate formulas that look like this:

And in my report, instead of placing the data field (which is text), I am placing the formula (which converts the text into a number).

This allows me to format the appearance of the number (comma, decimal point, etc), and also allows me to add subtotal, sum and count fields to the footer.

But when I refresh this report, it takes a good 3-4 minutes for Crystal Reports to crunch the data and display the report. (In contrast, if I use an xls file where the numeric fields are already formatted as numeric), it could take less than 10 seconds.

So this seems like an awful waste of time, having to convert these fields for every single record over and over and then calculate the sums in memory. I'm wondering if there is a better way to do this (with CSV files)
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 100

Accepted Solution

mlmcc earned 500 total points
ID: 41840171
Since the data is in " " Crystal assumes the values are strings.

Unless you need the values to be numbers/dates for calculations and formatting you could just use them as is.

Can the CSV creation occur without the " " around the fields?

LVL 10

Author Closing Comment

ID: 41840261
Unfortunately no. It's an automated data dump from a very large corporation and I am sure I will have 0.0002% chance of convincing them to change it.

On another note ... I had mentioned that the data calculations take several minutes before the report is finished rendering in Crystal. There are only 110 records in this csv file so I can only imagine how long this will take when there are 500+ records.

So, I think I am just going to bite the bullet - open the data files each week in Excel, manually format the 8 or 9 columns and then re-save it as an xls file.

I feel like Crystal Reports is a mature product and they should have better support for csv ... AND ... they should have a feature that allows the user to specify data types for each field.

You run into the same annoying condition if you're linking to an excel file and say for example, the first 20 records have an empty 'price' field. It doesn't bother looking any deeper, just gives up and assumes it is a string.

In any case, thanks for your help
LVL 35

Expert Comment

ID: 41844427
So this seems like an awful waste of time, having to convert these fields for every single record over and over and then calculate the sums in memory. I'm wondering if there is a better way to do this (with CSV files)

 FWIW ...

 I found it hard to believe that converting some text fields to numbers could actually slow CR down that much, so I did some tests.

 The actual problem seems to be using a CSV file as a datasource.  I took your sample file and duplicated the records until I had 200 total, and picked 10 fields that contained numeric data.  It didn't matter if I used 10 formulas to convert those fields to numbers, or just put those 10 fields on the report.  Either way, when I first opened the report and hit "refresh", it took CR over a minute to produce the report.  The times were basically identical, so converting the fields took almost no time.

 Basically, this just supports your decision to import the CSV file into Excel, because it looks like trying to use a CSV file in the report is just going to be slow, no matter what you do.

 If you don't know, there are ways to simplify, or even automate, the CSV-Excel import process.  I think there are some utilities that will do it, but you might want to start with the MS article below on Excel's "external data" feature.  I'm not an Excel expert, and I only skimmed the article, but it looks like it could help.


Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question