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"
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.
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)?
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?
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.
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.
If I import it into Access 2003 it ships Line 1001 and Line 1002 into an
import error table.
Worth adding to the TODO or not a good feature?
