Re: Loading Offline Files from Excel

Bob,

If the data is fairly straightforward, I have used iSeries Access File Transfer in the past. However, getting the data in the spreadsheet to match the target PF on the iSeries so that the data will map over and transfer without errors can be tricky, especially with date, Mapics date, and numeric data.

When I need to make absolutely sure the data is mapped properly, I have used this approach:

1) Create a PF with fields that correspond to the columns in your spreadsheet data. As I said, it can be tricky to try to handle dates and numeric data, so I usually make them all character fields.
2) Save the data from Excel as a CSV file via File/Save As. If you have multiple worksheets in a workbook you will need to save each sheet separately.
3) Transfer the CSV file to a folder in the IFS via iSeries Access, FTP, or shared folders.
4) Use the CPYFRMIMPF command to copy the data from the IFS (stream) file to the PF from step 1).
5) Write an RPG conversion program to read the data from the above file, convert the data to the appropriate type, and then write it to the target file.

Depending upon your situation, this approach may be too much effort, but it does offer the most flexibility and allows you to validate the data as you write it.

If I can elaborate any further, let me know.

Pete Nielsen
Copper Harbor Technologies




Hello,
I have a requirement to load some offline data to our MAPICS system. Currently it is in an Excel 2003 spreadsheet and I need to get it into a file on the iSeries that I can use to load from. As I have never done this before I have no clue on how to proceed.

Could some kind soul out there enlighten me.

Thanks very much in advance

Bob Anderson

IT Manager
Kent Sporting Goods Inc.
433 Park Ave. S
New London OH 44851
419-929-7021 x315
P Please consider the environment before printing this e-mail.