Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Crystal Reports CSV data source, field types

Posted on 2016-10-11
Medium Priority
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 101

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 101

Accepted Solution

mlmcc earned 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

688 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