From: | JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> |
---|---|
To: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | How to correctly include double quotes in an insert statement |
Date: | 2019-03-27 16:13:46 |
Message-ID: | CAAY=A78uEcxcdPjoaxVJkJQuzh6i1DGRrH5wdkU_y2JKXgD1wQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello,
I have a tab-delimited text file from which I have to populate a table.
After reviewing the data in such a text file I noticed that there are
fields containing double quotes.
For example, I found several rows like this:
--------------------------------------------------------------------------------------------------------------------------------------------
CODE DESCRIPTION
CATEGORY BRAND
--------------------------------------------------------------------------------------------------------------------------------------------
ACEITERA-102 "ACEITERA GOTA VISIBLE 102mm 4"" X 3/4""" LUBRICANTES RICSA
ACEITERA-13 "ACEITERA GOTA VISIBLE 25mm 1"" X 1/4""" LUBRICANTES
RICSA
ACEITERA-32 "ACEITERA GOTA VISIBLE 32mm 1-1/4"" X 1/4""" LUBRICANTES
RICSA
As you can see, the DESCRIPTION field is surrounded by double quotes.
Also, measurement in inches are included using a pair of double quotes.
I am writing code to perform the export process to a table and I already
removed the double quotes that surround the DESCRIPTION and also one of the
double quotes that represent the inches.
So, when the export process runs, each line is converted to the following
format before performing the INSERT operation:
ACEITERA-102 ACEITERA GOTA VISIBLE 102mm 4" X 3/4" LUBRICANTES RICSA
ACEITERA-13 ACEITERA GOTA VISIBLE 25mm 1" X 1/4" LUBRICANTES RICSA
ACEITERA-32 ACEITERA GOTA VISIBLE 32mm 1-1/4" X 1/4" LUBRICANTES
RICSA
However, I can see that using double quotes still causes some issues later
when running our system.
I have also tried to escape double quotes using \" without success like
this:
ACEITERA-102 ACEITERA GOTA VISIBLE 102mm 4\" X 3/4\" LUBRICANTES RICSA
ACEITERA-13 ACEITERA GOTA VISIBLE 25mm 1\" X 1/4\" LUBRICANTES
RICSA
ACEITERA-32 ACEITERA GOTA VISIBLE 32mm 1-1/4\" X 1/4\" LUBRICANTES
RICSA
My C# code reads each row of the text file and splits it to get each field
separately.
The result of the split function is a string array of 4 items, for example:
a[0] contains the CODE
a[1] contains the DESCRIPTION
a[2] contains the CATEGORY
a[3] contains the BRAND
I have read several proposed solutions by googling the subject but I have
not been able to make it work correctly.
I will very much appreciate your feedback.
Respectfully,
Jorge Maldonado
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Alberto Rodriguez | 2019-03-27 19:08:51 | ERROR on database index |
Previous Message | Gavin M2301 | 2019-03-20 04:14:12 | Fixed mingw |