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.