From: | Robert Buckley <robertdbuckley(at)yahoo(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | create a script which imports csv data |
Date: | 2012-06-28 11:53:07 |
Message-ID: | 1340884387.36134.YahooMailNeo@web133002.mail.ir2.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have to create a script which imports csv data into postgresql ...and have a few questions about the best way to do it.
The csv data is automatically created from an external database so I have no influence over which columns etc are downloaded.
The csv comes without an fid field and has therefore no unique identifier.
How can I best create a table for the import?
Would I first create a table without an fid and then after the import create a sequence and add the sequence to the table, then somehow update the fid field?
could anyone show me the best way to do this?
at the moment I am doing this...which makes postgresql throw an error because obviously the 'fid' field is missing from the csv data!
CREATE SEQUENCE fid_seq INCREMENT 1 START 1;
CREATE TABLE Anlagenregister_Aktuell_2011 (
fid INTEGER NOT NULL DEFAULT nextval('fid_seq'),
Firma TEXT,
Anlagenschluessel TEXT,
Anschrift TEXT,
PLZ TEXT,
Ort TEXT,
Bundesland TEXT,
Energietraeger TEXT,
Inbetriebnahmedatum DATE,
Netzzugangsdatum DATE,
Netzabgangsdatum DATE,
Ausserbetriebnahmedatum DATE,
Installierte_Leistung_kW numeric(11,4),
Lastgangmessung TEXT,
Einspeisemanagement TEXT,
Spannungsebene TEXT,
Zaehlpunktbezeichnung TEXT,
Anlagentyp TEXT,
Geographische_Koordinaten TEXT,
Schalloptimierung TEXT,
Biomasse_KWK_Bonus TEXT,
Biomasse_Technologie_Bonus TEXT,
PRIMARY KEY (fid)
);
copy Anlagenregister_Aktuell_2011 FROM '/var/www/Anlagenregister_Aktuell_2011.csv' DELIMITERS ';' CSV;
thanks,
Rob
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2012-06-28 11:59:56 | Re: create a script which imports csv data |
Previous Message | Marc Mamin | 2012-06-28 10:00:30 | function lookup using a "real" function call |