seperate json file into different columns by key

**excelmymind** · 10-25-2017, 03:19 PM

I have a series of gene id json files setup inside of a single excel cell.

Each cell is setup with 5 key:value pairs
Lets say they are Dog, Cow, Whale, Cat and Snake

Within one cell [{"Name":"Canus Bitus","Leg Number":"4","Lung Number":"1","LengthinFeet":"2.5","Poisonious":"no"},{"Name":"Moo Milker","Leg Number":"4", "Lung number":"4",etc...},{whale},{cat},{snake}]

How can I take that string and turn each key:value into a column? {dog}col1 {cow}col2 {whale}col3 {cat}col4 {snake}col5

Yes... this should be done in python but nothing is documented so I am creating the process 'as is' in excel, then upgrading to a tool designed for this century.

**kev_** · 10-26-2017, 03:38 AM

This works with the limited data you supplied
- with data is in column A
- results in B:F
- test in attached workbook with {CTRL}K

Please Login or Register  to view this content.

**buran** · 10-26-2017, 05:06 AM

check https://github.com/VBA-tools/VBA-JSON
also, not sure, given that you are python user, why not use python and just output the result in excel (if you must). Probably you have seen the link to python forum in your other thread

**excelmymind** · 10-26-2017, 05:38 PM

Because I am relatively new to python and sewing together modules of package code is fun, but its a different process and mindset than data munging.

Which is code for 'I am polished but still a newbie'

@kev_ is that python or c, or vba?

**kev_** · 10-26-2017, 10:56 PM

Hi excelmymind
It is VBA

**buran** · 10-27-2017, 02:35 AM

Originally Posted by excelmymind

Because I am relatively new to python and sewing together modules of package code is fun, but its a different process and mindset than data munging.

Which is code for 'I am polished but still a newbie'

It's definitely your decision, but for me it sounds like "double work", which is polite way to say "wasting your time unnecessary to do the same thing twice" :-). More over python is more versatile and suitable for this kind of work. Even if you spend the same time, it will be well worth it as you will learn/polish new skills and knowledge.

**AliGW** · 10-27-2017, 02:37 AM

Thread moved to VBA section.

seperate json file into different columns by key

LinkBack

Thread Tools

Rate This Thread

Display

seperate json file into different columns by key

Re: seperate json file into different columns by key

Re: seperate json file into different columns by key

Re: seperate json file into different columns by key

Re: seperate json file into different columns by key

Re: seperate json file into different columns by key

Re: seperate json file into different columns by key

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Seperate First, Middle and Last name to seperate columns

Json file from URL to Excel

Json file from URL to Excel

[SOLVED] Seperate inches into 2 seperate columns

JSON URL to xls in rows and columns

[SOLVED] Macro to seperate worksheets to seperate workbooks fired from external excel file

How can i Sort a json file data to a excelSheet in proper way.

Bookmarks

Bookmarks

Posting Permissions