Re: Bug in COPY from CSV?

If you have command line support for PHP it is fairly easy to
write a program that would use "pg_escape_string" to condition
the data. You could even use "pg_copy_to" to send the data
to the db as well.

On Mon, 2005-14-02 at 22:30 -0500, Bruce Momjian wrote:
> I did some research on this and it turns out it is one of our TODO
> items. It is:
>
> o Allow COPY FROM ... CSV to interpret newlines and carriage
> returns in data
>
> This would require major refactoring of the copy source code.
>
> We are actually careful to warn people who dump out data with newlines
> in CSV format:
>
> test=> insert into t values (4, 'lkjasdf
> test'> lkjasdf
> test'>
> test'>
> test'>
> test'> aaaa');
> INSERT 542038 1
> test=> copy t to '/bjm/3' with null as '' csv quote as '"';
> WARNING: CSV fields with embedded linefeed or carriage return characters might not be able to be reimported
> COPY
>
> Someone has come up with a patch which might allow this so it might work
> in 8.1.
>
> I am attaching a reproducable case of your report.
>
> ---------------------------------------------------------------------------
>
> Rick Schumeyer wrote:
> > I think I've found a bug in PG 8.0 that occurs while copying from CSV files.
> >
> > I checked the bugs list but didn't see anything similar.
> >
> >
> >
> > This occurs when reading a CSV file where one of the 'text' fields has
> >
> > a blank line. I included an example that shows the problem below.
> >
> > I found that if I change the blank line to have one space, pg reads
> >
> > it just fine.
> >
> >
> >
> > If this is indeed a bug, if someone could let me know the best
> >
> > way to address it, I would appreciate it.
> >
> >
> >
> > --EXAMPLE
> >
> > create table t (
> >
> > id integer,
> >
> > description text
> >
> > );
> >
> >
> >
> > copy t from stdin with null as '' csv quote as '"';
> >
> > 1,"Now is the time"
> >
> > 2,"for all good men
> >
> > to come
> >
> > to the
> >
> >
> >
> > aid of their party"
> >
> > 3,"The quick brown fox"
> >
> > \.
> >
> > --END EXAMPLE
> >
> >
> >
> > The above example produces this output:
> >
> >
> >
> > CREATE TABLE
> >
> > psql:test2.sql:8: ERROR: unterminated CSV quoted field
> >
> > CONTEXT: COPY t, line 5: ""
> >
> >
> >
>
> plain text document attachment (/bjm/0)
> CREATE TABLE t (
> id INTEGER,
> description TEXT
> );
> COPY T FROM stdin WITH NULL AS '' CSV QUOTE AS '"';
> 1,"Now is the time"
> 2,"for all good men
> to come
> to the
>
> aid of their party"
> 3,"The quick brown fox"
> \.
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787