Thread: issue with delimiter in field during COPY

Hi,

Using COPY to bring data into a table. It uses "|" which i allow for with a
command such as:

COPY mydata FROM E'C:\\mydata\\mydata.txt' USING DELIMITERS '|'

This works fine except for a url field which randomly includes a pipeline
"|" character. Despite the url being in quotation (" ") marks, the field is
split in two resulting in an unexpected number of columns.

Looking at the data imported, i can see that the quotation marks seem to
have been imported as well rather than the content recognised as text.

Is there any way to force the system to recognise the quotation marks, the
info in postgres 9.0 documentation seems to suggest that it is only possible
to specify the quotation marks using 'quote' for CSV format (along with
specifying the header).

Any help much appreciated...

Andy


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/issue-with-delimiter-in-field-during-COPY-tp4990771p4990771.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi,

My apologies for the previous post, i reread the documentation and realised
that a "CSV" load can actually use a file with delimiters other than a
comma.

As such, i have answered my own question.

Thanks

Andy

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/issue-with-delimiter-in-field-during-COPY-tp4990771p4990883.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Monday, November 14, 2011 5:39:41 am LPlateAndy wrote:
> Hi,
>
> Using COPY to bring data into a table. It uses "|" which i allow for with a
> command such as:
>
> COPY mydata FROM E'C:\\mydata\\mydata.txt' USING DELIMITERS '|'
>
> This works fine except for a url field which randomly includes a pipeline
> "|" character. Despite the url being in quotation (" ") marks, the field is
> split in two resulting in an unexpected number of columns.
>
> Looking at the data imported, i can see that the quotation marks seem to
> have been imported as well rather than the content recognised as text.
>
> Is there any way to force the system to recognise the quotation marks, the
> info in postgres 9.0 documentation seems to suggest that it is only
> possible to specify the quotation marks using 'quote' for CSV format
> (along with specifying the header).

Yes, you will need to use CSV format to specify a quote character. No you do not
have to specify a HEADER.  The HEADER option is a toggle, if it is present it
indicates that there is a header row. If it is not present than that indicates
there is no header.

>
> Any help much appreciated...
>
> Andy
>
>
> --


--
Adrian Klaver
adrian.klaver@gmail.com

On Monday, November 14, 2011 6:27:10 am LPlateAndy wrote:
> Thanks Adrian,
>
>
>
> Apologies, i'd assumed specifying "CSV" would work just for actual CSV
> files with comma separation.

No problem. CSV has become a generic term describing separated value files of all
types.

>
>
>
> Thanks again
>
>
>
> Andy
>
>
--
Adrian Klaver
adrian.klaver@gmail.com