I have a tab delimited file and one of columns has data ending with \, which I believe is cause the data to be rejected.
1234 lodestone&text=\LOHDstohn\ Agency
How should I overcome this?
Loading data ending with \
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Loading data ending with \
Use the ESCAPE keyword to change the escape character to something else
Example:
Example:
Code: Select all
dbadmin=> \! cat /home/dbadmin/test.txt
destone&text=\LOHDstohn\ Agency
dbadmin=> \d test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
public | test | col1 | varchar(100) | 100 | | f | f |
public | test | col2 | varchar(100) | 100 | | f | f |
(2 rows)
dbadmin=> copy test from '/home/dbadmin/test.txt' delimiter E'\t' escape E'\001';
Rows Loaded
-------------
1
(1 row)
dbadmin=> select * from test;
col1 | col2
--------------------------+--------
destone&text=\LOHDstohn\ | Agency
(1 row)
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.