Field Separator on select and copy

Want to create an extract from one cluster to a flat file and then copy that into another cluster, but need to change the default field separator to . The following is not working:-
vsql -c "select * from poctest.bond limit 5;" -q -t -o BondTest.txt -P format=unaligned -P fieldsep=E'\011'
What is the correct format for the fieldsep parameter?
I'm assuming that the following would then work?
cat BondTest.txt | vsql -At -c "copy POCTEST.bond from stdin delimiter E'\011';"
Thanks in advance.

Best Answer

  • Options
    Tim_1Tim_1 ✭✭
    Answer ✓

    Thanks, works perfectly!


  • Options
    Tim_1Tim_1 ✭✭

    My bad, the separator should be a TAB, went missing from the original post

  • Options
    marcothesanemarcothesane - Select Field - Administrator

    I found it difficult to pass a 'tab' through the command line.

    My workaround is to cheat the tab character into an environment variable, using perl, and then to use it in the vsql command:
    For the export, two commands - note that, in the vsql call, all configuration switches come before the "-c" switch:

    $ export TAB=`perl -e 'print qq(\t)'`
    $  vsql -q -t -P format=unaligned -P fieldsep="$TAB"  -c "select * from foo limit 5"
    1.000000000000000   Arthur  Dent    2017-02-05 00:00:00
    2.000000000000000   Ford    Prefect 2017-02-05 00:00:00
    3.000000000000000   Zaphod  Beeblebrox  2017-02-05 00:00:00
    4.000000000000000   Tricia  McMillan    2017-02-05 00:00:00
    5.000000000000000   Gag Halfrunt    2017-02-05 00:00:00
  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    Yes, though you can also use \t as the delimiter to indicate TAB:

    copy myTable from stdin delimiter E'\t';

Leave a Comment

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