issue with delimiter in field during COPY

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

Thanks Adrian,

Apologies, i'd assumed specifying "CSV" would work just for actual CSV files
with comma separation.

Thanks again

Andy

From: Adrian Klaver-3 [via PostgreSQL]
[mailto:ml-node+s1045698n4990887h21@n5.nabble.com]
Sent: 14 November 2011 14:24
To: LPlateAndy
Subject: Re: issue with delimiter in field during COPY

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
[hidden email]

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_____

If you reply to this email, your message will be added to the discussion
below:

http://postgresql.1045698.n5.nabble.com/issue-with-delimiter-in-field-during
-COPY-tp4990771p4990887.html

To unsubscribe from issue with delimiter in field during COPY, click here
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsu
bscribe_by_code&node=4990771&code=YW5keUBjZW50cmVtYXBzLmNvLnVrfDQ5OTA3NzF8LT
E3NDM2MTI2> .
See how NAML generates this email
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macr
o_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.B
asicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.templ
ate.InstantMailNamespace&breadcrumbs=instant+emails%21nabble%3Aemail.naml-in
stant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/issue-with-delimiter-in-field-during-COPY-tp4990771p4990893.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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