Обсуждение: 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-----