Splitting JSON string in Excel into several columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @tuiv
How about updating the double quotes and using a JSON Parse tool? Seems to give good results.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
