Q
Manage Learn to apply best practices and optimize your operations.

Data export from AS/400 into MS Excel

Is data export from AS/400 into MS Excel possible? And if so, how difficult is it?

My questions stem from a client of ours that uses an older version of the AS/400. I am trying to get them to send me information in an Excel file via e-mail so that I can take the Excel file and import it into my Sequel server database. I have offered to write a translator for them; however, they are a very large company that can't seem to find the time to let me sit down in front of their computers.

If you could please write a short synopsis of how this can be done, or point me to an article that very clearly outlines how this may done, I would very much appreciate it. By helping myself, I know I can help them make their lives simpler. It is tough to convince them, so I want to make it as painless for them as possible so they can't so no.


If you want to export the AS/400 data directly to the IFS, it can be accomplished with the AS/400 command: CPYTOIMPF and/or CA/400 via a GUI driven download.

If you are doing a large database, or many files that need to be related, you will have to create an AS/400 Query, SQL with output to a Physical, or write a program to output the data first. Bear in mind that the data transferred will not strip the trailing blanks from any field making the download and/or creation very large.

There are a number of utilities that will handle this, but bear in mind that in Excel, if you want to export the AS/400 data directly to the IFS, it can be accomplished with the AS/400 command: CPYTOIMPF and/or CA/400 via a GUI driven download. If you are doing a large database, or many files that need to be related, you will have to create an AS/400 Query, SQL with output to a Physical or write a program to output the data first.

Also remember that the data transferred will not strip the trailing blanks from any field making the download and/or creation very large. There are a number of utilities that will handle this, but realize that an Excel spreadsheet cannot be more than 65535 records. You would probably have to import directly to the SQL database or into MS Access.


This was last published in March 2001

Dig Deeper on Data backup, storage and retrieval on iSeries

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close