Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Import delimited files to postgresql

Status
Not open for further replies.

rrgkanth

Programmer
Nov 16, 2005
35
US
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.
 
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 :)
 
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.
Code:
[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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top