Convert Excel file to AS400

I have 'n' number of excel files . i need to convert them into AS400 and validate data . My problem is that all these files are in different formats (e.g. Excel 1 has Age, Name ,Salary fields; excel 2 has name , salary , company fields) to validate data I need these excel files to be converted into a standard format which has all the fields in all excel files . How can i do that, i have to options but are these possible? 1. Convert Excel into standard excel and then import it in AS400 as ','seperated flat file OR 2. Import each excel in AS400 and then put them into standard format file for validating data Can i do this , if yes How?

Answer Wiki

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

Good morning. Not quite sure what you mean by all fields. However you can try the following.

If the excel file layouts do not change, then create a pf with the formats. Export excel in .csv and place file in a QDLS folder, then use cpyfrmpcd to copy to a flat file with a record length that is greater than the longest excel row. Then use the cpyfrmimpf command to copy the data from the flat file to the final PF.

For each of the PF’s created, ensure that you have a key field for joining.

The flat file can be created without DDS.

Hope that this will work for you.

Discuss This Question: 1  Reply

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.
  • Brijesh

    Try this

    * First set up all your columns in Excel to match your existing file layout on the AS400. Save as Excel 97-2003 (.xls). If you are just going to to a new file put them in the order you want. If you have numbers that are stored in a text field, used the TEXT function in Excel to convert them to text (watch though it will convert blanks to zeros so change them back to blank when done). 
    * Lauch iSeries access 
    * Click Send Button (Menu->Actions/Send File to Host) 
    * Tools->Create System i database file 
    * Choose .xls file. 
    * File Type BIFF8 (MS Excel 8) 
    * Next until you get to Scan 
    * Review Fields and Lengths. 
    * Pick your iSeries name, give file a location (library/name) 
    * Give Description then finishe though. File is now created. 
    * Click Transfer Data to System i 
    * Verify rows. 

    File is now on iSeries. If you want to copy data to another file with identical fields, do a cpyf with append option.
    14,450 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: