Postgres Pro
Downloads
Home   >   mailing lists

Thread: Import fails on single quote.

Import fails on single quote.

From
dsbw
Date:
Hello,

Trying to import data, tab-delimited but unfortunately with quotes around string fields anyway. Like:

1234<tab>"JOHN"<tab>"SMITH"<tab>5678

PGAdmin import fails when the quoted value contains a single quote:

1234<tab>"EMPLOYEES'"<tab>"CREDIT UNION"<tab>5678

So the trailing quote (for possessive) after "EMPLOYEES" trips it up. Seems like, if one is inside a set of double-quotes, any single quotes should be ignored? (I know CSV is multiply and contradictorily defined, but still.)

===Blake===

Re: Import fails on single quote.

From
richard coleman
Date:
Blake, 

You might want to try escaping the single quotes in your data.  Either by doubling them up or switching double quotes for dollar-quoted strings.

Just a thought, 

rik.



On Tue, May 11, 2021 at 3:02 PM dsbw <dsbw@protonmail.com> wrote:
Hello,

Trying to import data, tab-delimited but unfortunately with quotes around string fields anyway. Like:

1234<tab>"JOHN"<tab>"SMITH"<tab>5678

PGAdmin import fails when the quoted value contains a single quote:

1234<tab>"EMPLOYEES'"<tab>"CREDIT UNION"<tab>5678

So the trailing quote (for possessive) after "EMPLOYEES" trips it up. Seems like, if one is inside a set of double-quotes, any single quotes should be ignored? (I know CSV is multiply and contradictorily defined, but still.)

===Blake===

Re: Import fails on single quote.

From
Aditya Toshniwal
Date:
Hi Dsbw,

Did you try changing the escape field ? It is by default a single quote.
image.png

On Wed, May 12, 2021 at 12:32 AM dsbw <dsbw@protonmail.com> wrote:
Hello,

Trying to import data, tab-delimited but unfortunately with quotes around string fields anyway. Like:

1234<tab>"JOHN"<tab>"SMITH"<tab>5678

PGAdmin import fails when the quoted value contains a single quote:

1234<tab>"EMPLOYEES'"<tab>"CREDIT UNION"<tab>5678

So the trailing quote (for possessive) after "EMPLOYEES" trips it up. Seems like, if one is inside a set of double-quotes, any single quotes should be ignored? (I know CSV is multiply and contradictorily defined, but still.)

===Blake===


--
Thanks,
Aditya Toshniwal
pgAdmin hacker | Sr. Software Engineer | edbpostgres.com
"Don't Complain about Heat, Plant a TREE"
Attachment

Re: Import fails on single quote.

From
dsbw
Date:
||You might want to try escaping the single quotes in your data.  Either by doubling them up or switching double quotes for dollar-quoted strings.||

It's not my data. That's why I'm importing it.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, May 11th, 2021 at 12:16 PM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
Blake, 

You might want to try escaping the single quotes in your data.  Either by doubling them up or switching double quotes for dollar-quoted strings.

Just a thought, 

rik.



On Tue, May 11, 2021 at 3:02 PM dsbw <dsbw@protonmail.com> wrote:
Hello,

Trying to import data, tab-delimited but unfortunately with quotes around string fields anyway. Like:

1234<tab>"JOHN"<tab>"SMITH"<tab>5678

PGAdmin import fails when the quoted value contains a single quote:

1234<tab>"EMPLOYEES'"<tab>"CREDIT UNION"<tab>5678

So the trailing quote (for possessive) after "EMPLOYEES" trips it up. Seems like, if one is inside a set of double-quotes, any single quotes should be ignored? (I know CSV is multiply and contradictorily defined, but still.)

===Blake===

Re: Import fails on single quote.

From
dsbw
Date:
OK, it's not as straightforward as an unpaired single quote. I just had one fail on:

"PET'1'"

So the single quote is paired, and caused the problem. So I changed it to "PET 1"...and the whole import worked. But I got suspicious and started looking at the data for single quotes and found things like "T'BIRD", "B--CH'N!!" and "JOHN'S WIFE" and those all worked.

So I'm not sure what the deal is.

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, May 11th, 2021 at 12:01 PM, dsbw <dsbw@protonmail.com> wrote:
Hello,

Trying to import data, tab-delimited but unfortunately with quotes around string fields anyway. Like:

1234<tab>"JOHN"<tab>"SMITH"<tab>5678

PGAdmin import fails when the quoted value contains a single quote:

1234<tab>"EMPLOYEES'"<tab>"CREDIT UNION"<tab>5678

So the trailing quote (for possessive) after "EMPLOYEES" trips it up. Seems like, if one is inside a set of double-quotes, any single quotes should be ignored? (I know CSV is multiply and contradictorily defined, but still.)

===Blake===

Re: Import fails on single quote.

From
"Basques, Bob (CI-StPaul)"
Date:

Probably because of the number on the end.  So its failing on the end of words, and a number on the end, still equals on the end of a word (to Postgres).

 

Bobb

 

 

 

From: dsbw <dsbw@protonmail.com>
Reply-To: dsbw <dsbw@protonmail.com>
Date: Thursday, May 13, 2021 at 1:45 PM
To: "pgadmin-support@lists.postgresql.org" <pgadmin-support@lists.postgresql.org>
Subject: Re: Import fails on single quote.

 

Think Before You Click: This email originated outside our organization.

 

OK, it's not as straightforward as an unpaired single quote. I just had one fail on:

 

"PET'1'"

 

So the single quote is paired, and caused the problem. So I changed it to "PET 1"...and the whole import worked. But I got suspicious and started looking at the data for single quotes and found things like "T'BIRD", "B--CH'N!!" and "JOHN'S WIFE" and those all worked.

 

So I'm not sure what the deal is.

 

Sent with ProtonMail Secure Email.

 

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, May 11th, 2021 at 12:01 PM, dsbw <dsbw@protonmail.com> wrote:

Hello,

 

Trying to import data, tab-delimited but unfortunately with quotes around string fields anyway. Like:

 

1234<tab>"JOHN"<tab>"SMITH"<tab>5678

 

PGAdmin import fails when the quoted value contains a single quote:

 

1234<tab>"EMPLOYEES'"<tab>"CREDIT UNION"<tab>5678

 

So the trailing quote (for possessive) after "EMPLOYEES" trips it up. Seems like, if one is inside a set of double-quotes, any single quotes should be ignored? (I know CSV is multiply and contradictorily defined, but still.)

 

===Blake===

 

Re: Import fails on single quote.

From
Aditya Toshniwal
Date:
I got "email delivery failed" so replying again.
Did you try changing the escape field ? It is by default a single quote.
image.png

On Fri, May 14, 2021 at 12:14 AM dsbw <dsbw@protonmail.com> wrote:
OK, it's not as straightforward as an unpaired single quote. I just had one fail on:

"PET'1'"

So the single quote is paired, and caused the problem. So I changed it to "PET 1"...and the whole import worked. But I got suspicious and started looking at the data for single quotes and found things like "T'BIRD", "B--CH'N!!" and "JOHN'S WIFE" and those all worked.

So I'm not sure what the deal is.

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, May 11th, 2021 at 12:01 PM, dsbw <dsbw@protonmail.com> wrote:
Hello,

Trying to import data, tab-delimited but unfortunately with quotes around string fields anyway. Like:

1234<tab>"JOHN"<tab>"SMITH"<tab>5678

PGAdmin import fails when the quoted value contains a single quote:

1234<tab>"EMPLOYEES'"<tab>"CREDIT UNION"<tab>5678

So the trailing quote (for possessive) after "EMPLOYEES" trips it up. Seems like, if one is inside a set of double-quotes, any single quotes should be ignored? (I know CSV is multiply and contradictorily defined, but still.)

===Blake===



--
Thanks,
Aditya Toshniwal
pgAdmin hacker | Sr. Software Engineer | edbpostgres.com
"Don't Complain about Heat, Plant a TREE"
Attachment

Re: Import fails on single quote.

From
dsbw
Date:
||I got "email delivery failed" so replying again.

That's good, I didn't see it.

Did you try changing the escape field ? It is by default a single quote.

I haven't, but I will and report back.
Attachment