can I load a file with delimiter including several chars?

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

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

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 |+|?

Hi!

any single character can appear in the data

Escape too? Non-printable chars too?
https://community.vertica.com/vertica/topics/use_special_symbol_for_delimiter

Hi!

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

Table

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

Data

1$NC$Vincent$NC$2011-01-01$NC$8602162311234,8602162311233
1$NC$STEVEN$NC$2011-02-01$NC$8602162311235|8602162311236 

Script - 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)

Regards