RE: How to correctly include double quotes in an insert statement - Mailing list pgsql-novice
| From | Danny Severns |
|---|---|
| Subject | RE: How to correctly include double quotes in an insert statement |
| Date | |
| Msg-id | d22dfea2b3c24305a901e2cbe7e9a968@JANDC000-MBN16.ventech.com Whole thread Raw |
| In response to | How to correctly include double quotes in an insert statement (JORGE MALDONADO <jorgemal1960@gmail.com>) |
| Responses |
Re: How to correctly include double quotes in an insert statement
(JORGE MALDONADO <jorgemal1960@gmail.com>)
|
| List | pgsql-novice |
If I understand your question correctly, your C# code that splits the description field needs to be expanded to 8 fields instead of 4 and drop the X between the last 2 dimensions, and convert the fractions to decimals, like so:
a[0] contains the CODE
a[1] contains the DESCRIPTION
a[2] contains the THICKNESS // thickness being an integer of the 102, 25, 32, if it is never a fraction
a[3] contains the UOM // unit of measure being mm or whatever if this varies
a[4] contains WIDTH // convert 1-1/4 to 1.25, convert fractions to decimals
a]5] contains Height // same as other dimensions
a[6] contains the CATEGORY
a[7] contains the BRAND
This assumes your table carries the dimensions separately, since you did not give the table schema, which is actually what should determine how to split the field. If the dimensions are carried as part of the description in the table, then remove all the quotes when you parse the dimension description. If thickness is carried as part of the description and width and height are carried as a single field, then remove all quotes and split it into 5 fields. The point is, I would parse the description and split it to match the table.
If you are already splitting the description to match the db table, and your last sentence and question of “I have not been able to make it work correctly” means your C# code to split the description or to remove all quotes, is not working, then list your split/remove quotes routine so it can be reviewed for comment. In other words, is the problem how to split the description (a database question), or is the problem coding the routine to perform the split or to remove quotes (a C# question), or all three?
All the above assumes you have no control over the code that creates the csv or text file, so improving how the description and measurements are handled, is not an option. It that is not true, then I would first change that code to expand the fields.
HTH
Danny
From: JORGE MALDONADO <jorgemal1960@gmail.com>
Sent: Wednesday, March 27, 2019 11:14 AM
To: pgsql-novice <pgsql-novice@postgresql.org>
Subject: How to correctly include double quotes in an insert statement
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
pgsql-novice by date: