Re: Need help doing a CSV import

On 07/13/2010 04:25 PM, DMan65 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.
>
> 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.
>
Have you tried just leaving them as newlines within quoted fields. A
quick test shows the following:

CSV file (3 records, one with a field split across two lines):
"a test line"
"another test
line here"
"Third record"

steve=# create table zzz (foo serial, bar text);
steve=# \copy zzz (bar) from zzz csv
steve=# select * from zzz
;
foo | bar
-----+--------------
1 | a test line
2 | another test
: line here
3 | Third line
(3 rows)

Cheers,
Steve