Upload Excel file into AS/400

AS 400
Microsoft Excel
Can an excel file be uploaded into AS/400?

Answer Wiki

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

Yes. It’s easiest if you are using the add-on for transfers. I will recommend using a CSV format for the uploads. I have had better luck doing them that way.

Discuss This Question: 5  Replies

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.
  • TheRealRaven
    It can be uploaded, but then what to do with it? It certainly isn't a native file.

    Uploading is one thing; processing is another thing altogether. What is needed?
    37,065 pointsBadges:
  • azohawk
    I upload excel files to new physical files a few times a year.
    4,075 pointsBadges:
  • mmanley
    We do this all the time, but there are a few tricks. You should first remove all the headers, subtotal lines, etc., so that you have a "flat" file. Save that file as a CSV file.
    The next step would be to create a physical file with fields that match the columns in your PC file.  These should match by type and maximum length.
    Next, put one record in the physical file.
    Next, (if you're using one of the Client Access transfer protocols), download the file with the dummy record to your PC, saving the transfer definition. You can download it to any file name, but you must specify that the file type for the download is CSV.
    Once you have the transfer definition, you can take the Client Access transfer TO the host option.
    Specify the transfer definition you previously created, point your input file at your PC CSV file, and point the output file at your AS/400 physical file. Make sure you specify that you're uploading to an existing file. You can then tell the transfer to replace the existing data, or create a new member. It's actually a very simple process.
    One thing you have to be careful of is the format of your spreadsheet data. If you have commas or dollar signs in your numeric fields, change their format to simply have the number (and decimal point if necessary). The upload process doesn't expect to clean up the formatting.
    You can do a similar process by creating the CSV file and FTPing it to the IFS, and then use the CPYFRMIMPF (or one of the variations), but those require determining delimiters, etc.
    470 pointsBadges:
  • WoodEngineer
    I recommend using the latest version of Client Access or i Access Client Solutions.  IBM enhanced the upload feature over the years giving it more power and requiring less work for users.

    Also look at CPYFRMSTMF.  One feature which is very handy is an option to specify the starting record which allows skipping header records when transferring data.
    8,245 pointsBadges:
  • ToddN2000
    Just a heads up on data transfers, be careful if the spreadsheet contains dates. Make sure the upload correct. For some reason I hav always had issues with dates depending on how the column is formatted.
    136,530 pointsBadges:

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.

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


Share this item with your network: