2.5 GB CSV file as data source for Crystal report

[Former Member]

Hi Experts,

I was asked to create a crystal report using crystal report as datasource(CSV file that is pretty huge (2.4Gb)). Could you help with me any doc that expalins the steps mainly with data connectivity.

Objective is to create Crystal Report using that csv file as data source, save the report as .rpt with the data and send the results to customer to be read with Crystal Reports Viewer or save the results to PDF.

Please help and suggest me steps as I am new to crystal reports and CSV as source.

BR, Nanda Kishore

[former_member11696 · Accepted solution]

Hello,

Forget it.... There is no way CR will be able to use a flat file of that size. As a test, can you even open that file in Excel and how long does it take, assuming it doesn't crash Excel?

Go back to your CSV providers and suggest they provide data in another format. Actually connecting to the database directly is the best option.

And depending on the size of the report exporting it to PDF likely is not going to work either. If it's less than 100 meg PDF file it may work but that takes time also.

Do not use CR to archive your database. You will have to break the data into smaller CSV files if that is your only data format.

To do so you need to create an ODBC DSN as the data source, Check Microsoft's site on how to create ODBC Data sources with CSV's as the file source.

Thank you

Don

[Former Member · Accepted solution]

Hi Don,

Thanks. Customer said " Normal laptop cannot open that with Excel" . So Customer is asking if this is possible in Crystal report viewer or exporting to PDF.

Regards,

Nanda Kishore

[former_member11696 · Accepted solution]

Hi Nanda,

No, if Excel can't do it CR won't be able to either.

They really need to look at a better way of getting data. You have not said what their data source is so I can't offer any detailed suggestions.

Don

[Former Member · Accepted solution]

Nanda,

I'm with Don on this one. Yes you can use a CSV file as the data source for CR but 2.5 gigs... That's really pushing it, even if you have some high end hardware with tons of memory.

Your best bet is to dump your CSV data into database then use the database as your data source.

If you don't have a database available for use, you can download a copy of Microsoft SQL Server2008 R2 Express Edition with Advance Services for free. It has a 10 gig per database limit, so that should give you plenty of head room. (MS Access would be easier but is is capped at a 2 gig max and it's not free).

This edition comes packaged with the SQL Server Management Studio so you'll be able to 1) Create a new database, 2) Create new table to hold your data, 3) Import the data in the CSV into the new table.

It will also come packaged with the SQL Native Client 10, so you'll be able to connect to get CR connected using either a OLE-DB or ODBC connection.

If you get hung up, just post back with a couple of sample rows of data, the column name and their data types... I can help you write the SQL code...

HTH,

Jason

[Former Member · Accepted solution]

Hi,

Thanks for help.

There is no source db available.

This data has been gathered manually from several sources so csv is only option. And somehow customer wants to be able to read itu2026

BR, Nanda Kishore.

[Former Member · Accepted solution]

That doesn't prevent the CSV from being loaded into a database.

[former_member11696 · Accepted solution]

Hi Guys,

Jason, it may be an issue. The file size I doubt is ever going to work due to it's size. I don't think any import wizard for any DB is going to be able to read a CSV file that huge.

Your only option is to show them that the files can not be used, even by Microsoft. Their work around is to reduce the size of the CSV by creating multiple files that can be open by Microsoft and then you can import the data into a DB and then use CR to report off of that DB table. Or then you'll be able to create reports off them.

They must use some other way of collecting all of that data so it can be used by CR, there is no other way around it.

Go to the source, find out what tool they are using to build a CSV file that huge. I think you'll find they are exporting a database to a CSV format because it may be their only file type option. So again they'll have to break the CSV file into usable file sizes.

Thank you

Don

[Former Member · Accepted solution]

Any enterprise grade DBMS can chew threw a file that size. The BULK INSERT command in SQL Server has options to break the processing down into batches (say 2 million rows at a time, for example). It may take a little time to complete an import that large but it's definitely doable.

It also has BCP (Bulk Copy Program) that can be used to import or export huge chunks of data.

[BULK INSERT (Transact-SQL)|http://msdn.microsoft.com/en-us/library/ms188365.aspx]

or Google SQL Server BCP

[former_member11696 · Accepted solution]

Thanks Jason for the links.

Point I was making was CR will never be able to use a CSV file that large. If they use third party tools to import the CSV into a real database server then CR won't have problems. Subject to the report layout of course.

Thanks again

Don

[Former Member · Accepted solution]

Hi Jason,

I zeroed on MS SQL 2008 R2 . I have an issue. Data issue

column separated by , (comma) and some by ; (semi colon). I am having this issue now. How to load this data to table.

BR, Nanda Kishore

[Former Member · Accepted solution]

The following are the columns: ID,MODEL,CUSTOMER,SHIP DATE,DELIVERY NOTE NUMBER,MATERIAL,DESCRIPTION

BR, Nanda Kishore

[Former Member · Accepted solution]

Hi,

I got the following error when I use Import -Export Wizard: overflowed the disk I/O buffer

Detailed error is here:

Copying to [dbo].[FR] (Error)

Messages

Error 0xc020209c: Data Flow Task 1: The column data for column "ID" overflowed the disk I/O buffer.

(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "c:
FRRW01.csv" on data row 100293.

(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - FRRW01_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

(SQL Server Import and Export Wizard)

Could You help.

Regards, B Nanda Kishore

[Former Member · Accepted solution]

Nanda,

The issue of having some records with comma and some with a semi colon will need to be resolved before you can do an import. Assuming that there are no semi colons in any of the text values of the report, you could do a "Find & Replace" to convert the semi colons to commas.

If find & replace isn't an option, you'll need to get the files separately.

-

---

I've never used the Import Export Wizzard myself. I've always used the BULK INSERT command

It would look something like this...

BULK INSERT SQLServerTableName
FROM 'c:\My_CSV_File.csv'
WITH (FIELDTERMINATOR = ',')

This of course implies that your table has the same columns, in the same order as the csv files and that each column is the correct data type to accept the incoming data.

-

---

If you continue to have issues getting your data into SQL Server Express, please post in one of these two forums

[Transact-SQL|http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads]

[SQL Server Express|http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/threads]

The Transact-SQL forum has some VERY knowledgeable people (including MVPs and book authors) posing answers.

I've never posed to the SQL Server Express but I'm sure they can trouble shoot your issues with the Import Export Wizard.

If you post in one of them, please copy the post link back to this thread you I can continue to to help.

Jason

[Former Member · Accepted solution]

Hi,

I shall try. Thanks.

BR, Nanda Kishore

[Former Member · Accepted solution]

I used Ultra Edit software to open those files and I cleaned the file then loaded to SQL server. Thanks all for help

BR, Nanda Kishore