Splitting JSON string in Excel into several columns

[tuiv]

Hi all,

I have a JSON string in my Excel file (that also needs to be sanitized e.g., " instead of ').  It is a string with 23 items and I need to separate them into single columns.

{'_type': 'snscrape.modules.twitter.User', 'username': 'AineHrElephant', 'id': 1088039862467510273, 'displayname': 'The HR Elephant', 'description': 'We want to Embrace, Educate & Enable every business on this island to effectively lead & guide their ‘herd’ to success!\nTel: +44 ...'}

Type	'username	id	displayname	...
snscrape.modules.twitter.User	AineHrElephant	1088039862467510273	The HR Elephant	...
...	...	...	...	...

I have tried to parse the JSON string as well as text to columns but it did not work. Especially for latter I set the delimeters for "," and ":" by using two "text to column" items but due to the high amount of 23 items it is quite a lot of work. 

Can someone help? 

Thanks,

Tui 

[Luke_C]

Hi @tuiv 

How about updating the double quotes and using a JSON Parse tool? Seems to give good results.

[tuiv]

Hi Luke,

thanks. I already tried this but have the problem that I have individual rows per ites now instead of one column for _type, one column for username, one column for id etc.

Any function I can use here?

[Luke_C]

Yes @tuiv , use a cross tab tool. Noticed your screenshot seems to show a 2nd record, so I added a record ID to group this. 

I'd suggest taking a look at the interactive lessons in the learning section of the community.