Solved

Crystal Reports CSV data source, field types

Posted on 2016-10-11
6
60 Views
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
0
Comment
Question by:ecarbone
  • 3
  • 2
6 Comments
 
LVL 100

Expert Comment

by:mlmcc
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?

mlmcc
0
 
LVL 10

Author Comment

by:ecarbone
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.
sample_export.csv
0
 
LVL 10

Author Comment

by:ecarbone
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:
   ToNumber({data.MyFieldName})

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)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Accepted Solution

by:
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?

mlmcc
0
 
LVL 10

Author Closing Comment

by:ecarbone
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
0
 
LVL 34

Expert Comment

by:James0628
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.

https://support.office.com/en-us/article/Connect-to-Import-external-data-9967afd8-85ee-4df3-aa06-753bcc1a2724

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

20 Experts available now in Live!

Get 1:1 Help Now