Re: How to correctly include double quotes in an insert statement

Thanks for your reply.
The C# split function works fine and return the 4 fields I need (CODE,
DESCRIPTION, CATEGORY AND BRAND).
My only issue is with the DESCRIPTION field which contains double quotes
which denote the "inches" measurement.
When the record is inserted into the table, such double quotes are included
and this is causing problems.
My question is how to correctly "escape" double quotes, or maybe double
quotes should not be part of a table field content.

Best regards,
Jorge Maldonado

On Thu, Mar 28, 2019 at 6:04 AM Danny Severns <Danny(at)eidl-sw(dot)com> wrote:

> *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(at)gmail(dot)com>
> *Sent:* Wednesday, March 27, 2019 11:14 AM
> *To:* pgsql-novice <pgsql-novice(at)postgresql(dot)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
>