Re: Need help doing a CSV import

On Wed, Jul 14, 2010 at 01:20:25PM +0000, Tim Landscheidt wrote:
> Craig Ringer <craig(at)postnewspapers(dot)com(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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