Re: Copy From CSV feature request?

mike wrote:
> I import some of my data into my postgres database, win32 platform, via
> the COPY table FROM with CSV. My CSV file is created from a Crystal
> Report (v.9). I run the report and have Crystal export the results into
> a CSV file (using the default settings).
>
> I have some data which looks like this when stored in the source
> application (m$ sql server 2000) and Crystal:
>
> Line 1000 1/1/2004 Company2 Person2 Misc
> Line 1001 1/1/2004 Company3 Person3 " Nickname Misc
> Line 1002 1/1/2004 Company3 Person3 " Nickname Misc
> Line 1003 1/1/2004 Company4 Person4 Misc
> Line 1004 1/1/2004 Company5 Person5 Misc
>
> When I export the report as a CSV file Crystal exports it like this:
>
> Line 1000 "1/1/2004","Company2","Person2","Misc"
> Line 1001 "1/1/2004","Company3","Person3 " Nickname","Misc"
^^^
This is just wrong. Double-quotes inside double-quoted fields need to be
escaped by doubling. As, e.g. here:
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm

> Line 1002 "1/1/2004","Company3","Person3 " Nickname","Misc"
> Line 1003 "1/1/2004","Company4","Person4","Misc"
> Line 1004 "1/1/2004","Company4","Person4","Misc"
>
> Now when I execute the COPY function it aborts on Line 1002 with the
> error "extra data after last expected column". I am not sure why it
> does not abort on Line 1001.

It's probably seeing the end-of-line as part of the last field on line 1001.

> If Crystal is exporting the data in the correct format should COPY FROM
> CSV be modified to handle an odd number of text qualifiers in a row?
> Does anyone know if this is a valid format (no escape character
> automatically inserted)?

Nope - it's a bad format. Not that there is actually a standard
definition of what CSV is, AFAIK.

> Could COPY be modified to accept the data without having to insert a "
> someplace in the row so that the original data appears the same?

How does PG determine that what you've got is an unescaped " rather than
a missing comma?

> Would it be too much of a performance hit to do this?
>
> How other applications handle it:
>
> Only Excel 2003 seems to display the data correctly.

Well, it silently guesses what you want and gets it right this
particular time.

> If I open the CSV file using OpenOffice Calc 2.0 it combines Line 1001
> and Line 1002 into one row.
>
> If I import the data back into Crystal the data after Person3 does not
> appear.

There's a sign that you've got a bug. If Crystal can't read what it
writes then I'm not sure you can expect anyone else to do so reliably.

> If I import it into Access 2003 it ships Line 1001 and Line 1002 into an
> import error table.

Quite right too. It's one of the areas where Access does the right thing :-)

> Worth adding to the TODO or not a good feature?

I'm not a developer, but it strikes me as double plus ungood.

--
Richard Huxton
Archonet Ltd