About CSV output using vsql command

yamakawayamakawa Vertica Customer

Hi.
Currently, I am outputting CSV data from Vertica with the following command.

#!/usr/bin/bash
vsql -F ',' -P pager=off -R $'\r\n' -AXQtnq -f ${SQL_FILE} > ${UNLD_FILE}

The output CSV file is as follows.

col1,col2,...,col10[\r\n]
col1,col2,...,col10[\r\n]
[\n]
[EOF]

Is it possible to avoid outputting the \n in the output CSV file?
(Currently, I pass the result of the vsql command to the pipe and use head -c -1 to remove it)

That's all, I look forward to your response.

Best Answer

Answers

  • SankarmnSankarmn Community Edition User ✭✭

    @yamakawa , try this for a simple output.
    vsql -U Yama_kawa -F $'\t' -At -o yamakawa_output1.csv -c "select * from my_table;"
    (or)
    https://www.vertica.com/blog/create-a-csv-file-with-fields-enclosed-by-quotes/

  • yamakawayamakawa Vertica Customer

    @Sankarmn ,Thank you for your answer.
    I tried to output the data as described, but if there is data that contains a line feed (\n) in the column, it is in an incorrect format.
    I should have mentioned in the first question, but the cause of the CSV output in the example is changing the record endings to distinguish between the line feed code in the column and the record endings as the cause of the CSV output.

    I would like to ask a question about the phenomenon that an unnecessary line feed is added to the end of the file when I use the -Q option of VSql command.
    (The reason is that unnecessary newline code is in the way of processing by sed,awk command in the subsequent process.)

  • SankarmnSankarmn Community Edition User ✭✭

    Line feed can be dealt with sed by adding '/^$/d' with '-Aqc' option.
    Test the code before running elsewhere.

  • yamakawayamakawa Vertica Customer

    Thanks for the answer.
    However, I don't understand it for the life of me.
    When the value stored in Vertica contains \n, vsql -Aqc "..." I can't use sed to enclose items with "".
    Is it possible to create a CSV that conforms to RFC4810?

  • moshegmosheg Vertica Employee Administrator

    Also consider this example:

    SELECT 'SomeString' || E'\013' || E'\010' || 'END';
     SomeString
              END
    (1 row)
    
    dbadmin=> SELECT REPLACE('SomeString' || E'\013' || E'\010' || 'END', E'\013' || E'\010', '<CR><LF>');
     SomeString<CR><LF>END
    (1 row)
    
  • yamakawayamakawa Vertica Customer

    @mosheg

    Very helpful.

    I'll use control characters for line separators and delimiter.
    In addition, when the -Q option is used, I think that an unnecessary line feed is output every 1000 lines.
    We decided not to use this option because we found a problem with it.

    However, to output CSV files with VSql command in bash, we need to use
    The following command is used to execute the process, but the
    I'm not happy with the fact that it's not a line separator character with only the end of the file.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file