How to load a simple CSV?

[yang]

I have a file in the most common CSV format, which is where double quotes are the enclosing quote characters and are escaped using another double quote. This is the default for Excel, Python, psql’s COPY WITH CSV, and many other tools. E.g., here’s a row with two fields:

Code: Select all

“a”"b”,”c
d”

We have substantial CSV data to load, but we can’t figure out how to make Vertica’s COPY command accept simple CSVs.

We tried things like:

Code: Select all

copy test from ‘/tmp/aoeu.csv’ delimiter ‘,’ enclosed by ‘”‘;
copy test from ‘/tmp/aoeu.csv’ delimiter ‘,’ enclosed by ‘”‘ escape as ‘”‘;

Thanks in advance for any answers.

[id10t]

Hi yang!

For ESCAPE AS & ENCLOSED BY you can choose any ASCII value in the range E'\001' to E'\177' inclusive (any ASCII character except NULL: E'\000')...
question marks that you use are NON-ASCII and out of range.

[jpcavanaugh]

Can you upload/send the table definition and a sample and we can help?

[yang]

Hi yang!

For ESCAPE AS & ENCLOSED BY you can choose any ASCII value in the range E'\001' to E'\177' inclusive (any ASCII character except NULL: E'\000')...
question marks that you use are NON-ASCII and out of range.

Question marks? I'm not using question marks. If you're talking about the weird "smart quotes" that this forum is replacing my ordinary double quotes with, then I don't know how to disable that - I already wrapped the thing in code tags.

If you try out using regular quotes, the error I get from the second line is that I can't use the same char for both enclosing and escaping.

[yang]

Can you upload/send the table definition and a sample and we can help?

Sure, that's literally the sample table I was using in my first post. Here's a sample schema:

Code: Select all

create table test (a varchar(512), b varchar(512));

[JimKnicely]

Hi Yang,

Did you find a solution to your problem? If not, I can try to help.

But I'm not completely sure I understand your original sample data. What values do you expect to see in the A and B columns of the TEST table once its loaded?

I created a text file in notepad on a windows machine using your original data:

Code: Select all

“a”"b”,”c
d”

Then I transferred the file (as binary) to a Linux test server and then loaded it into the TEST table in Vertica. Like this:

Code: Select all

dbadmin=> copy test from '/usr/home/dbadmin/test.txt' delimiter ',';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from test;
   a    | b
--------+----
 a"b | c
(1 row)

Is that the data you expect?

Note that if I vi the file on my Linux server, I see this:

Code: Select all

<93>a<94>"b<94>,<94>c
d<94>

[yang]

In short I expect the same result as if I loaded it into PostgreSQL - that the first cell contains (in C escaped string literal syntax) "a\"b", and that the second cell contains "c\nd".

Also be careful with the "smart" double quotes - if you're just copying from this forum then you need to replace those characters with normal double quotes.