Receiving double quotations from SQL query

Help
1

Dear Community,
I’m building a process that will parse through a JSON file that is stored as a string in a SQL server. On the server, the JSON string looks something like this:

[{“boundingBox”:“43,86,511,139”,“lines”:[{“boundingBox”:“44,86,418,67”,“words”:[{“boundingBox”:“44,86,188,67”,“text”:“Nasdaq”},{“boundingBox”:“244,91,44,49”,“text”:“&”},{“boundingBox”:“299,91,163,52”,“text”:“AMEX”}]},{“boundingBox”:“43,161,319,22”,“words”:[{“boundingBox”:“43,161,55,16”,“text”:“Stocks”},{“boundingBox”:“104,161,15,17”,“text”:“in”},
etc

but when I query it from UiPath, what is loaded into my datatable looks like this:
“[{”“boundingBox”“:”“43,86,511,139"”,““lines””:[{““boundingBox””:““44,86,418,67"”,”“words”“:[{”“boundingBox”“:”“44,86,188,67"”,““text””:““Nasdaq””},{““boundingBox””:““244,91,44,49"”,”“text”“:”“&”“},{”“boundingBox”“:”“299,91,163,52"”,““text””:““AMEX””}]},{““boundingBox””:““43,161,319,22"”,”“words”“:[{”“boundingBox”“:”“43,161,55,16"”,““text””:““Stocks””},{““boundingBox””:““104,161,15,17"”,”“text”“:”“in”"},
etc

For some reason, my quotations have become double quotations, which is throwing off the JSON deserialize operation downstream.

Anyone has an idea why it is doing this?
I know I probably could just use the replace activity and fix it, but I find this inelegant and would prefer to fix the problem from the source. Thanks!

Have a nice day,
Philippe

2

Buddy @Phil

Though this value might have only one " along the value, it will get doubled into “” when it is been copied to datatable as it would convert to string type which eventually doubles the " where it occurs,
rather to fix from the source, you can use string manipulation out here
like this
out_text = is the json value stored
then
out_text = out_text.ToString.Replace(“”“”,“”)
and that too you can convert it when you are passing this value from a datatable to a excel or a csv…as it is better to be in double quotes while being in datatable

Kindly try this and let know buddy
Cheers @Phil

3

Thanks Palaniyappan,
Is there any way I can prevent it from doubling the " when converting to string?

I thought of manipulating the string, but I can’t get it to work simply: if I use:
out_text = out_text.ToString.Replace(“”“”,“”)
I will kill all quotations in my JSON, breaking my key/value pairs and I get:
[{boundingBox:43,86,511,139,lines:[{boundingBox:44,86,418,67,words:

I still need to keep one instance of the “, but UiPath won’t let me have an uneven number of " in my operation (i.e. it does not accept out_text.ToString.Replace(”“”“,”“”)

Any idea how to do this?

Thanks!
Philippe

4

hello @Phil two double quotes are considered as empty string…
try below…
.replace(char(34) & char(34),char(34))

5

Hi Akshay,
Thanks for this. However, .replace(char(34) & char(34), char(34)) is throwing a compiler error saying " ‘.’ expected". Any idea what is up with that? Thanks!

Phil

6

Hi everyone,
Thanks for the help on this!

After playing around with it, I finally figured it out:

On the char point: it should have been chr(34) instead of char(34); that is what was throwing off the error.

On the overall point: the JSON deserializer never had an issue with the double quotes. What had changed is in the original version of my program, I was receiving a JSON object, vs in the new version, I am receiving a JSON array. Thus, I was using the wrong activity.

The double quotes still weirds me out, but the important this is it works.

Thanks again!
Phil

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.