Split name value pairs of JSON statements

Hello everyone

In JSON the data is stored in name value paired separated by comma .. and I would like to split those data into adjacent columns
The problem is that there are values containing comma and this is confusing for the split process
How can I split the values to adjacent columns?

I have attached sample workbook ..
I prefer if possible dealing with arrays. That's to store the range("a1").currentregion in an array then to manipulate the process

*another point: can I specify the desired keys in array. I mean something like that
sKeys=Array("Title","Awards","Ratings" and so on)

Thanks advanced for help

The problem is that there are values containing comma and this is confusing for the split process

Could we say that the border is

","

If answer to previous question is YES then try next code
Note: There is an empty row at the end ... delete it

Please Login or Register  to view this content.

Thanks a lot for this great solution.
The problem that the results are incorrect for two keys
After testing your code I copied the range("B2:Z2") and pasted special as values in range("A6") and checked Transpose so as to see the results well
and I got this result for that range
Untitled.png

Also to make sure of the keys ..Copy the value if A1 and paste it in the following link
https://jsonlint.com/
And click 'Validate JSON' to see the results

What is or are the keys to split the data, is it "," as used by last macro ???

Thanks a lot for reply Mr. Kyle
I have no experience at dealing with JSON parser so I posted the thread so as to find a solution and to learn something new ..
I would appreciate a lot if you help me
Thanks a lot for replies excel masters

What do you mean that you have no experience? You’ve been using it in our other threads and asking for help with it on Eileen’s Lounge where Hans has been helping you.

That is is the best way of dealing with this kind of data, putting it onto a sheet just makes it harder as it’s not in a format that a table can represent. As I put in the post above, the data is not all in the format key:value

Use the parser, stick at it, you’ll get there!

Thanks a lot
I am new to those stuff and I am learning from posting such problems ..
As a desired output I need to put each value in a column or field even if it was an object so as for 'Ratings' field as an example
the desired output would be string

Please Login or Register  to view this content.

Also I need to get the value without the keys as the keys supposed to be headers .. and the order of the values preferred to be in an array order
sHeader=Array("Title","Awards","DVD","Ratings") ..
Hope it is clear now
Thanks a lot for sharing

It wasn’t unclear in the first instance, but you’re still going about this in the wrong way, use the parser to build the data you want into an array in vba and write it to the sheet.

Dumping it on the sheet then trying to clean it up is 10x harder

If you just want all the values, loop through all the dictionaries and collections in the parser JSON and write them directly to the sheet. This will remove all the keys for you

What are you trying to do with this data once you have it on a sheet in the format you want?

In the main code I have already the array before dumping it in the sheet and it is dumped to see and check the results

Please Login or Register  to view this content.

What I am seeking for is to get the keys as headers and their related values in columns .. so in row 1 something like that
Title - Awards - DVD - Ratings - and so on

and in the rows below something like that
Close-Up - 2 wins. - N/A - [{"Source":"Internet Movie Database","Value":"6.8/10"}] and so on
So the final result would be in flat database...

I have solved it partially .. Put a JSON string in A1 and try this code

Please Login or Register  to view this content.

Now how can I arrange the data according to sHeader=Array("Title","Awards","DVD".... and so on)

For the data provided

Please Login or Register  to view this content.

Thank you very much Jindon and Akuini
Both solutions are perfect and working like charm
Best and Kind Regards