file transfer limit on Excell document

[Guest.Visitor]

I can't seem to transfer a file into excell format with more than 16383 records. Is there any way to get around this. The only other solution I could come up with was to transfer into CSV format and then convert that into excell format.

[Guest.Visitor]

There is a limit on older versions of Excel. I don't remember what it is but have had the same problem. Our fix was to upgrade to the next version of Excel without the limit. Roger

[Guest.Visitor]

I noticed that when I migrated to C/A Express, that limitation went to a much higher number. Can't remember how high off hand. HTH Scott

[Guest.Visitor]

When "Slick Willie" Gates stole Lotus he took bugs, limits and all and called it EXCEL.. His excuses for the bugs and limits in EXCEL was "He wanted it to be compatible with Lotus". How could it be otherwise? He tried to blame the record limit on CA, but I tried to load a file from a CD with 40,000 records and EXCEL choked. I have had to go the CSV route, also. bobh

[Guest.Visitor]

James, I just did a Data/Get External Data/Create a New Query and pulled down data from a very large file on the 400. It errored out at the max number for Excel97 at 65536 rows, but I still got all 65536 rows. There is something else wrong with your setup. What is the exact error message you are getting? What version are you on (Help/About)? Bill

[Guest.Visitor]

The exact error message I receive is: CWBTF0003 - The number of the received records exceeds the limit of the PC data file. I'm using Excell 97 which has a record limit of around 65,000. the version of client access I'm using is V3R2M0 Client Access/400 for Windows 95/NT

[Guest.Visitor]

James, That is interesting, because I just checked the "What's New" section of help for Excel 97 and it says that it now allows the 65k rows instead of the 16k that you are experiencing. Maybe it's a problem with MSQuery. Are you at SR2 (Service Pack 2)? If not, I highly suggest you do it. You can get it here: http://support.microsoft.com/support/kb/articles/Q151/2/61.ASP . As it says in this article, SR1 must be installed first. Bill

[Guest.Visitor]

Knowledge Base article 13020750 says that CA for Win 95 (CA V3R2) supports Excel 3, 4, and 5 (BIFF 3, 4, 5), but does not say what the limits of those types are. Article 9733712 says: Microsoft Excel version 5.0 and earlier limited spreadsheet size to 16384 rows. Client Access for Windows 3.1 RUMBA/400 file transfer processes files with more than 16384 rows, allowing the creation of BIFF files that are too large for Excel to load. Because of this, Client Access for Windows 95/NT V3R1Mx Data Transfer limits the amount of data to 16384 rows. Microsoft Excel version 7.0 lifted this restriction and allows for 65535 rows. Client Access Data Transfer supports BIFF 3, BIFF 4, and BIFF 5, but not BIFF7 and maintains the 16384-record limitation at the time of this writing. I couldn't find an article about CA Express, but it would appear it supports Excel 7 and up.

[Guest.Visitor]

Oh gee. I misunderstood what was going on. James, I thought you were doing an MSQuery Remote Data Fetch. But you must be doing a CA data transfer. Ken hit it right on. BUT, this just points you to a solution. (You must have the MS Query option loaded in Office, the default in the Office install is to not install it). In Excel, go to Data/Get External/Create a New Query. Create a Datasource that uses the Client Access 32-bit ODBC driver, if you don't already have one defined. On the properties, make sure that you have it process the library where your data resides. I highly recommend you do NOT use the Query wizard. Select the Datasource for the 400 you defined. Select your table(s) you want to process. I highly recommend you de-select the auto-query button. Send the data back to the spreadsheet when you are done. Bill

[Guest.Visitor]

James, Why don't you just chop the AS/400 file into smaller size files and download these to separate excel sheets? After this, it's just a matter of merging the excel sheets.

[Guest.Visitor]

I think the problem may be with my version of client access. I don't get the option to create BIFF7 files. I think I'll try an upgrade to v4r4mo express.

[Guest.Visitor]

The biggest problem with splitting the data up is that this is a regular task that needs to be done. I'm trying to implement some sort of process where the user runs a program and the files are automatically transfered onto her system. There are at least 8 files that need to be transferred. Splitting 8 files up would be time consuming and cumbersome.

[Guest.Visitor]

The MS Query thing worked perfectly! :-) It is also more applicable to what I need. Thanks to everyone for their input. Hopefully technology moves faster than my data grows else I'll have a problem at the 65k limit!

[Guest.Visitor]

James, I'm glad to hear it worked for you. I began showing users here how to use MS Query and it has transformed many of them, they are doing many complicated reporting requests on their own now and Query/400 use has dropped in half. I am also prodding my staff to learn and use Visual Basic for Applications to develop even more complicated systems. As to possibly needing more than 65K rows when you download, you really should consider moving it to a database program like Access. It can use MS Query as well. Bill