Search code examples
csvinformix

Informix 'UNLOAD TO' generates backslashes when I export table as CSV

Whenever I try to export a table from an Informix database to a CSV file, I find that the generated file contains backslashes. This is the query I used: 

UNLOAD TO 'C:/Documents and Settings/XXXX/XXXX/test.txt' DELIMITER '|'
select * from xxx

This is an example of the results I get in the CSV file

A|B|C|D|E|F\
This\
Is\
SOME\
TEXT\
|
A2|B2|C3|D4|E5|F6

If anyone knows how to resolve this, I would really appreciate it.

Solution

  • This is because are newline characters in the values of this column. You can remove the newlines with replace function.

    First you must enable newline in quoted strings running this stored procedure

    EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T');

    Then you can use replace to remove (or change to another character) newlines from the column that has newlines (in this example is column3)

    UNLOAD TO 'C:/Documents and Settings/XXXX/XXXX/test.txt' DELIMITER '|'
SELECT column1, column2, replace(column3, "
", "") 
  FROM xxx

    Note that in the call to function replace are only a newline between the two firsts quotes in the second parameter, the third parameter is the value for which you want to replace the newlines.