Cannot refresh report with latest data.

[huytonscouser]

I have a .xlsx input file, defined as a linked/ODBC(RDO), so the file
will always contain the latest data.

I created a report and it works fine. However everytime i refresh the
report with the new data, i always get the message
"Failed to retrieve data from the database", followed by
"Details: 22001:[Microsoft][ODBC Excel Driver] The field
is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data. [Database Vendor Code: -1047]

All the fields are string, and none greater than 255 chars.

If i recreate the report from scratch, using the latest data, it works.

I just cannot refresh the report.

Any ideas ?

 

[PeterDimitrov]

Check if this will work for you: create an excel file with one row and in each string column put a 255 chars long text. Create the report, then update the excel file with your real data and try to refresh the report without verifying it.

Having excel as a database is not the best solution for many reasons. If you create an Access database, link the excel file as a table and use this table in your report you will have much better results.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[huytonscouser]

Thanks Peter,i've tried that.

The file is .xlsx and contains about 74k rows. If i create as linked/access (.accdb), then i get same error . If i try to create a .mdb i get a message that the file contains functionality(probably > 64k rows). Thats why i created a linked .xlsx/odbc(rdo) connection.

 

[PeterDimitrov]

Did you create the excel file with 1 row and 255 chars per string column ?
As far as I know when the ODBC driver is retrieving the excel table structure it will read the first 8 rows (I am not sure about the exact number) . So the table, which will be returned back might have 2 chars for a column and if you insert a record with 5 chars before to refresh the report you will receive the error above. By creating a file with 255 chars per string column on the first row you will avoid this scenario.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[huytonscouser]

Peter, i created a single row .xlsx file with columns containg 255 chars. Opened report, presed refresh, no error. Ran the job which creates the original(linked) .xlsx file. Pressed refresh, same error.

Are you suggesting that the .xlsx file (row 2) after headers has to contain a row of padded columns at 255 chars ?


the .xlsx is exported from a crystal report, and the text fields are restricted to a max of 255 chars.

This sounds like something that must impact every .xlsx file used as ODBC ???
Bascially I have a .xlsx input file, with about 74k rows.

It gets updated daily.

What connection type do i need to create to use this in Crystal cr11, and be able to refresh/run the report daily ?

 

[PeterDimitrov]

I never used excel as a database and I cannot answer your question about the connection. I had a case when importing the data to a database was failing because of the way how the ODBC driver retrieves Excel file structure. The solution was to insert first row (after the columns' captions) with 255 chars for the text columns. Your problem seams similar and I am surprised that this did not resolve it.

Did you try to reduce the number of rows to less than 64k? At least you will find if the problem is in the structure or the number of rows.

Few days ago Ken Hamady shared on LinkedIn a sleek method to import CSV files by a creating schema.ini file. Here is the link to the discussion:

http://goo.gl/f3rjL

He posted also this link about schema.ini:

http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx

If the ODBC driver for Excel cannot import the data you may try to convert the file to a CSV and use Microsoft text driver.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.