Thread: Load a csv file into a pgsql table
Greetings, *Except* copy command, are there other quick ways to load data from a csv file into a pgsql table please? Thanks a lot!
Define 'quick'. You could write a script that would transform a .csv file into an INSERT statement and save it to an .sql file. Or I suppose you could do silly ODBC stuff with MS Access. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, September 19, 2006 2:15 PM To: PgSQL General Subject: [GENERAL] Load a csv file into a pgsql table Greetings, *Except* copy command, are there other quick ways to load data from a csv file into a pgsql table please? Thanks a lot! ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
On Sep 19, 2006, at 11:15 AM, Emi Lu wrote: > Greetings, > > > *Except* copy command, are there other quick ways to load data from > a csv file into a pgsql table please? There are several bulk loaders, but I believe they all use COPY behind the scenes. If copy isn't an option then your best bet will be many inserts in a transaction, but that'll be significantly slower. You could cobble together a loader using perl, DBI and one of the CPAN CSV modules fairly easily. (But I can't think of any reason why you wouldn't use copy, so you must have some constraint you haven't mentioned - can you expand on why copy isn't an option?) Cheers, Steve
On Tue, 2006-09-19 at 13:27, Brandon Aiken wrote: > Define 'quick'. > > You could write a script that would transform a .csv file into an INSERT > statement and save it to an .sql file. > > Or I suppose you could do silly ODBC stuff with MS Access. > > -- > Brandon Aiken > CS/IT Systems Engineer > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Emi Lu > Sent: Tuesday, September 19, 2006 2:15 PM > To: PgSQL General > Subject: [GENERAL] Load a csv file into a pgsql table > > Greetings, > > > *Except* copy command, are there other quick ways to load data from a > csv file into a pgsql table please? Haven't seen the OP go by, but here's the one of the simplest csv loaders ever created. No guarantees to suitability implied or otherwise. #!/usr/bin/php -q <?php $tablename = $argv[1]; $filename = $argv[2]; if ($argc!=3){ echo "Usage:\n\n loadpg tablename filename\n"; exit; } if (!file_exists($filename)){ die ("given filename doesn't exist\n"); } print "copy $tablename from stdin;\n"; $fp = fopen($filename,"r"); while(!feof($fp)){ $line = fgetcsv($fp,4096); if (strlen($line)==0) continue(1); print implode("\t",$line); print "\n"; } print '\.'; print "\n"; ?> Note that you just redirect the output to psql and off you go.
Thank you for all the inputs. Actually, I am reluctant to do the update line by line. I plan to use a shell script to . replace all characters such as ' to \' . update each line to insert into . call "-c query " load the file into db In java, call this shell script, after data populated into tables, will do other data comparison based on this table then. >> You could write a script that would transform a .csv file into an INSERT >> statement and save it to an .sql file. >> >> Or I suppose you could do silly ODBC stuff with MS Access. >> >> -- >> Brandon Aiken >> CS/IT Systems Engineer >> >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org >> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Emi Lu >> Sent: Tuesday, September 19, 2006 2:15 PM >> To: PgSQL General >> Subject: [GENERAL] Load a csv file into a pgsql table >> >> Greetings, >> >> >> *Except* copy command, are there other quick ways to load data from a >> csv file into a pgsql table please? > > Haven't seen the OP go by, but here's the one of the simplest csv > loaders ever created. No guarantees to suitability implied or > otherwise. > > #!/usr/bin/php -q > <?php > $tablename = $argv[1]; > $filename = $argv[2]; > if ($argc!=3){ > echo "Usage:\n\n loadpg tablename filename\n"; > exit; > } > if (!file_exists($filename)){ > die ("given filename doesn't exist\n"); > } > print "copy $tablename from stdin;\n"; > $fp = fopen($filename,"r"); > while(!feof($fp)){ > $line = fgetcsv($fp,4096); > if (strlen($line)==0) continue(1); > print implode("\t",$line); > print "\n"; > } > print '\.'; > print "\n"; > ?> > > Note that you just redirect the output to psql and off you go.
Emi Lu wrote: > Greetings, > > > *Except* copy command, are there other quick ways to load data from a > csv file into a pgsql table please? Quick? No. Joshua D. Drake > > > Thanks a lot! > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/19/06 13:26, Steve Atkins wrote: > > On Sep 19, 2006, at 11:15 AM, Emi Lu wrote: > >> Greetings, >> >> >> *Except* copy command, are there other quick ways to load data from a >> csv file into a pgsql table please? > > There are several bulk loaders, but I believe they all use COPY behind > the scenes. > > If copy isn't an option then your best bet will be many inserts in a > transaction, but that'll be significantly slower. You could cobble > together a loader using perl, DBI and one of the CPAN CSV modules fairly > easily. > > (But I can't think of any reason why you wouldn't use copy, so you must > have some constraint you haven't mentioned - can you expand on why copy > isn't an option?) COPY has great facilities for specifying the physical layout of the CSV file, but is otherwise limited. Facilities that I miss are: statistics: loaded 10000 rows, loaded 20000 rows, ... etc. skip: if the COPY dies (or is killed after 10Mn rows have been loaded, it's so useful to be able to add "--skip=10425000" to the command and have the bulk loaded quickly scan to that record. Yes, tail(1) can slice off the unloaded records, but that means that now you have 2 files. Messy. transactions: goes hand-in-glove with statistics and skip. exceptions file: if you have a unique index on the table, and one of the input records is a duplicate, kick it out to an exceptions file, note it to stderr and keep on loading. A "fields" option would also be handy. This is for when the number of fields in the input file does not equal those in the table. Just MHO, of course. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFEFPsS9HxQb37XmcRAkxpAJ9czWEjP+lYDInS8dVeN9OLYY865wCfU0Fm /Z3FxL6o5XCU3SivPFQDVEc= =K438 -----END PGP SIGNATURE-----