Skip to main content
rafy
rafy
Gold ⭐️
May 18, 2022
Question

Can't Remove Special characters from json

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

araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
May 18, 2022

@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é

Translate
-- 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.
rafy
rafyAuthor
Gold ⭐️
May 19, 2022

Thanks so much.

But i have no control over the system generated json.

Translate
araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
May 19, 2022

@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é

 

Translate
-- 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.
Terms & ConditionsAccessibility statement