Import delimited files to postgresql

[rrgkanth]

Hi,

I am new to postgresql and need some advice on its capabilities.

We are looking at importing delimited files into postgresql. However, there is some tranformation which needs to be done on the way.

Are there any options like that available? Looking for something similar to MS DTS or Oracle Bulk loader.

Please advise asap.

Thanks,
rrg.

 

[BSewell]

what do you need done?

I recently imported CSV files myself. What I did was to create a script to import the CSV's and saved it as a txt file. Then you simply type in psql dbname -f script.txt into command prompt.

Really handy, as I found in my case postgres didnt like fields that had dates and currency values in them. Ended up having to edit the CSV's which was easily enough and then ran the script again. Really saves you alot of time

 

[feherke]

Hi

I see neither BSewell has a solution for that "some tranformation which needs to be done on the way". Maybe I am right when I think there is no way for that in PostgreSQL. Anyway, I would not even try such thing. I would prefer to import in a temporary table, then do the transformation while moving to the permanent table. Then there would be no problem with different data types or formatting.

[gray]to put this data :[/gray]
[blue]master #[/blue] cat datecurrency.txt
26/6/2006       $1.25
27/6/2006       $2
28/6/2006       $3.5

[gray]into this table :[/gray]
[blue]master #[/blue] psql -d notforwomen -U develop -c '\d datecurrency'       Table "public.datecurrency"
  Column  |       Type       | Modifiers
----------+------------------+-----------
 date     | date             |
 currency | double precision |

[gray]I would do this :[/gray]
[blue]master #[/blue] psql
Welcome to psql 7.3.9, the PostgreSQL interactive terminal.

[blue]master=#[/blue] [b]create temporary table[/b] dctemp ( date [b]text[/b], currency [b]text[/b] );
CREATE TABLE

[blue]master=#[/blue] [b]copy[/b] dctemp [b]from[/b] [i]'/tmp/datecurrency.txt'[/i];
COPY

[blue]master=#[/blue] [b]insert into[/b] datecurrency [b]select[/b] to_date(date,[i]'DD/MM/YYYY'[/i]),trim(currency,[i]'$ '[/i])::[b]double precision from[/b] dctemp;
INSERT 0 3

[blue]master=#[/blue] [b]select[/b] * [b]from[/b] datecurrency;
    date    | currency
------------+----------
 2006-06-26 |     1.25
 2006-06-27 |        2
 2006-06-28 |      3.5
(3 rows)

Feherke.

http://rootshell.be/~feherke/