Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: Converting an AS400 table to variable length CSV file

    Hi,

    I would like to convert an AS400 table to a variable length CSV file. I am using the CRTPF and CPYTOIMPF commands for that. The problem is that CRTPF has a mandatory parameters which is record length. But in my case, I would like to be able to create a variable length csv file because I dont know before hand what will be the maximum length required to hold my tables data. Do you know of any solution to my problem of of a utility that can do that.

    Thanks a lot

    Simo

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Converting an AS400 table to variable length CSV file

    Let me first confess, I do not have any idea of AS/400 or DB2/400 ..

    Anyway, I'll try to answer ...

    You can use either

    select col1,',',col2 from table1
    or
    select col1||','||col2 from table1

    and redirect the output to a file ...

    The latter query may not work if your line gets too long ...

    Cheers

    Sathyaram


    Originally posted by lacabessa
    Hi,

    I would like to convert an AS400 table to a variable length CSV file. I am using the CRTPF and CPYTOIMPF commands for that. The problem is that CRTPF has a mandatory parameters which is record length. But in my case, I would like to be able to create a variable length csv file because I dont know before hand what will be the maximum length required to hold my tables data. Do you know of any solution to my problem of of a utility that can do that.

    Thanks a lot

    Simo
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    I imagine you have client access or star SQL ODBC available. Set up an ODBC DSN and connect to he table using something like Excel (through) MS Query. Import the data into Excel and save this as .csv file.

    If you have to many records for Excel use MS Access through linking the source table to a new .mdb database and export the data.
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Aug 2003
    Posts
    2

    Unhappy

    We already have options on how to import data from the AS400 to a csv file. But we would like to use the AS400 commands. Because the source table has 20 million rows, we would like to avoid using ODBC but instead dump the file to a "variable record length" csv file

    Simo

  5. #5
    Join Date
    Sep 2003
    Posts
    3

    AS/400 DB2 data export

    I have a same problem - we need to export data from AS/400
    database to a CSV format as we are migrating to the PC based solution. As I'm not expert on AS/400 field - I'll appreciate if anyone can provide any solution for this task.

  6. #6
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Red face

    AFAIK there is no standard export utility on AS400 like you would have with ORACLE, DB2 UDB or SQL server. There is file transfer from the client side (for instance with Client Access), but action is still triggered from the client in that case. Anyway, this works quite good. You may need to program something on AS400 to emulate export facility.......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  7. #7
    Join Date
    Sep 2003
    Posts
    3
    I know I can fetch files from AS/400, but on the machine data are stored in database tables which are found in libraries... the question is how to perform export from databas/tables to CSV?

  8. #8
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    In AS400 terms data is stored in physical files, since we are talking about a file-system, not a truly relational DB. All our exporting from AS400 to other systems (ORACLE / SQL server) is programmed with RPG. According to our AS400 programmers there is no standard functionality to do an export of a physical file to a csv format. You need to program something yourselves.
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  9. #9
    Join Date
    Sep 2003
    Posts
    3
    I'll try through the ODBC to access tables on the server
    and export data to CVS.

  10. #10
    Join Date
    Dec 2004
    Posts
    1
    You might be able to create a flat file with rcdlen(1). Then try copying the database file to it, it will fail but you should be able to go back through your messages and get the correct record length. In CL it's not bad.

    I tried to do the math to get the length, and it worked up to a point. For some reason, once I get past a certain point, every packed field requires an extra character. Smaller files (fewer fields) don't seem to have this problem. I haven't figured this one out yet, but I'm looking into it. I'll let you know what comes of it if you want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •