Need help doing a CSV import
I am in the process of moving a FoxPro based system to PostgreSQL.
We have several tables that have memo fields which contain carriage
returns and line feeds that I need to preserve. I thought if I converted
these into the appropriate \r and \n codes that they would be imported as
carriage returns and line feeds, but instead they are stored in the
database as \r and \n.
The command I am using is:
copy main.company
from E'f:\company.csv"
DELIMITER "|"
CSV HEADER QUOTE '"' ESCAPE E'\\';
The command imports all of the data, but I want the escaped codes to be
expanded to their actual values.
Can anyone assist me with this?
On 14/07/2010 7:04 PM, tony@exquisiteimages.com wrote:
I am in the process of moving a FoxPro based system to PostgreSQL.
We have several tables that have memo fields which contain carriage
returns and line feeds that I need to preserve. I thought if I converted
these into the appropriate \r and \n codes that they would be imported as
carriage returns and line feeds, but instead they are stored in the
database as \r and \n.
PostgreSQL doesn't process escapes in CSV import mode.
You can reformat the data into the non-csv COPY format, which WILL
process escapes. Or you can post-process it after import to expand them.
Unfortunately PostgreSQL doesn't offer an option to process escapes when
"CSV" mode COPY is requested.
I posted a little Python script that reads CSV data and spits out
COPY-friendly output a few days ago. It should be trivially adaptable to
your needs, you'd just need to change the input dialect options. See the
archives for the script.
--
Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> wrote:
I am in the process of moving a FoxPro based system to PostgreSQL.
We have several tables that have memo fields which contain carriage
returns and line feeds that I need to preserve. I thought if I converted
these into the appropriate \r and \n codes that they would be imported as
carriage returns and line feeds, but instead they are stored in the
database as \r and \n.
PostgreSQL doesn't process escapes in CSV import mode.
You can reformat the data into the non-csv COPY format,
which WILL process escapes. Or you can post-process it after
import to expand them. Unfortunately PostgreSQL doesn't
offer an option to process escapes when "CSV" mode COPY is
requested.
I posted a little Python script that reads CSV data and
spits out COPY-friendly output a few days ago. It should be
trivially adaptable to your needs, you'd just need to change
the input dialect options. See the archives for the script.
Another option is a small Perl script or something similar
that connects to both the FoxPro and the PostgreSQL database
and transfers the data with parameterized "INSERT". The ad-
vantage of this is that you have tight control of charsets,
date formats, EOL conventions & Co. and do not have to won-
der whether this and that file is in this and that stage of
the conversion process, the disadvantage is obviously that
you lose any speed benefit of bulk "COPY".
Tim
On Wed, Jul 14, 2010 at 01:20:25PM +0000, Tim Landscheidt wrote:
Craig Ringer <craig@postnewspapers.com.au> wrote:
I am in the process of moving a FoxPro based system to PostgreSQL.
We have several tables that have memo fields which contain carriage
returns and line feeds that I need to preserve. I thought if I converted
these into the appropriate \r and \n codes that they would be imported as
carriage returns and line feeds, but instead they are stored in the
database as \r and \n.PostgreSQL doesn't process escapes in CSV import mode.
You can reformat the data into the non-csv COPY format,
which WILL process escapes. Or you can post-process it after
import to expand them. Unfortunately PostgreSQL doesn't
offer an option to process escapes when "CSV" mode COPY is
requested.I posted a little Python script that reads CSV data and
spits out COPY-friendly output a few days ago. It should be
trivially adaptable to your needs, you'd just need to change
the input dialect options. See the archives for the script.Another option is a small Perl script or something similar
that connects to both the FoxPro and the PostgreSQL database
and transfers the data with parameterized "INSERT". The ad-
vantage of this is that you have tight control of charsets,
date formats, EOL conventions & Co. and do not have to won-
der whether this and that file is in this and that stage of
the conversion process, the disadvantage is obviously that
you lose any speed benefit of bulk "COPY".
You can do your transformations and hand the stream off to the COPY
interface. See the pg_putcopydata() section of the DBD::Pg manual for
examples. :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> wrote:
[...]
Another option is a small Perl script or something similar
that connects to both the FoxPro and the PostgreSQL database
and transfers the data with parameterized "INSERT". The ad-
vantage of this is that you have tight control of charsets,
date formats, EOL conventions & Co. and do not have to won-
der whether this and that file is in this and that stage of
the conversion process, the disadvantage is obviously that
you lose any speed benefit of bulk "COPY".
You can do your transformations and hand the stream off to the COPY
interface. See the pg_putcopydata() section of the DBD::Pg manual for
examples. :)
Eh, yes, but then you have to do all the escaping yourself
and the simplicity of "get values A, B, C from this connec-
tion and pass it onto that" goes away :-). Now if there'd be
a "pg_putcopydata(array of arrayrefs)" ... :-).
Tim