Data transfer to excel

[cbat]

Hi, this is my first time posting a question, as I have always been able to find answers here. Thanks!!

I am downloading data from an IBM AS400 into an excel 2002 workbook. As the data comes in it stops at cell 16,351 and spews can not fit data. surely that is not the max amount of cells. Any ideas or suggestion would be appreciated. Thanks.

 

[SkipVought]

Hi,

I think that your AS400 ODBC driver has a limit EITHER in a connect string parameter OR in the driver setup. 16,351 is pretty close to 16,384, which was a row limit for Excel many releases ago.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[koresnordic]

Hi Chat,
I do this all the time and it is your version of Excel. E97 only allows 16384 rows as mentioned by Skip above. If you want more, you will have to upgrade to a newer version of Excel that can handle 65535 rows. But first take a look on the 400 to see how many data lines your file has. If more than 65535 (mine do sometimes) then you will have to either create more files of smaller size or use the query to cut it into smaller chunks and import into more than one sheet.



Graham

 

[koresnordic]

Doh just re-read and you are using E2002. In which case, which version of client access are you using?



Graham

 

[cbat]

I am using Client Access 5 release 1. Everything in the ODBC Driver looks fine (compared it to a pc that is able to download the file). Thanks.

 

[xlbo]

koresnordic - psssst. E97 DOES have 65536 rows - it's E95 that had only 16384. What I HAVE found though is that various other apps did not "upgrade" their excel specs so the internal limits for data output are not changed and consequently, you get errors like the one you have found

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[fredericofonseca]

The problem is with Client Access.
All verison of CA will only alow 14k rows, and there is not way around it so far.

The solution is to download the file as a CSV, and then importing the file into Excel.

If you are dealing with more than 65535 rows then you may be better off doing a minor VBA macro that will read the file and split it over several worksheets.
I even think there is something already posted on Tek-Tips to do that so you better do a search.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[cbat]

There are pc's with the same verion of office and client access that can perform this paticular download.

 

[fredericofonseca]

Not with direct export to Excel or to an Excel file, unless a version greater than 5.2 is out that allows that.

As I said other file formats will do it, but not Excel.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com