Copying data between heterogeneous databases with CPYTOIMPF

One of the many things that the IBM i (and its predecessors) has always been good at is exchanging data with other platforms. And one thing that often surprises me is that people often don’t realise just how easy sharing data can be.

How easy? This easy

CPYTOIMPF FROMFILE(MRKRPT01) TOSTMF('/home/PAUL/MarketingReport.csv')
          MBROPT(*REPLACE) RCDDLM(*CRLF) DTAFMT(*DLM)
          RMVBLANK(*TRAILING) FLDDLM(';')

In this example, I cam copying a physical file (table) called MRKRPT01 to a CSV file called MarketingReport.csv in the IFS. I’m using MBROPT(*REPLACE) to ensure the new file overwrites the existing file and RCDDLM(*CRLF) appends a Windows standard carriage return and line feed to the end of each line.

Then things become interesting.

DTAFMT(*DLM) indicates that the data contains delimiter characters, just like a CSV file should. And FLDDLM(';') indicates what the delimiter character should be. By default, this is a comma but I’m in Belgium where standards are different, so I’m using a semi-colon instead.

The RMVBLANK(*TRAILING) removes the trailing blanks from the character fields, which ensures the output is as tidy as it looks.

I now have a perfectly formatted CSV file which can be distributed by FTP, SMTP or by sharing the folder so that end users can find the file themselves. And if you’re using QNTC you can change the TOSTMF parameter to something like ‘/QNTC/path/to/server/folder/file.csv’ in order to put the file wherever you like on the network.