JSON to CSV Tool
https://jsonformatter.org/json-to-csv
Transcript
Hey, Scott Austin here and in this video, what I'm going to show you how to do is how to bulk add meta fields to a collection. So for the store that I'm building right now, abstract rebellion to start off with, we've got over 500 collections. So I use Postman to bulk upload collections. Right? I'll show you what that looks like here.
I have another video on this process. So if you want to do bulk creation of collections, go check that one out. So here is the process I use to do that. I have a bulk create collections and this was a little bit different in that there's a collection tag that I'm adding to these, right? So this one has a rule.
So we go to the body. You can see that the rule for it is, you know, the product is tagged with a specific field. So what I did is I created a spreadsheet, you know, a CSV file with all 500 and some plus collections with all these fields that I needed. And let's actually show you what that looks like.
And that is this spreadsheet right here where I have the collection name, the collection tag for the rule, and then a collection description. So I did that and what I wanted to do at the same time was create a meta field for that collection also, right? I can do a collection name, I can do a collection description, and I want it to do Merrifield also in my investigation, and I might be wrong on this, but I found that you can't create a collection and add a Merrifield to it at the same time because there are different API calls, right?
So this API call that we're doing here is smart collections, JSON. And you know, I tried some stuff that I searched on the internet and I couldn't find it. So then what I found is and in the the postman documentation for Shopify, you'll see down here that's down here there's a create a new Merrifield elements. And this just shows you a generic metal field and we're doing some investigation.
What I found out is I can create collection. Merrifield So that's what I created up here, a collection meta fields creation element. And you can see there we're putting in the collection and then the Merrifield. So this is calling a different API right before we work on the smart collections
That is calling these smart collections JSON where the create collection meta fields is calling matter fields json. So because there are different nodes, this is my guess on the APIs, we can't combine the two. So when you want to create collections meta fields, well you have to know at that point is the collection ID, right?
So what I had to do, so it became a three step process. Step one I created all my collections. Step two, I exported all my collections that I just created so that I could get their collection ID And then step three, I created a spreadsheet for the creation, the meta fields, and then uploaded that and ran it. So I'm going to show you steps two and three here in more detail.
So step two is once you create all your collections. All right, so let me just show you why I'm doing this. So here is my store. We've got hundreds of collections here, 13 pages of them, and then we have these top level nodes. And what we are doing is this matter field that we're setting has one of these items, two core genre, subject color, artist or aspect ratio or could have more than one, right?
So if the midfield is set to the core, I want to show it. So I did some custom code here on this page that goes through all the collections and if the midfield value equals the core, then it shows it here, right? If the midfield value equals genre, then it shows it here. Same thing for subject and color and so forth.
So if we go into the yeah, here they are, here is let's go to a collection, just show you one of them. Right. So we'll go to Zen Art and you'll see our midfield value down here. I'm calling it tag and there's genre and there's all the other ones that we talked about. Art is color. The core subject aspect ratio.
So I wanted to set that for hundreds of of ones and I could have done it in bulk view, which you just saw a second ago here. Let's go back to our our collections to select all and then edit not edit. There's edit right there. All right. I could have done a bulk edit and one by one, but then I would have to remember which one of these values of the right one for this thing.
And that was hard for me to remember across 500 plus collections. And it would take a whole bunch of time to do right. So what I did is I did the export like we were just mentioning. So let me show you what that looks like. So here I have get collection meta fields. All right, so this is a get and you can see we're calling up collections and then the meta fields JSON.
And this one is just for a single element, right?
We could also do it in bulk if we wanted to. Where's the bulk?
Retrieve a list of all the there we go. We keep a list of all smart
collections.
This one here, Right. So this we choose a list of all smart collections. I set the limit to the max, which is 250, which means I had to run this three times to download all of my files or all my collections.
But if I run this,
you'll see it gives me this JSON file with all this information. Right? So here I can see the ID and I can also see the tag,
which is the net, which is the rule that creates that collection.
So here we have the rule being product tags that's on the product side down on the collection side of the product tag is equal to genres and right. And then down here we have this tag, Medfield. When it's on purpose. These are the same genre and genre, right? And that's how I can on the front end know that under genre, that's these elements here,
because my Medfield says genre, it's telling me that this collection is defined by the product tag genre
in in the rules.
So what I did is I downloaded this JSON file, so I copied that. Now I need a CSV to upload, right? So and what I figured out is I figured out that my C is V had to look a certain way. So I'm going to go in and show you my tests,
So I started, you know, playing with different CSS vs and seeing the information that I needed. Right? And this is the one where I could create Neta Fields off of this information. So I started figuring out how to do it. Now, one of the things that I found was hard to figure out for me right in the beginning was knowing what like type I wanted.
Let's just expand this. I knew my name spacing key, right? That was pretty easy to figure out, but I didn't know what the value is supposed to be because sometimes, you know, the midfield definition for this is single texturing, but multiple of them. So it's an array, it's a list of text strings. Right? And I couldn't figure out for the life of me because the documentation in Postman actually showed the type it called the type something a little bit different.
but I'm going to show you what I figured out anyways. Right. So the challenges I was having was figuring out what the type was going to be for the Merrifield. And I found in my documentation it used to be called string, I think.
And then after online store 2.0 or somewhere along there, they changed that to single line text field, but that wasn't working for me either. So what I did to figure out what do I name these things and what is the format of the value, right? So here you can see we've got an array with the square brackets, but they've got the back slashes before the quote marks.
And I forget what they call that. So what I did is I pulled off the get collection meta fields and I that's how I figured out how to do these things. So I just put in a random collection ID that I knew had a menifield to it,
and I said, I want the name spacing key of Jane. Come in.
Tag. And then I ran that. So if I just send this right now, it gives me back. And that's how I figured out, the type of this menifield that I'm using is this. And the way that I'm going to have to upload it into Shopify through Postman is this way, right? So that's how I figured those things out, because in looking in the API documentation, I couldn't find this level of detail and that's the format and structure of things.
So I just figure let's pull it out from the API and that'll tell us how we should input it into the API.
So I got from Postman the so we retrieved a list of smart collections,
so we got this big long list, right? And then I figured out we could also only ask for certain fields right here. So then I said, Well, let's just try ID and title. And then I also wanted this condition here, right? That's subject. That's what I want to be the Merrifield value. So I wanted to pull that down.
So I have the same row of my spreadsheet so I could use that information. So then I put in condition, right? Because that's what this is right here. So let's pull down the condition. And that didn't work for me. Well, it would help if I spelt it properly. So then I run that
and you can see it breaks there.
I've got to turn this on. So I turned on the just show me these certain fields and let's also set the limit to be 250
and now we run that and you see just gave me ID and title, it didn't give me condition. So then I turn that off and looked at it again and said, well, you know, I ID and title that's at this level.
Well, the same thing at that level is rules. So let's try to just pull down the rules. And that ended up working for me.
So now this is the ID, which is what I need to upload my Merrifield value. This condition will help me know what my Merrifield values are going to be that I'm going to upload in the total.
I just have to double check that things are looking the right way. Right? So then I've got this JSON file
I need it in CSP format. So I need these and rows. Right? What I really need is three columns of ID title and condition and then I can do some more processing from there. And I had a hard time finding how to convert that.
There's a bunch of online tools that supposedly convert JSON to CSB, but every time I did it, it gave me all this information in one long row, right? It's one on one cell, which went on forever, not in separate rows, but of all the tools I actually found one that worked. I tried like six of them, I think.
And I don't know why this one works and the other ones don't. But it does, right? So here I'm just going to paste in that JSON paste and now I'm going to do JSON to CSV. So this tool is JSON formatter dawg. JSON to CC, right? And now you can see boom, I've got rows in a series V which look beautiful for what I need.
So then I took that. Now I had to do this. Remember, I did three exports in to get over the 500 collections I have. This is 250 of them, but I did it three times and just put them together into one big file. Right. So then
open up Excel
paste that in.
So then what I did is I said, All right, I need to separate these out in two columns.
So then I did a text, two columns eliminated by comma,
and that got me
this.
So now I got three columns and I got too much data in here. But then I said, Well, I can do this one and I can do another text to column. And for this one I'm going to have the eliminator be the pipe right there, run that, and now I've got this.
Remember, I just need to subject the genres. And then I ran the text two columns one more time, and on this one here, instead of the pipe, I made it the colon
and then I could delete this
and let these
and this gave me enough information. So now I and I don't know why Excel does this all the time.
It drives me nuts. But this I just want to see the number because sometimes you say this is a C is V, it actually saves it like this format. And then when you try to put it in a postman or through the API for postman, it didn't work, right? It said invalid ID because that's not the ID. So what I had to do here was under my format, I had to make it a number to add to this before I saved the file because it might seem in that other format.
Yeah. And then that and I saved this and it would work. So in the end what I came up with is a spreadsheet, a CSV file that looks like this.
So let's expand each of these. So the namespace and tag and the type, those were the same across all of the collections.
And then we had the collection. I'd let's see if that's actually saved as a number. It was before. Yeah it is. Okay. The collection name I actually don't use in the uploading. Like I said, it's just there for me to check and balance. And then I created this format, right? So I just did a little parsing. Remember, I actually show you what I did because what I had from the JSON to see as V is it said this, right?
So then what I did is I said, All right, then we're going to make this the title case of that right. So proper.
And then what I did is I put in a square bracket
backslash quote.
And then I put another backslash quote
square bracket
and that is to mimic these here. Right. And then what I did is I did another can cat
and said I want this and this one commas between them comma and then this and bracket boom. Right. And then I took that which is right now it's an equation. Right. And I copied that and then I pasted special would just text.
So now that is no longer an equation and that is the texturing. That's how I got this texturing here, right? Because in that other spreadsheet from the JSON to see us v, I had this value here, so I had to go through a couple of steps in Excel to get that value to the format that I needed to upload it.
But then with these, right, so I got namespace key type collection, ID and value. And if we look at our postman
on our
Create collection meta fields,
we have namespace key value and type right and collection ID up here. Now. I had some problems with this also in that because I work in Shopify and Liquid so often I'm very used to putting spaces here right?
And I forget which one it's on. But I had spaces like this when I was typing stuff out and it wasn't working right. And then I realized that, you know, there's no spaces here, right? So why don't I remove the spaces from here and see what happens? And that was my problem right?
It didn't like the spaces after the curly brackets.
So getting rid of the spaces and I could run it in and it would run. And then the way we run this. So this one here, this is for bulk upload. Now to run it, we have to open up runner down here
and we want to drag over the create collection meadow Fields into into runner runners right here, drag it over, it drags over the whole abstract or billion collection.
So I've got to turn off the ones I don't want to do like that.
I've got to select my file first. And then the first time I ran this, I didn't do any delay and like every other one of them didn't work.
And it's probably because, you know, there's API limits on Shopify, it's like to a second or something like that. So then, you know, it ran like half of my my meta fields were there, half my collections had the menifield and then the other half did not. So then what I did instead is I moved my delay up to 1000 milliseconds.
So one a second. Right. And so it'll take 6 minutes to run this. And then I ran it and it ran totally fine. And I'm not going to do that right now, but you can see each one running and it being working okay and all that good stuff.
So that in the end, when I was done trying to get to here, go to my collections list and select all and none of these had matter fields when I started.
And then after I ran it, I've got the midfield preselected in the columns here already. You can see they've got the menifield values in there. So it ran. It worked. It was it was wonderful. It's super happy. So I was able to, you know, bulk create over 500 collections and then add meta fields, over 500 collections using this Postman API process, which, you know, that would have taken me days to do manually and I was able to do it in a few hours, including the learning curve that it took me to do this.
So now what I want to do is just go back here and on this create collections. Merrifield I want to show you this and a little bit of detail so that you can, if you want to do this, this is what you want to copy in in Postman Right? So you can see here, if we go to the params, there are no parameters, right.
For the body. It's got these values here so you can pause the video, type those out on under yours. If you want to do that. And then for test we're doing a201 status so you can see what that looks like there in settings. There was no settings that I changed I think, and then you can see up here that this was the, the URL query for, for the API.
So if you've got this no params the body set up this way and then the test set up this way, it should work for you or at least give you a good enough starting point where you can get it to work for you because your configuration may be a little bit different on things like you know, your namespace in that kind of stuff.
All right. So if that helps you get started in using Postman to bulk create metal fields for collections. Thanks for watching.