Thread: import csv file into table

I think this is possible with postgresql using the copy to command.  The
file I have has no blank spaces in it and uses ',' for field separators
and ' for beginning and end of records and has no multi-line records in
it.  Earlier I tried to use the csvfix utility and sql_insert subcommand
to generate insert statements for postgresql.  The record format had so
many errors in it I deleted the file it produced and deleted the utility
from this system.  Has anyone on this list done what I'm about to try?



--

I can remove the single quotes from beginning and ends of all records
easily.  Do the single quotes need to be removed from around all fields
or just from the beginnings and ends of records?  I have no comas inside
of fields in the file.  The file first came from emacs-orgmode then was
edited to create the csv file.
Thanks much for help on this problem and thanks for the link to the
pgloader package I'll check that out shortly.

On Fri, 12 Jun 2015, David G. Johnston wrote:

> Date: Fri, 12 Jun 2015 12:52:17
> From: David G. Johnston <david.g.johnston@gmail.com>
> To: Jude DaShiell <jdashiel@panix.com>
> Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
> Subject: Re: [NOVICE] import csv file into table
>
> On Fri, Jun 12, 2015 at 11:38 AM, Jude DaShiell <jdashiel@panix.com> wrote:
>
>> ?[...]
>>  and ' for beginning and end of records
>> ?[...]
>>
>
> ?This is the problem.  You need to remove the first and last single-quote
> and ensure that fields do not contain embedded commas.  COPY will work if
> those two things hold true.  Embedded commas and field quoting are the main
> difficulties with using comma-separated format.?
>
> COPY is efficient but it expects you to provide clean data to it.  You need
> to fix those items externally to PostgreSQL.
>
> I have not used it myself but you might evaluate pgloader.
> http://pgloader.io/
>
> David J.
>

--

If the field separator is <single-quote comma single-quote> then the first and last single-quotes complete the first and last field quotes Respectively and should not be removed.  You simply need to specify <comma> as your field separator and single-quote and your quote symbol.  The documentation for copy explains how you do this.

Hi

On 13 Jun 2015 1:01 PM, "Jude DaShiell" <jdashiel@panix.com> wrote:
>
> I can remove the single quotes from beginning and ends of all records easily.  Do the single quotes need to be removed from around all fields or just from the beginnings and ends of records?  I have no comas inside of fields in the file.  The file first came from emacs-orgmode then was edited to create the csv file.

(Sorry for any mangled quoting. Replying from my phone.)

It would help a lot if you provided a representative line from the csv file.

--
Michael Wood

Not a problem.

'2014-05-19','116','68','64','93'
On Sat, 13 Jun 2015, Michael Wood wrote:

> Date: Sat, 13 Jun 2015 09:55:42
> From: Michael Wood <esiotrot@gmail.com>
> To: Jude DaShiell <jdashiel@panix.com>
> Cc: PostgreSQL List - Novice <pgsql-novice@postgresql.org>,
>     David G. Johnston <david.g.johnston@gmail.com>
> Subject: Re: [NOVICE] import csv file into table
>
> Hi
>
> On 13 Jun 2015 1:01 PM, "Jude DaShiell" <jdashiel@panix.com> wrote:
>>
>> I can remove the single quotes from beginning and ends of all records
> easily.  Do the single quotes need to be removed from around all fields or
> just from the beginnings and ends of records?  I have no comas inside of
> fields in the file.  The file first came from emacs-orgmode then was edited
> to create the csv file.
>
> (Sorry for any mangled quoting. Replying from my phone.)
>
> It would help a lot if you provided a representative line from the csv file.
>
>

--

SOLVED!  What worked was:
\COPY health FROM health.csv WITH csv DELIMITER ',' QUOTE '"'

I think I ended all of that with a semicolon but psql didn't complain.
Thanks much for your interest assistance and listening.



--