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.