Hello,
I have a .csv with nested JSON rows that I would like to break into columns. I have been following steps from a similar posting from earlier this year, which have me close to my goal.
An example input row looks like the following (formatted here for legibility):
[{ "key":"Pattern", "value":["Solid"] }, { "key":"Worldstock Country", "value":["Peru"] }, { "key":"Neck Style", "value":["Scoop Neck"] }
... and it continues
My workflow gets me to this point so far:
RecordID | Key | Value |
1 | Pattern,Worldstock Country,Neck Style,Sleeve-length,Front,Care Instruction,Closure,Dress Length,Size,Dimensions,Model Number,material | Solid,Peru,Scoop Neck,Long Sleeve,Flat Front,Hand Wash,Pull Over,Short,L, M, S, XL,See Details,231516,Acrylic, Alpaca Wool |
2 | Recommended Use,Primary Color,Battery Type,Heel Height | NOT STATED,Multi-Color,Does Not Contain a Battery,Flat/Low Heel (Under 1\ |
3 | Heel Height,Material,Gender,Shoe Size,Size,Color,Model,Casual & Dress Shoe Style,Manufacturer Part Number,Brand,Age Group,Shoe Width | Low (3/4 in. to 1 1/2 in.),Leather,Women,7,7,Black,FAWNA-BLKMUL,Oxfords,FAWNA-BLKMUL,Marc Fisher,Adult,B(M) |
This is close to what I want to achieve - something like this:
(important that the recordID be preserved)
RecordID | Pattern | Worldstock Country | Neck Style | Sleeve-length | Front | Care Instruction |
1 | Solid | Peru | Scoop Neck | Long Sleeve | Flat Front | Hand Wash |
What is an efficient way to achieve this final outcome? Workflow attached.
Any and all help/suggestions greatly appreciated.
Solved! Go to Solution.
Hello Alex,
The workflow you sent didn't include the text file you were working from, so I have built the solution from where you got to...
RecordID | Key | Value |
1 | Pattern,Worldstock Country,Neck Style,Sleeve-length,Front,Care Instruction,Closure,Dress Length,Size,Dimensions,Model Number,material | Solid,Peru,Scoop Neck,Long Sleeve,Flat Front,Hand Wash,Pull Over,Short,L, M, S, XL,See Details,231516,Acrylic, Alpaca Wool |
2 | Recommended Use,Primary Color,Battery Type,Heel Height | NOT STATED,Multi-Color,Does Not Contain a Battery,Flat/Low Heel (Under 1\ |
3 | Heel Height,Material,Gender,Shoe Size,Size,Color,Model,Casual & Dress Shoe Style,Manufacturer Part Number,Brand,Age Group,Shoe Width | Low (3/4 in. to 1 1/2 in.),Leather,Women,7,7,Black,FAWNA-BLKMUL,Oxfords,FAWNA-BLKMUL,Marc Fisher,Adult,B(M)
|
The format you want the data in doesn't work particularly well because as you can see the 'key' changes for each record ID. I have got it in the format you want but it still shows a lot of blanks.
Hope this helps.
Thanks,
Nick
Hi Nick,
Thank you for the response and solution. I recently came to very similar conclusion (admittedly with a few extra steps).
There are quite a few empty spaces with the format that the data is in; for the time, that will be a separate issue -- happy now to have it in a more machine-readable format.
I have posted my workflow below as a reference for others if they want a similar approach.
Many thanks (text file to be included next time),
Alex