Move Data from Excel Spreadsheet to DB2/400

Posted on 2006-04-10
Last Modified: 2012-08-14
Dear All,

What is the easiest way to move data from an Excel Spreadsheet to an AS400 Database? I get a file once a month that needs to be uploaded and processed on the AS400. Currently I'm converting the Excel file first to CSV, then upload it using Client Access Transfer to the AS400 but I need a more automated way of doing this.


Question by:Errol Farro
    LVL 14

    Accepted Solution

    client acces supports direct transfer from excel to DB/400 via an addin(cwbtfxla.xll)
    it can er found in
    C:/program files/IBM/client Access/shared
    LVL 14

    Expert Comment

    use ODBC and write a bit of VB to poulate the file

    craete a transfer and use then shell the transfer on a schedue job witth-in windows.

    therey are many ways - if you give a few more details we can give the most appropriate solution.

    LVL 1

    Expert Comment

    Dave is correct.  You can use the cwbtfxla to write a script to do the transfer.  If you'd prefer another way, you could simply save the CSV to the iSeries in either QDLS (shared folder) or in an NTFS file share on the iSeries (AS/400).  - Let me know if you need further details about that part.

    Once the file has been saved to the iSeries (do this by simply selecting File Save As from Excel and pointing to your iSeries box - for example \\iseries\), you use the one of the following 2 command to convert the spreadsheet to a native DB2/400 file:

    In the case of saving the file to a shared folder you use the CPYFRMPCD command (copy from pc document) - note the .xls file name must be only 8 characters plus the ".csv" - 12 characters total.)

    In the case of saving the file to an NT share on the iSeries, just use the CPYFRMIMPF command (copy from Import File).  This is by far the easiest method and you're not restricted by the file name lengths, etc.  Here's what the command looks like:

    CPYFRMIMPF FROMSTMF('\iSeries directory\test.csv')

    The only deal is that the file has to exist before either command is run, so you'd have to have a fixed file / csv format for it to work.  

    Dave's method allows you to create files on the fly - without having to define them first.  If you know the format, and it's always the same, then the method I describe here works great.  The CSV file can then be processed at will and can be generated by other means.

    Let me know if you need more info about what I described here.

    Good luck.

    - Bryant

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Great sound, comfort and fit, excellent build quality, versatility, compatibility. These are just some of the many reasons for choosing a headset from Sennheiser.
    This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now