Fetching data from excel sheet in AS/400

Question

I am using AS/400 with Reflections for IBM. Here is my question: How can the data from an excel sheet be transfered to an AS/400 session. From there I should be a ble to create macros to add the info to where ever it needs to be inserted.

Answer 1

Depends on your requirements, multiple methods are available,

Transport,

• FTP your saved to the as400
• ODBC from your Excel to the iSeries
• Save your file in the IFS (e.g. /home/data). This brings the sheet in scope of the as400
• Start Client Access Emulator (TN5250), and use the transport in that program. You can use an external file that describes the format
• Start CLient Access File Transfer, and use the external file with the descriptions

Format,

• Save as flat file, and use CPYF (*MAP, *DROP) into a DB2 Table
• As flat file into a DB2 table with one field (e.g. CRTPF). That table can be processed by any iSeries program, Query/400 or SQL
• Save as CSV and use the command "Copy from import file (CPYFRMIMPF)" or the "Copy from stream file (not sure from the top of my head, something like CPYFRMSTRF)
• Save as CSV and use a descriptor file to import into a DB2 table

Setup,

• Directly from Excel (e.g. ODBC)
• With file transfer (e.g. save in Excel, FTP, CPYFRMIMPF)
• script (e.g. use the COM functions in Perl to read Excel, send to as400)
• other script (e.g. use the Excel function in Python, write directly to DB2 (your iSeries that is) by using the DB2 module

At least but not last, use Java with the JT400 toolbox. You can access the iSeries and its resources from your local pc.

You've many roads to choose from. Maybe the most important factor is what you're allowed to do on the as400. E.g. may you only access that machine as DB server, or can you write as400 programs.