 Hey guys, I got to get going, but I wanted to record this little intro for this video We're gonna be converting JSON files into CSV files CSV files or comma separated value files Pretty sure is what it stands for which is basically text files that are spreadsheets We're going to do that and we're gonna dump some data from a website in JSON format We're going to take the keys and make that the header for the CSV file And we're gonna look at using other characters for the CSV file delimiters. So I Hope you find this useful and if you have any Suggestions because there's multiple ways to do this. I'm sure I would love to hear them So let me know in the comments below. So let's jump into the tutorial. Okay, we're going to be looking at converting JSON output to CSE CSE CSV format comma separated values The notes for this are on pacepin at that link. There should be a link in the description of this video I'll bring you there. They know that control C there. Okay First we're going to need some JSON output to convert I've talked about in the past a website called random user dot me and how it can generate random user data for you Let's get 20 results and just put it to the screen. That's it. That's some JSON output It's a little messy. So let's go ahead and pipe that into jq dot and it will format it a little bit nicer for us Now there's multiple layers to this CSV, but we just want the name information. So what I'm going to do here is I'm going to say From the results We want the name If I type that right there we go. We have 20 names with titles. They have some there in other languages here There's options in the API to Filter out stuff like that, but we have the title which is mr. Mrs. Madame stuff like that first name and last name For a CSV file It's very easy to convert this to a CSV file where we have just the list of names and titles and stuff But we want the first line of the CSV file to be the titles the values So the keys the title the first name so we know what each column is so let's work on that first There may be a way to do this all in one command. We're going to do it in two commands so we're going to Type that into jq. We're going to get the names and then what we want are just the keys But doing this causes a problem. First of all, we got multiples. We just want the first one for this song essay zero Here you'll notice that it actually put them out of order because by default it's going to do them alphabetically So although we have all three of our keys there. They're out of order. So what we want to do is say unsorted So keys unsorted and again all this is in the notes and the link that I provided We do that and now we have title first name last name. Great. It's still in an array. We don't want that what we want to do is Flatten it Which actually didn't do anything actually sorry flattens for the next the next command what we want to do right now is we're Going to pipe that into at CSV which will convert that to a CSV But we have a lot of it's giving us those backslashes for the quotation marks we can avoid that For a lot of instances just by going to JQ and adding the dash R command and now we just have our quotations things separated Let's go ahead and dump that into a file called names dot CSV Okay, we have our first line now. Let's change up our code a little bit What we're going to do is instead of just getting the first result We're going to say get all of our results so we're getting all 20 results and for this we want to So if we just do that, we'll get the names in JSON format I want to flatten this which gives it as an array and then I want to put that into at CSV and There we go So now I can take that And I can dump that so I'm going to do two greater than because we're going to append to our names that CSV and at this point I can say Libre office and give it that file name and it's going to open it up It's going to confirm that we're using commas for separation with quotation marks We're going to click okay, and here we have our spreadsheet with the first line is the keys for each column and then we have all our information That could be the end of the tutorial, but I'm going to teach you one more thing If we modify that last command, let's say we don't want to use the standard comma CSV C stands for comma comma separate values I believe is what stands for and What we're going to do here is instead of using the CSV command Which is very useful because it does do backslashes for quotation marks and other special characters So in certain situations this technique I'm about to show you may not work But in most cases for me it works I like using a pipe symbol because then if I'm going to Cut and grep and grep and cut through a file It's easier to not have to worry about quotation marks and commas that might be in the fields And most time when people are writing stuff out for forms and whatnot and spreadsheets the pipe symbol is very rarely used So here instead of saying the at CSV I'm going to say join it with a pipe and it does the same thing so you can go the same route That we went before but instead of using that CSV you can use the join command and use whatever character you want so again link in the description To this pay spin with all these notes. I ask that you check that out also visit films by chris.com That's chris of the cave. There's a link in the description, and I hope that you have a great day