About CSV output using vsql command

yamakawa

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.

mosheg

Please consider using Ctrl Char as a delimiter and as a record separator,
When a printable delimiter character or \n is part of the data itself.
For example, here we use \001 (CTRL-A) as field separator
and \002 (CTRL-B) as record separator
Export command example:

vsql -F $'\001' -R $'\002' -P null='REALNULL' -AXtnqc \
"select * from schema_name.table_name limit 100" > \
your_file_name.tbl

Import command example, for a COPY command one can use to load data exported as shown above:

copy schema_name.table_name
from '/home/dbadmin/your_file_name.csv'
delimiter E'\001'
record terminator E'\002'
null 'REALNULL'
abort on error;

Sankarmn

@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/

yamakawa

@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.)

Sankarmn

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

yamakawa

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?

mosheg

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)

yamakawa

@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.