Hi,
Is it possible to use 2 delimiters in COPY command?
when i use GZIP DELIMITER '~|' its throwing error "ERROR 2730: COPY delimiter must be a single character"
i have PIPELINE, COMMA, '~','^' coming from my source data so i am planning to use combination.
Can we use 2 delimiters in COPY command?
Moderator: NorbertKrupa
Re: Can we use 2 delimiters in COPY command?
Using a Custom Column Option DELIMITER
This example, redefines the default delimiter through the COLUMN OPTION parameter.
Create a simple table.
Use the COLUMN OPTION parameter to change the col1 default delimiter to a tilde (~).
Hope this helps.
This example, redefines the default delimiter through the COLUMN OPTION parameter.
Create a simple table.
Code: Select all
CREATE TABLE t(
pk INT,
col1 VARCHAR(10),
col2 VARCHAR(10),
col3 VARCHAR(10),
col4 TIMESTAMP);
Code: Select all
=> COPY t COLUMN OPTION(col1 DELIMITER '~') FROM STDIN NO COMMIT;
>> 1|ee~gg|yy|1999-12-12
>> \.
=> SELECT * FROM t;
pk | col1 | col2 | col3 | col4
----+------+------+------+---------------------
1 | ee | gg | yy | 1999-12-12 00:00:00
(1 row)
Itipong Chewinpipat (Bay)
DBA Specialist (Vertica/Oracle)
DBA Specialist (Vertica/Oracle)
Re: Can we use 2 delimiters in COPY command?
ok, but whats my scenario is
I have file with '|' as delimiter and i also have data like this
col1|col2
1|App,le
2|Oran~ge
3|What the|hell is this
3rd row 2nd column value has '|'
how to solve this?
I have file with '|' as delimiter and i also have data like this
col1|col2
1|App,le
2|Oran~ge
3|What the|hell is this
3rd row 2nd column value has '|'
how to solve this?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Can we use 2 delimiters in COPY command?
You can enclose the text in your source file with quotes and let Vertica know in the COPY command.
Example:
Example:
Code: Select all
dbadmin=> \! cat /home/dbadmin/jim.txt
1|"App,le"
2|"Oran~ge"
3|"What the|hell is this"
dbadmin=> copy test from '/home/dbadmin/jim.txt' enclosed by '"';
Rows Loaded
-------------
3
(1 row)
dbadmin=> select * from test order by 1;
col1 | col2
------+-----------------------
1 | App,le
2 | Oran~ge
3 | What the|hell is this
(3 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Can we use 2 delimiters in COPY command?
Hi!
You don't need a combination, just use in non-printed ASCII char as delimiter, for example '\033' (escape char - hard to believe that you have it in text ):
Also, you can use in '\n' (new line as delimiter) and for record separator use in combination of chars (its possible). For example, look at COPY statements in DataCollector folder - there fields separator is a new line and record separator is a combination '\n.\n' (new line, dot,new line)
You don't need a combination, just use in non-printed ASCII char as delimiter, for example '\033' (escape char - hard to believe that you have it in text ):
Code: Select all
daniel@synapse:/tmp$ vsql -c "drop table if exists foo cascade"
DROP TABLE
daniel@synapse:/tmp$ vsql -c "create table foo (id int, text varchar)"
CREATE TABLE
daniel@synapse:/tmp$ echo -e "1\033foo" | vsql -c "copy foo from stdin delimiter e'\033'"
daniel@synapse:/tmp$ vsql -c "select * from foo"
id | text
----+------
1 | foo
(1 row)
Also, you can use in '\n' (new line as delimiter) and for record separator use in combination of chars (its possible). For example, look at COPY statements in DataCollector folder - there fields separator is a new line and record separator is a combination '\n.\n' (new line, dot,new line)