Thread: Importing into Postgres from a csv file
Does any one know if this is possible, and if so how? Jake
"Jake" <ccrasoro@home.com> writes: ' Does any one know if this is possible, and if so how? With my present knowledge, I would either use a Perl script to create a file that looks like the file you get when you dump a table, or I would use the DBI/DBD::Pg modules to insert the rows, one by one. The method I choose would depend on whether the data replaces the old data or is added to it. I just bought the "Programming the Perl DBI" book published by O'Reilly, so needless to say, I am not quite ready to import a cvs file yet. -- David Steuber | "Are you now, or have you ever been, a member NRA Member | of the NRA?" --- HUAC, 2004 Happiness is a SAAB Gripen <http://www.gripen.saab.se/> in the garage, an FN-FAL in the safe, and an HK P7M8 on the hip.
----- Mensaje original -----
De: "Jake" <ccrasoro@home.com>
Para: <pgsql-general@postgresql.org>
Enviado: miércoles, 23 de agosto de 2000 21:56
Asunto: [GENERAL] Importing into Postgres from a csv file
> Does any one know if this is possible, and if so how?
Use copy command:
COPY table FROM 'data.csv'
USING DELIMITERS ','
WITH NULL AS 'null string'
;
I think that you can do this using the COPY verb in psql. COPY table FROM file USING DELIMITERS ','; Just be sure that the order of the data in the file is the same as the order of the variables in the table. John On Wed, 23 Aug 2000, Jake wrote: > Does any one know if this is possible, and if so how? > > > Jake > >
John McKown wrote: > > I think that you can do this using the COPY verb in psql. > > COPY table FROM file USING DELIMITERS ','; > > Just be sure that the order of the data in the file is the same as the > order of the variables in the table. > > John > > On Wed, 23 Aug 2000, Jake wrote: > > Does any one know if this is possible, and if so how? > > > > > > Jake > > > > COPY table FROM '/usr/file' USING DELIMITERS ','; with the complete path. Jerome.
On Thu, 24 Aug 2000, John McKown wrote: > I think that you can do this using the COPY verb in psql. > > COPY table FROM file USING DELIMITERS ','; Be aware you will probably have to start psql as the postgres (superuser) to use the COPY command. But ... the \copy version is usable by normal users. Spent the best part of this morning playing with it. Testing - stress testing - my system and PostgreSQL. Importing 187 files of approx. 150,000 records with 4 fields (float4, float4, float4, interger). Was taking about 8 seconds per file on a PII/350 w/ 256 MByte RAM. Hope my partition survives the process (system's at home) since it was only 2 GByte with 1% used. Rod -- Roderick A. Anderson raanders@altoplanos.net Altoplanos Information Systems, Inc. Voice: 208.765.6149 212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
On Thu, 24 Aug 2000, Roderick A. Anderson wrote: > On Thu, 24 Aug 2000, John McKown wrote: > > > I think that you can do this using the COPY verb in psql. > > > > COPY table FROM file USING DELIMITERS ','; > > Be aware you will probably have to start psql as the postgres (superuser) > to use the COPY command. > > But ... the \copy version is usable by normal users. Why do you need to be the postgres superuser to do this this? I admin that I have only used the COPY table FROM STDIN; version of the command. It works under my "normal" id. Just curious, John
> On Thu, 24 Aug 2000, Roderick A. Anderson wrote: > > > On Thu, 24 Aug 2000, John McKown wrote: > > > > > I think that you can do this using the COPY verb in psql. > > > > > > COPY table FROM file USING DELIMITERS ','; > > > > Be aware you will probably have to start psql as the postgres (superuser) > > to use the COPY command. > > > > But ... the \copy version is usable by normal users. > > Why do you need to be the postgres superuser to do this this? I admin that > I have only used the COPY table FROM STDIN; version of the command. It > works under my "normal" id. > I believe this was for security reasons. When the backend performs a COPY, as opposed to the client, it executes the command as the Unix user "postgres". Therefore, if Joe User could have the backend perform the COPY, he could overwrite contents of the $PGDATA tree at will (or any other files which should only be modified by the server itself). Hope that helps, Mike Mascari
Jake wrote:
>
> Does any one know if this is possible, and if so how?
>
> Jake
I have a perl script to do this which you are welcome to if you want
it. I found problems using the:
COPY ... DELIMITER ',' ...
syntax because it didn't (seem to me to) handle data enclosed in quotes,
which might contain quotes and newlines. I.e. something like:
1,"Fred O'Neill said ""Hello!""
""Oh! Hi!"" said Elizabeth.",37,21/3/2000
I had a lot of this sort of stuff in my data, which was exported from a
Microsoft product. A perl script also let me handle nulls more
flexibly.
Regards,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
You sure can...
put the file somewhere on your db server.. let's just say /tmp and the file
is called data.db
su - postgres
psql [dbname]
copy TABLE from '/tmp/data.db' using delimiters ',';
That'll do ya.. Make sure that the columns are in the right order too... :)
Here's the syntax for the copy command
COPY [ BINARY ] table [ WITH OIDS ]
FROM { 'filename' | stdin }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
COPY [ BINARY ] table [ WITH OIDS ]
TO { 'filename' | stdout }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
Jake <ccrasoro@home.com> wrote in message
news:y%Vo5.24467$eR5.721567@news1.rdc1.on.wave.home.com...
> Does any one know if this is possible, and if so how?
>
>
> Jake
>
>