Upload to AS400 DB file

15 pts.
Tags:
Application development
AS/400
DataCenter
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:  

 
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