Upload to AS400 DB file

15 pts.
Application development
Hi I am not so proficient in programming and i am looking for a way to upload an excel sheet into a database file (with same columns as in m excel) on the as400. I tried to FTP, but i goes in as a flat file. Various other FTP options using FDF etc have failed. Any suggestions ? Thanks Raman

Answer Wiki

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

Loading external data to an AS/400 file is always a multi-step process, which is true with any database. Excel treats each cell independently. DBs are strict about field content and type. There are two primary ways to do a load to an as/400 physical file.

Both ways first involve having the target file already created. You will need to have its file field definition (DSPFFD).

Next, decide if you want to load it directly to this file or go through an intermediate input file first. Loading directly is easy if the record length is small (less than 50), has little or no numbers, no negative numbers (otherwise you have to convert the negative into the appropriate character representation where the last digit would be) and the target file has no packed numbers. By now I have probably talked you out of this idea. Doing direct loading usually means you have to format your excel data in a flat format. This means character fields need to be filled with spaces, and numbers need decimal points removed and zero filling. For example if you have the number 5.1 in Excel that will be moved to a 9S2 field, it would need to look like: 000000510. This can all be accomplished in Excel via formulas and then concatenating them into a single string. Or it can be done in Access. However, this is a lot of work. So I would not advise this method.

The 2 step input file method:
1. Get the DDF for your as/400 file. Use it to compare to your excel (they must have the same number of fields and data types in the same order). Or create a new file based on the structure of the excel file.
2. Create a flat single field file on the as/400 as your input file. You can do this with or without creating a DDS description using CRTPF command. It just needs to be long enough for your incoming data.
3. Save your excel file as a CSV (comma separated value file).
4. FTP the file to the input physical file created in step #2. Remember when you do the ftp, you need to specify the member as well as the file name, e.g., “put data.csv LIB/DATAPF.DATAPF”
5. Once the data is loaded, use the CPYFRMIMPF command, e.g., CPYFRMIMPF FROMFILE(LIB/DATA) TOFILE(LIB/DATAPF *FIRST)
6. There are a few things to watch out for. Beware of null fields in the csv. If the 400 physical file does not allow nulls, any nulls in the csv will not load. A null will look like two commas with no data between them: ,,. This should be checked in the Excel spreadsheet first. The easiest way to do this in spreadsheet is to go to the top of each column, and hit ctrl+down arrow. It will jump to the end of the data or the first null cell. Sort cells with nulls and fill with a least one space fn its a character column or a zero if it?s a numeric column of data. Also, the CPYFRMIMPF assumes *ADD. Hit F4 on the command and change the MBROPTION choice to *REPLACE or *UPDADD if you want a different add or replace option.

If you do not know DDS, you can always create the physical file using interactive SQL: STRSQL, type CREATE TABLE, hit F4 and fill in the file definition, then hit enter when you are done. Anything you don’t know, just hit F4 for prompting or F1 for help.

Discuss This Question: 4  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.
  • Yorkshireman
    did you look at the data transfer facility with client access?

    it (used to) be able to interpret a spreadsheet format directly  as a BIFF3 - which is MS Excel 3
    but MS have no concept of backward compatibility, so you may need to have the inbound Spreasheet 'saved as' an old excel format. 

    then use the batch version of the upload command in a .bat file.  or call from the IBMi with a strpccmd  call.

    6,085 pointsBadges:
  • IanSimmons
    This is actually a straightforward process. Create a CSV file that matches exactly the columns in your DB file or table. Put this into the IFS area of your server. You can do that using iSeries Navigator or just pure Windows Explorer, so long as your IBM i has shared network folders. In my example below, I use a folder called /Shared.

    Either way, use Copy From Import File (CPYFRMIMPF). 

    CPYFRMIMPF FROMSTMF('/Shared/subfolder/mycsvfile.csv') TOFILE(MYLIB/TABLETOCPY) MBROPT(*REPLACE) RCDDLM(*ALL) FROMRCD(2)                     

    The last parameter, FROMRCD(2), is only necessary if your CSV file has column headings.

    The import can be sensitive to nulls, whcih are common in CSV files; if so, you can add the parameter RPLNULLVAL(*FLDDFT) to default blanks and zeroes, etc.

    Do not have the 'from' file open when you do this.
    160 pointsBadges:
  • ToddN2000
    Is your upload into a new file or an existing file? It can be done a number of ways depending on if it currently exists or not.
    49,660 pointsBadges:
  • philpl1jb
    Doubt you'll get a response, question was asked April 17, 2007  1:09 PM.  But it's all good responses.
    53,910 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: