Exporting a file to CSV on the IFS

3 min read · Software

Reviewed on

Log in to save 
CPYTOIMPF FROMFILE(PRODLIB/ITEMS)                +
          TOSTMF('/home/export/items.csv')       +
          MBROPT(*REPLACE)                       +
          STMFCCSID(1208)                        +
          RCDDLM(*CRLF)                          +
          DTAFMT(*DLM)                           +
          FLDDLM(';')                            +
          DECPNT(*COMMA)

To include column names as the first row, add ADDCOLNAM(*SQL).

Example: exporting a selection, not the whole file

CPYTOIMPF copies an entire file. When only some columns are wanted, or only one region's rows, the way is not to filter afterwards in Excel: it is to hand the command an object that already holds what is needed.

A view in QTEMP costs nothing and disappears by itself at end of job:

CREATE VIEW QTEMP/EXPCUS AS
  SELECT CUSTNO, CUSTNAME, TURNOVER
    FROM PRODLIB/CUSTOMERS
   WHERE REGION = 'NORTH'
     AND TURNOVER > 0

CPYTOIMPF FROMFILE(QTEMP/EXPCUS)                    +
          TOSTMF('/home/export/customers-north.csv') +
          MBROPT(*REPLACE)                          +
          STMFCCSID(1208)                           +
          RCDDLM(*CRLF)                             +
          DTAFMT(*DLM)                              +
          FLDDLM(';')                               +
          DECPNT(*COMMA)                            +
          ADDCOLNAM(*SQL)

With ADDCOLNAM(*SQL) the header comes from the view's column names, so renaming them in the SELECT (AS "Customer code") decides what the person opening the file reads, without touching the source file.

Note

without STMFCCSID a new file takes the CCSID of the source database file, an EBCDIC encoding, and whoever opens it on Windows sees the wrong characters. 1208 writes UTF-8. If the file is opened in Excel with a double click and accented letters come out wrong, STMFCCSID(*PCASCII) uses the Windows encoding, which IBM points to precisely for Windows applications. The CCSID is chosen when the file is created: on a file that already exists the value must be the same, or the command stops with an error. To change it, delete the file first.

Tip

where Excel uses the comma for decimals, as with Italian settings, fields are separated by a semicolon and decimals are written with a comma, DECPNT(*COMMA). The default is the period, and an Italian Excel may read 12.50 as text rather than as a number.

Strings stay inside double quotes, the default for STRDLM: that way a semicolon inside a company name does not shift the columns, and a quote in the data is doubled.

Releases. ADDCOLNAM, which writes column names as the first row (*SQL for the SQL names, *SYS for the system ones), is in IBM's 7.5 documentation. If on an older release the command rejects it as an invalid keyword, the header has to be added separately.

Sources

#cl #ifs #csv
← Back to blog

You might also like

Comments

  • marcotrezzi.m… (edited)

    Hello to all, I want to share also another way that I used CPYTOIMPF for convert into CSV and put on IFS from Db2 file. After having the file ready, use the following command: CPYTOIMPF FROMFILE(LIB/PFOBJ) TOSTMF('/your_folder/file.csv') FROMCCSID(37) STMFCCSID(*PCASCII) RCDDLM(*CRLF) Done that, you can download the file from ACS using "Actions > Integrated File System" ad use it. Eventually, you can put those parameters: MBROPT(*REPLACE) ADDCOLNAM(*SYS) The parameter ADDCOLNAM can be found from release V7R2. (https://www.ibm.com/docs/en/i/7.2.0?topic=ssw_ibm_i_72/cl/cpytoimpf.html) Marco

You need an account to comment. Log in · Sign up