can I load a file with delimiter including several chars?

I want to load a file with delimiter including four char. but I got error: ERROR 2730: COPY delimiter must be a single character can I load a file with delimiter isn't a single char?


  • Options
    Navin_CNavin_C Vertica Customer
    Hello Vincent, Can you show us the sample data you want to load with the delimiter including four char. Try using the Copy command's delimiter option with your delimiter
  • Options
    Hi Navin, my sample data as below(the delimiter is ‘$NC$'): 1$NC$Vincent$NC$2011-01-01$NC$8602162311234,8602162311233 1$NC$STEVEN$NC$2011-02-01$NC$8602162311235|8602162311236 in the business, I cannot use simple char(such as '|' or ',') as delimiter because maybe these char exists in the field value. how can i load it with command copy. thanks in advanced
  • Options
    I have exactly the same type of use case - any single character can appear in the data that would be loaded, how can we specify an uncommon string as the delimiter - like |+|?
  • Options
    any single character can appear in the data
    Escape too? Non-printable chars too?
  • Options

    For anyone who have same issue I did a small python script. Will demonstrate on Topic Question.

    CREATE TABLE public.multisep
    id int,
    name varchar(80),
    date date,
    field varchar(80)


    - http://pastebin.com/Q8At8Tcr
    $ ./vconv.py -h
    usage: ./vconv.py [-h] -i PATH -d STR [-D CHAR]

    modifies a delimiter in CSV file; print output to STDOUT

    optional arguments:
    -h, --help show this help message and exit
    -i PATH specifies a CSV file to process.
    -d STR specifies an original delimiter.
    -D CHAR specifies a new fields delimiter. [default: '\x1b']

    Usage example:

    daniel@synapse:~/Documents$ /tmp/vconv.py -i /tmp/sample.txt -d '$NC$' | vsql -c "copy multisep from stdin direct delimiter e'\033' abort on error"
    daniel@synapse:~/Documents$ vsql -c "select * from multisep"
     id |  name   |    date    |            field           
      1 | STEVEN  | 2011-02-01 | 8602162311235|8602162311236
      1 | Vincent | 2011-01-01 | 8602162311234,8602162311233
    (2 rows)

Leave a Comment

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