Re: Importing CSV File - Mailing list pgsql-general

On 10/27/2015 09:36 AM, Melvin Davidson wrote:
> The trick is to tell postgres where the data ends with \.

That is for all the data. What the OP was referring to was designating
where each row in the data ended.

>
>  From http://www.postgresql.org/docs/9.3/interactive/sql-copy.html
>
> End of data can be represented by a single line containing just
> backslash-period (\.). An end-of-data marker is not necessary when
> reading from a file, since the end of file serves perfectly well; it is
> needed only when copying data to or from client applications using
> pre-3.0 client protocol.
>
> So if you can somehow add the \. to the end of your data before the
> extraneous \r\n 's , it will solve your problem.
>
> The following works as a test:
>
> CREATE TABLE junk
> (
> pkey  integer,
> jdata varchar(10),
> CONSTRAINT junk_pk PRIMARY KEY (pkey)
> );
>
> TRUNCATE TABLE junk;
> COPY junk FROM '/tmp/junk.data' WITH CSV;
>
> contents of /tmp/junk.data:
> -------------------------------------------------------------------
> 1,'junk1'
> 2,'junk1'
> 3,'junk1'
> \.
> garbage data1
> more garbage
> ....
> blah
> enough alread
>
>
>
>
> On Tue, Oct 27, 2015 at 12:23 PM, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>     On 10/27/2015 10:04 AM, Adrian Klaver wrote:
>>     On 10/27/2015 08:44 AM, Jeff Janes wrote:
>>>     On Mon, Oct 26, 2015 at 2:45 PM, David Blomstrom
>>>     <david.blomstrom@gmail.com <mailto:david.blomstrom@gmail.com>
>>>     <mailto:david.blomstrom@gmail.com>
>>>     <mailto:david.blomstrom@gmail.com>> wrote:
>>>
>>>         I tried to import a CSV file into a PostgreSQL table using
>>>     pgAdmin
>>>         III. I got an error message: "extra data after last column."
>>>
>>>
>>>         All my spreadsheets have an "end of data" column that has
>>>     /r/n in
>>>         each cell. When I import a CSV file into a MySQL table,
>>>     everything
>>>         beyond /r/n is ignored. Is there some way to tell PostgreSQL
>>>     to stop
>>>         at /r/n?
>>>
>>>
>>>     How does it know when to stop ignoring and start the next record?
>>
>>     I wondered about that also. I did find this:
>>
>>     http://dev.mysql.com/doc/refman/5.7/en/load-data.html
>>
>>     LINES TERMINATED BY
>>
>>>
>>>     You could write a little awk or perl script to give the PROGRAM
>>>     option
>>>     of copy, but you can't do that within pgAdmin.
>>>
>>>     Cheers,
>>>
>>>     Jeff
>>
>>
>     Whence the csv file? If it starts out in spreadsheet, can you not
>     export only the columns you want in the database?
>
>     Google "postgres import tool" finds several options
>
>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com