Support Questions
Find answers, ask questions, and share your expertise

Can't Remove Special characters from json

Rising Star

Hi,

I would like to convert this json to csv. But it failed because of the special characters in the json content. Below is the json i would like to convert to csv. So the responseMessage value is where the problem is & its auto-generated in that format. 

Thank you.

INPUT

{

"responseMessage": "hdhdhdhdh:dgdgdgdg "fdfdf" bbbbbbb",

"result": "failed",

"code": "34"

}

 

DESIRED OUTPUT

{

"responseMessage": "hdhdhdhdh dgdgdgdg fdfdf bbbbbbb",

"result": "failed",

"code": "34"

}

3 REPLIES 3

Master Collaborator

@rafy 

 

The extra character in the response message is not a special character. It's just some extra double-quotes. However, when double-quotes appear in a string in JSON they must be escaped. The correct valid JSON would've been:

{
  "responseMessage": "hdhdhdhdh:dgdgdgdg \"fdfdf\" bbbbbbb",
  "result": "failed",
  "code": "34"
}

 

The application that's generating those messages should be fixed to generate valid JSON representations.

 

Cheers

André

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

Rising Star

Thanks so much.

But i have no control over the system generated json.

Master Collaborator

@rafy ,

 

This is tricky and whatever solution we come up with here cannot be guaranteed to always work. It will always depend on the content that comes in the responseMessage field.

 

For the particular example that you shared above, this works:

araujo_0-1652932649010.png

The settings are: 

Search Value:      (?s)(.*\"responseMessage\"\s*:\s*\"[^\"]*|\G[^\"]*)\"(?!,)
Replacement Value: $1\\"
Evaluation Mode:   Entire text

 

You can check it out here: https://regex101.com/r/paPPhK/1

 

Cheers,

André

 

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.
; ;