importing a csv file into postgres 1

[keak]

Hi there,
Is there a easy way to import a csv postgres file via psql command line?
I treid looking up the net but I can't seem to find a straightforward method of doing this.
The format of the file will be something like:

field1, field2, field3, field4
data, data, data, data
data, data, data, data
data, data, data, data
.....

Many thanks !!

 

[feherke]

Hi

No. That simple command would be the [tt]copy[/tt], but that can handle only single character delimiters, so the spaces after the commas will be considered part of the next column's data.

But you can easily clean up the csv file with a one-liner. ( All this works on Unix or Unix-based systems. On Windows you have to put the code in a file and execute from there. )

sed '1d;s/, /,/g' /tmp/keak.csv > /tmp/keak_2.csv

[gray]# or[/gray]

awk '{gsub(/, /,",")}NR>1' /tmp/keak.csv > /tmp/keak_2.csv

[gray]# or[/gray]

perl -pe '$_=""if$.==1;s/, /,/g' /tmp/keak.csv > /tmp/keak_2.csv

[gray]# or[/gray]

ruby -pe '$_=""if$.==1;gsub!(/, /,",")' /tmp/keak.csv > /tmp/keak_2.csv

Then you have to create the table and import the data :

[blue]db=#[/blue] [b]create table[/b] keak ( col1 [b]varchar[/b](256), col2 [b]varchar[/b](256), col3 [b]varchar[/b](256), col4 [b]varchar[/b](256) );
CREATE TABLE
[blue]db=#[/blue] [highlight #eee][b]copy[/b] keak [b]from[/b] [i]'/tmp/keak_2.csv'[/i] [b]delimiters[/b] [i]','[/i];[/highlight]
COPY
[blue]db=#[/blue] [b]select[/b] * [b]from[/b] keak;
 col1 | col2 | col3 | col4
------+------+------+------
 data | data | data | data
 data | data | data | data
 data | data | data | data
(3 rows)

Feherke.

http://rootshell.be/~feherke/

 

[keak]

Many thanks Feherke for that info!
Just for future reference, if the actual data contained the ',' (comma) character, is there any good way to import such a file?
I guess I will have to escape this comma with \' before I execute the copy command? It seems to be giving me a incorrrect number of fields when I try to import a line of that format.

 

[feherke]

Hi

Yes, that is an ugly situation. To import such data, you have to escape the literal commas ( , ) with backslashes ( \ ) :

[blue]db=#[/blue] [b]copy[/b] keak [b]from stdin delimiters[/b] [i]','[/i];
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> data,data,comma ->\,<- here,data
>> \.
[blue]db=#[/blue] [b]select[/b] * [b]from[/b] keak;
 col1 | col2 |       col3       | col4
------+------+------------------+------
 data | data | data             | data
 data | data | data             | data
 data | data | data             | data
 data | data | comma ->,<- here | data
(4 rows)

But to give headache, other programs usually quotes the values which contains the separator character, and PostgreSQL's method is not compatible with them.

I would prefer other separator character. My favorite is the Tab ( \t ).

Feherke.

http://rootshell.be/~feherke/