can I load a file with delimiter including several chars?

Vincent_1

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?

Navin_C

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

Vincent_1

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

Ian_Rubenstein

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

id10t

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

id10t

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