How to export data from AS/400 in Excel

5 pts.
Tags:
AS/400
AS/400 import/export
iSeries Access
Microsoft Excel
ODBC
Hi everyone,

I would like to know how to in a step by step process export date from AS/400 iSeries into an Excel workbook. The purpose is to gather the data from AS/400, export it to Excel, then filter the data for specific info, then import that to another Excel workbook. A basic inventory report by item numbers and total quantities on hand. It is then presented weekly in an Excel workbook for client review. I currently manually go through AS/400 and add up the total for each item, then manually enter the numbers into Excel. Problem is there are over 550 line items! There must be a way to do it automatically, rather than manually. Please bare with me guys as I am pure newbie but very quick learner.

Thanks in advance for your help!

Rico

Answer Wiki

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

There are a number of ways to go, here are a few.

  1. Create a comma delimited file .cvs – easy CPYTOIMPF can either write a comma delimited file to a flat as/400 file that you can send to the user or write a comma delimited file to a folder on the ifs (the output file should be named with .cvs) When the user goes to open the .cvs program it will open in EXCEL provided it doesn’t exceed 65000 rows.
  2. I believe that Navigator has an extract tool
  3. The user can create a query in Excel to access files on the 400
  4. Write an RPGLE program using java tools (HSSF) to actually write an excel worksheet – cell by cell

This entails some serious programming and a lot of run-time overhead.

Phil

I use option 4 all the time and have not noticed any serious run-time overhead.

Terry

The discussion above assumes that you have a file of the data. You may be able to create this file using
query on the 400. Or that Excel query capability might be good for you. But option 4 is definitely not on the table for a newbie. So the place to start might be with the wrkQRY command, but you will need to know the files and fields that contain the data that you are currently using.

Phil

If you use Ops Navigator, there is an “add-in” for excel which lets you import and export directly from the i-Series.

Regards,

Martin Gilbert.

Rico,

If you want to go beyond creating a .csv file, I would recommend a series of articles Scott Klement wrote on the subject:

He also has example programs which you can download from a site mentioned in the articles. I used them to create programs that produce .xls files that my users are quite pleased with.

Karl Woods

Discuss This Question:  

 
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following