Crystal Reports and Excel

[CoSpringsGuy]

Is anyone aware of a limitation for Crystal to read large excel files? I am suspicious that Crystal Reports (both versions XI and 2008) are not reading past around line 16,400 in a couple of large excel files I have.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[Tatertot45]

I have never seen that, but I have seen it where there is a blank line or field that CR is looking for and when it can't find it, it stops reading lines. The last time it happened to me, there was blank line that was hidden, so to CR, it looked like the data 'stopped', and it quit reading lines. Was terribly frustrating...

Tatertot45
CRCP
A+, Network+
CCNA
MCP

 

[CoSpringsGuy]

odd.. I will look at that but Im really thinking it is some sort of limitation because I have two files I am trying to read. Attach one, run, then attach the other and both of them stop reading about the same spot. No errors involved... Our customer noticed the problem when I reported back incorrect dollar figures and after analyzing both i determined they stopped in this general area. Looks like I may need to do some VBA coding to clean it up... Thanks

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Wouldn't this be a limitation of Excel? You could consider converting the file into Access where you can append to allow large datasets.

-LB

 

[CoSpringsGuy]

Im not sure... If I open the spreadsheet in Excel (both) there are over 64,000 lines but Crystal will only read 16,400 of them. In my mind that makes it a Crystal (maybe ODBC) issue. Im sure if I converted it to Acccess Crystal would read them all but shouldnt it do the same from Excel? I have found a couple other limitations in the Crystal Excel ODBC.

I wont rule out that these could be limitations in my own brain though! LOL

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

really odd... ran across this doing some searches but I am on 2008 and same problem exists on XI

Why do I get only 16,363 rows when I export to Excel?
Download PDF version of this faq: ExcelRowLimit.pdf

Issue: The Crystal Reports Excel export DLL (ie. U2fxls.dll) has a MAXIMUM record limit, which is to export to a MAXIMUM of 16,384 rows.


NOTE: This is the row limitation of Microsoft Excel 4.0, 5.0, and 7.0 (and not of Crystal Reports).


Solution: The row limitation in Microsoft Excel 8.0 is now 64,000. Only the Crystal Reports version 7 export DLL for Microsoft Excel can export up to this maximum limit.


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

I guess I should update this in case anyone is interested. The file that I use for this project is a CSV file recieved from the client. I have always converted it to Excel for use in Crystal. When you create a connection to an Excel file in Crystal you have several options:
Excel 3.0 through 8.0.

When saving an excel file the option that seemed to match the best is 5.0 and thats what I have used for years. This was never a problem until this very large file I recieved this week. After working around the issue and breaking the spreadsheet down into several smaller sheets thus creating several "tables" when connecting, thus solving my immediate problem, I decided to keep trying to figure out what the issue is.

So I went back to the large file and saved it as Excel Workbook 97/2003 and created a connection from Crystal using the 8.0 and it read all records.

Hope this helps if anyone else comes across that issue....

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CPK6104]

This does help.

Now a new question. Is there a limit to the number of sheets (tables)in a given spreadsheet? I've always has success linking several sheets. But now, I have a spreadsheet with 10+ sheets. When I go to create a brand new report (new connection | Excel 8.0), it doesn't show the sheet names and I have to quit the process of creating a new report. I'm using CR2008. Any ideas?

 

[SkipVought]

There was a version of Excel, and presumably a corresponding ODBC driver, that had a 16,384 row limit, a version with a 32,768 row limit and a version with a 65,736 row limit and the latest with 1,048,576 rows. So you must be aware of the version of Excel ODBC driver you are using.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]