Exceeding Limit in Excel

370 pts.
AS/400 database
Microsoft Excel
I am loading 20k records from our AS/400 using Excel icon(transfer from AS/400) but after 16k records loaded I am getting message indicating that limit is over need to start in another worksheet. I changed file type under RECV - Details icon in AS/400 to Excel 5 then also no use same error. Pls Let me know what to do.

Answer Wiki

Thanks. We'll let you know when a new response is added.


Data is downloaded to Excel using DTF’s (Data transfer from Iseries) utility available in Client access, In this utility , next to PC output device click on details and there after under file type select file type as BIFF5 or BIFF6 or BIFF7 or BIFF8 then select Details, here option exists wherein you can select
Create extra worksheets when first worksheet overflows.

This will save data beyond 65535 rows onto the next worksheet within the same excel file.</b>

All Versions of Excel have a limit to the number of rows that a worksheet can contain. I believe this is 65535 rows (2^16).

My guess is that it’s actually transfering each record in multiple lines from the 400.

Above is correct, with the point being that you either need to

1) Call MSFT and see if they have a “Business” version of Excel or something that does not have the limit
2) break your data across multiple worksheets – most likely by splitting the data before, and then importing the first chunk into worksheet1, then the latter into WS2. I am not familiar with AS/400, so I don’t know if you can split the data like this easily. If not, again, contact MSFT to see if they can help you figure out how to import into multiple worksheets
3) Use something other than Excel – perhaps MySql or such? If you are looking to do simple enough things, viewing/searching/sorting/editing data, then hire someone to make you a simple website that allows you to access a DataBase – it will be a lot faster than Excel for those types of operations anyways
4) there is a small chance that someone has written a plugin for excel that will extend the amount of data allowed. Google Excel Plugins / Addons / Etc

Your other option here is to upgarde to Excel 2007. There is a limit of 1M rows instead of the old 64k in previous versions.

Discuss This Question: 8  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Gilly400
    Hi, I've come across the same problem. Tried downloading to .csv and several other formats, but excel gets the same problem. I ended up creating a number of files with less than 65535 rows which can be opened separately. As far as I know this is a limitation in Excel, not in Client Access. Regards, Martin Gilbert.
    23,730 pointsBadges:
  • SbElectric
    Yes, excel has the following limit: Worksheet size 65,536 rows by 256 columns
    2,540 pointsBadges:
  • Alicsc
    Hi All, Sorry to say I can not see Details button active after I change file type to Excel 5(BIFF 5) and onething my AS/400 version is V4. But Ok noproblem I managed to do by spliting 16k data in two parts.
    370 pointsBadges:
  • Exceeding Limit in Excel - Learn Excel
    [...] Original post by unknown [...]
    0 pointsBadges:
  • slack400
    This is an Excel limitation. I would always export as a .DIF or txt and then import the data into Access or a SQL DB. You could always keep the data on the AS400 and build out a pgm or query to perform whatever function it is you're attempting to do. Why not utilize your DB2? You've already invested in it.
    2,740 pointsBadges:
  • Koohiisan
    Office 2007 loosened the restriction to 1,048,575 rows and 16,384 columns. Although, any file of such magnitude would not be able to be shared successfully in that form with older versions of Office. Incidentally, OpenOffice.org 3's Calc program allows 1,048,576 rows, but for the life of me I just can't get anything close to the level of success there that I can in Excel with using ODBC to pull data right in. But I keep trying...
    5,045 pointsBadges:
  • TomLiotta
    Excel is, after all, a spreadsheet program. Trying to manipulate 20k rows (or any large number of rows) with it is just nuts. It's the wrong tool. As was noted by JohnsonMumbai, use MySQL or something similar if that much data is involved. Tom
    125,585 pointsBadges:
  • Craig Hatmaker
    I didn't see why you wanted to load an entire file to Excel. If I missed it, sorry. The others have given good advice if you want to manipulate a file that size. On the other hand, there may be some alternatives you could consider and still use Excel, assuming your intent is some form of analysis. With that assumption, I'd recommend selecting a subset based on need, then only load those records to be charted, sorted, sliced, diced, ... whatever with Excel. You can also use summing and grouping to consolidate records down to a more manageable, yet meaningful set before loading into Excel. There are many ways to create a subset on your iSeries or using Client Access. There are also ways to do it in one step directly in Excel - no batch programs on the iSeries, no transfers then loads, no FDFs... just using Excel. If this is something that appeals to you, see this tutorial: Beyond Excel: VBA and Database Manipulation
    1,860 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: