Loading data ending with \

Moderator: NorbertKrupa

Post Reply
Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

Loading data ending with \

Post by Beg1nner » Tue Sep 22, 2015 7:28 pm

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?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1811
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Loading data ending with \

Post by JimKnicely » Wed Sep 23, 2015 4:51 pm

Use the ESCAPE keyword to change the escape character to something else :)

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Data Load”