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
  • 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)
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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 34

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now