Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal Reports CSV data source, field types

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

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
Hire Technology Freelancers with Gigs

Work with 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.

 
LVL 101

Accepted Solution

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

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 35

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

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…
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 …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

877 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