Importing and Exporting JSON in Powershell

good morning everyone this is Mike and welcome back to the channel in today's video we're going to be talking about importing and exporting objects specifically Json objects um in and out of powers shell so let me uh switch over to my lab environment here and we'll uh have a look all right so so when working with objects and Powershell you may have a need to to import them in or export them out there's a lot of different formats you can use uh there's CSV there's XML there's Json uh if you're doing the exporting side you could just do HTML so there's a lot of different options available and what we're going to be focusing on today is specifically the Json so let's say I have an object so let's maybe say we'll use a service um so maybe we'll have a look at the bit service and let's say I want to take this object and I want to export it out um in Json format so if I first of all look at what commandlets are available to me so if I kind of use the get help commandlet and we look at things that uh that have Json and their name uh we could see that there are two different commandlets to pick from so there is convert from Json and convert to Json so unlike other things like if I maybe look at the help on let's say CSV I you'll notice that there's the convert from and the convert to but there's also an import and an export right so CSV has kind of like four different command LS you can play around with uh Json just has two okay now with the convert from and the convert to accomplish is they modify the object that's in powershell's Pipeline and basically convert it to like Json formatted text and then leave it in powershell's pipeline okay so you would then have to take that text and write it out to a file if you wanted to accomplish let's say the full export so again I'll show you how to do that in a minute now the other thing I want to pay attention to is the object that you're working with right so a lot of people when they're using Powershell you know they'll run a command lit they'll look at the output that's produced on the screen and again think that that's you know everything that's there and remember that when you when you're running a command lit if that command lit produces output it's normally in the form of AET object right and that net object is going to have properties and methods on it uh properties meaning facts about the object methods being actions you can take against the object so you could see those things in action um if you take the output of something and pipe it over to a command lit called get member um and that will allow me to see you know everything that is uh that is being produced okay so so you know by running or piping the output to get member uh we could see all of the proper properties that are on the object uh the methods that are there uh in Powershell there's a couple extra properties added on these are known as Alias properties um and the type of object that was being produced okay and this is not some value made up in Powershell that's literally referencing AET object so if I took this uh this this this name here and and put it into Google you would get you know the the the net page for that particular object showing up in the Google search results so when you want to export you kind of have to think about like what is it that you actually want to export okay so so do I want the object produced by running this and do I want all the properties that go with it okay maybe you do maybe you don't um so usually when you're doing nextport you don't want everything and if you kind of leave Powershell to its devices it will give you everything okay so if I take the result of this and send it over to uh convert uh convert to Json um you know like we could see uh you know that's what we're getting right it it did everything um and that's not probably what what I would be after um if you you uh you know if you if you think about it right so maybe I want to be a bit specific about it right so maybe I want let's say out of here maybe I want the name property maybe I want the status property maybe I want the start type property okay now the Import and Export and the convert from and the convert to commandlet they're not intelligent enough to say hey uh maybe you want some of the properties but not all of them so what you have to do is you have to prepare the data first in Powershell before you then import or export it if you will okay in this case exporting right so I need to get rid of those properties before I do my uh ultimately my export right so now there's since we're going to be writing this out to a file there's literally one way to do it right um so some people would argue there's two and those people would be wrong so if you just want the data to show up on the screen uh there are there is two ways to do it right so the one that you see popular out on you know blogs tutorials and other YouTube videos is to send the result over to like I know like format table um and then I could say the properties that I want let's see those are what I'm going for and right so we have three properties right and take that a step further and then do the convert to Json and you're like hey now we're rocking and it's uh it's kind of like a hot mess right so so format table actually any command lit that that has format in it uh They Don't Really mod ify the object in the pipeline okay cuz remember when we first started here you know what object were we working with well let's go back to that get member right and this was the object we were working with so again it was a service controller right so when you uh do an X do do a format command what format does is it takes the object that was originally there in the pipeline and it substitutes it with a formatting object which has a whole bunch of properties it it has its own properties on it that look that are made to look normal when you see it on the screen the problem though is when you then want to take that screen output if you will and then do something else with it you get like a disaster right CU it's not working with the original object it's working with the formatting things so instead what you want to do is you want instead of using format table uh you use the command lit select object and what select object does is it's a lot even though the screen output looks the same as format table if you pipe the output of it over to get member you'll notice that it did touch the object in the pipeline like it didn't change it right so if I take that same um that same get member after format table you know and I kick it out here uh you'll notice like you know what you have in the pipeline is like a whole bunch of formatting objects and the original right so it's all format group data format end data whatever and and the original service controller is not in here okay so that's why again format table is not really helpful for this so if you just want to do screen output the format commandlets could be very helpful but they're not for this because we don't want screen output we want to write data out to a file okay so so with select object where was that so with select object it keeps the original object that was in the pipeline right that was being produced from over here so you have your service controller but notice it stripped off all the properties and the methods that we didn't explicitly tell it we wanted to keep so that's basically what select object does it's kind of like a decider you you you tell it what properties to keep and it gets rid of the rest uh so it's kind of like a white list if you will and all of the methods that were on the service controller are gone as well except for a couple generic ones that are there for like every net object so you're not kind of getting rid of these so so it's select object you pick the properties you want to keep and away we go so now I could take the result of this right this uh this select object here let me uh make my Powershell console a little wider here um so let me take uh the result of Select object and pipe that now to convert to Json and notice what we get right so so we have properly formatted Json data with the three properties that we wanted okay so so that again a little cleaner to kind of like read here so you have kind of like the opening of the Json um each line represents a property okay so you've got whatever the the name of the property is over here and then you've got the values over here right and then you've got the closing of the Json right so it's it's kind of easier to read in a in a way than XML is um so that's again one of the benefits of Json uh for simple structure it could be easier to read than XML would be right and also if any of you play in Azure a lot um all of the object you create an Azure are all described in Json data as well right so if you uh if you use Azure you probably have interacted with Json at some point right um all right so now we want this out to a file right so so the text here it is Json formatted text but it's not out to a file yet so convert all the convert to command let's kind of do is they convert it to text but leave it in powershell's pipeline which if you don't do anything with it it just gets you know written out to the screen here so what I need is I need a command lit that'll take text string if you will string objects and write them out to a file so that command lit is uh the command lit out file right um so we're going to use that and we're going to give it um a file path and again let me make my uh my screen a little wider here so I could fit this all in one line apologize uh so file path let's do um C colon demo and maybe we'll call this I know test.js n right just just not wide enough there all right so let's go ahead and do that and if I look at my directory which is uh currently set to the C demo folder on my computer there's my test JS all right so we got that going so now if I go and uh look at that file so maybe we'll pop this open with notepad here notepad.exe test.js uh we can see the loveliness of the file all right so hey we've got Json data now here's the here's the problem all right um here is the problem and one of the reasons why I don't like working with Json in Powershell okay so Json is great for other things but exporting Powershell objects to Json is not necessarily the best there's kind of better ways to kind of work with this stuff uh CLI XML is probably my favorite uh but if you look at what the Json looked like right and you look at what the original query was you know that was being run okay notice that status and start type kind of took a they got changed right so notice status is stopped but in the Json data it's status of one and start type is manual and start type is now three right so something's kind of like going on there and in order to kind of see what's going on one of the things you have to recognize within Powershell is kind of you're working with net objects and properties that may look like they're text or string may not be okay so like if I take the result of let's say you know get service and pipe it over to get member again okay notice that uh status the status property is not like it's not like string or or or integer or whatever it's this service controller status object and the start type's kind of the same way right it's like its own type of net object right so in Powershell when you look at these objects they numerate into certain values that that are in that are in text so it's easier for us to look at but in fact the actual net object stores the data in like raw integer form right so like with status you know whether it's running or stopped um so basically with that a stopped is one and a running would be four you know when you're looking at statuses right so that's why that one is there right CU that's the uh the raw value uh for that particular property on that net object so when you're converting to Json it just keeps the raw value right it doesn't keep the enumeration uh that that doet kind of puts on that type of data right so so you're seeing you know like one and four or start types would be like one two or three you know three is like a manual two is like an auto right so you lose kind of some of the the the the Nuance of the objects when you kind of export them in this way right now there may not be a big deal but if you're going to import this data back into Powershell later on that could be a big deal right so again just kind of understand the nature you know of of what's Happening Here okay okay so now I just exported one object again you could export multiple maybe I had a bunch of services right all the services to start with the letter B um and again let's say I want to convert that to Json and Export it out so we could do convert to Json um and then we'll do our out file um and the file path parameter C colon demo and maybe this will be Test 2 do ad Json right so we'll go ahead and run that all right so that again if I uh uh let's see so that was test two so where's my notepad line so let's do test two and here is our Json data all right all right so notice each object kind of gets its own section right contained within a script block uh then you got a comma onto the next object comma onto the next object and so on right and again notice the name kind of came over as is because that's a string uh but notice status uh and start type both got changed over okay so notice like you know four and two over here one and three one and three four and two right so um so that's again oh here's a four and three different combination right so the raw data has been what is uh kind of preserved here on the Json data all right so now uh so that gets the Json exported out now the the opposite of exporting is let's say I want to import this data into Powershell right um so so you first of all uh need to read the text in the file into Powershell right cuz let's say we're working with this file and I want to import it back into Powershell and turn it back into like a net object or or a Powershell pipeline object whatever whatever way you want to consider it you know what could we potentially do here so when looking at this I first of all need to read the text file into Powershell right so I could do that by using a command lck called get content uh give it a is it path or file path path uh so we'll give it a path and we'll bring how about we'll bring the the first one in okay so we load that in and that does not kind of render it as Json at this point right so all that did is it just literally read the text uh of that fun file into Powershell and each line of text becomes a string object so in fact if you pipe this over to uh get member uh you could see that it did in fact bring this data in as string right so so we're working with string objects and um looking at it here if I pipe the results over to a measure object okay I could see five because the 's five lines of text right so it brought this data in as five string objects which is definitely not what I want you know I want to turn it back into whatever it it originally was right because remember we originally started with uh with let's say uh this right so we had um objects with uh three properties on it name status and start t right so so that's kind of where I want to get to so in order to do that uh again we first start by bringing the text Data into Powershell by using get content and then we pipe it over to convert from Json and if we run that um it looks like uh We've achieved the goal here right so there's you know the the file that had a single object in it and if maybe we do the file that had multiple in it uh there we go right so so again it did bring the data in kind of in that raw form right so notice status and start type are kind of broken right here was what the original data looked like right and that's definitely not the same as that right so we'll we'll get to that in a minute here uh because the first thing we need to do is we need to fix what was imported in okay and the reason why I say fix it uh when you run I don't know if it's like whoever kind of created this command lit if if whatever they did you know was was different or special or whatever but when you look at the data uh specifically if I take the result of this and I bring it over to measure object I should see a a number a count of 1 2 3 4 5 6 7 8 9 10 11 right because there's 11 objects here but if I do measure object right it's just seeing one okay um so what kind of happens with convert from Json is it brings the data in as like uh like a single Json object uh think it is almost like an outer shell right and you want to get to like you know the the the inside of that shell which is where your 11 objects are right so just convert from Json doesn't quite do that um another tip to kind of know that that this is happening again remember the data inside the Json file it has properties of name status and start type right so if I take convert from Json and then pipe that over to uh get member you know what object is being produced well notied it's just some generic object and notice the properties on it are not the properties of the data that's inside this Json file like they properties for Json itself right and that's certainly not what we want okay so what we need to do is we need to crack that object open we need to take the Json object that's produced by doing convert from Json and we need to crack it open like you're cracking an egg right to get the the data inside of it into powershell's pipeline right so there's two ways to do that um so both kind of use a method that's called enumeration okay so enumeration um so enumeration is basically a way that you can grab the the objects that are in the property of the object in the pipeline um or you can use a numeration to call methods as well right so when you want to uh grab the data that's in a property of another object that's already in the pipeline or if you want to call a me both ways that that we use a numeration now in this approach uh we can use a numeration uh by running the for each object command lit right so I could do for each object um give it a process parameter uh which uses a script block and then you call the variable that represents the object in the pipeline so dollar underscore or dollar PS item okay even either one will work um I normally use dollar uncore and that will enumerate each uh each piece of data that's inside the Json object so the output looks kind of the same right so you might be thinking well you know Mike that that that doesn't look right you know when I just did the original Json kind of looks exactly the same but here's how you can kind of tell that it enumerated it pipe it over to measure object and notice there's 11 things in the pipeline now okay so this took grabbed all 11 of them and put them into the pipeline right again the other way that you can kind of view it is to bring it over to get member um and you can see again now we have a PS custom object and we have the three property that are on the data that was in the Json file okay so name status and start type right because that's what we were originally uh starting with here right so pulling the text file in that's what we originally started with name status and start type right so so the data on the object in the pipeline the properties on the object in the pipeline is now matching uh what we uh what was in the original import file okay so that's all nice okay so that was one technique uh that we could use to enumerate right so uh we do for each object uh process uh open curly bracket dollar uncore which again is a special Powershell variable that represents the objects in the pipeline put there by the previous command so and dollar underscore only works when you're in inside of a parameter that accepts a script block uh for its data type right so notice if I you know bring up powershell's help system um for the for each object command lit I notice in the syntax here if we go find process the process parameter which is what I was using right so process notice what type of data does it take it takes a script block all so that is again a type of net object it's basically a bunch of code inside of curly brackets so the dollar underscore or the dollar PS item either way uh represent the objects in the pipeline put there by the previous command L right um so that's what we're we're calling there so that was one way that we could do a numeration now what would be the other way so the other way is to utilize your own variables so what you do is you take the result of the get content and the convert from Json and put it in a variable so maybe I'll just call it dollar Json I mean call it whatever you want dollar peanut butter dollar elephant dollar crocodile whatever um so dollar Json so take the result put it in this variable and then all you have to do is literally just call the variable okay and that will get you the 11 objects that were in there right because if I pipe this over to to measure object um you could see that there's 11 in fact because it's a variable you all variables have a count property on them you could see that there's 11 objects in there right so stuff it in a variable and then call the variable and that would be the other way to get it done okay um so that would be the two approaches that you could use um to basically get the the the data from inside the Json object all right now the second thing we need to do is we should probably fix these properties so they look correct all right again if you're going down the rabbit hole you might as well go down a little further right so originally uh when we looked at the services let me bring this up here uh so we looked at the services if we piped that over to get member uh the original data was a service controller um not that I really care about that okay but notice the properties right so the name property is not really there it's really an alias for service name so service name service name is a string so that's fine that comes over good uh status though is not good right so it was originally a service controller status object and if I go find my get member output from somewhere up here um notice after you know you do enumeration notice status was not a service controller status object anymore it's now an integer right and start type again was also an integer okay so that's what they import as if you have you know a number you know when you import it into Powershell it's probably going to make it an integer uh if it has no decimal places if it has decimal places it'll come in as a double object okay but so notice originally start type was this type of object and status was this type of object right so that's a problem so we want to fix that so when I when I call my variable that has a Json data in it you know they're coming in as integers right so take that data pipe it to get member and integers all around right for status and start type so what I need to do is I need to take these properties and I need to convert them from an integer object to whatever this you know these types of objects are right so I need to do uh so I need to make that fix right so how do we go about doing that well uh when you're working with Powershell if you have objects in Powers shells Pipeline and you want to change them from something some from one type of object to another type of object you can use something in Powershell that's called a calculated property so what I do is I uh use select object and uh we specify the properties we want to keep so now I I'm fine with name I want to keep that and then status and start type I I can't I can't just keep them I need to make a calculated property out of them so I'm going to kind of convert what's there into something else so let's start with status first because that's kind of the shorter one right um so so what we're going to do is we're going to make a calculated property now to do this you create a construct and power shell that's called a hash table so a hash table starts with an at sign and an opening and a closing curly bracket and then inside the curly bracket you have to Define two different fields uh you need to define a label field which is what your property that you're making is called and then an expression field which says what the data is for your property so let's first start with label um equals and then what do I want to call the property I'm making well I don't want to make a new one right I want to call the new call the call the stuff I'm making status right so I'm making a new version of the status property so that's the name of it and then you're going to do a semicolon and then you're going to do expression equals and then that's going to take a script block so inside the script block I'm going to reference the object in the pipeline by Dollar underscore and then I use dot as a separator um and then I want to say I want the status property and I want to uh do something know as casting casting right so casting is basically where you're going to say what type of object you want this to be so this line right here is calling the data that's in the existing status property and I'm going to recast it by using the as parameter into a different type of object right so what type of object well I'm doing status here right so the original status was this type of object so I'm going to grab that I don't want to type all of that and I'm going to put that in um in a bracket not a curly bracket but a straight bracket so I open a straight bracket paste that value in close that bracket so I'm recasting it as this type of object and if I hit enter uh so notice what have I done so far with select object I've defined I want to keep the name property as is and I want to make a new property called status and this is the junk that's going to go in it okay so I'm just going to hit enter to see if that works right so cool so we got name and Status so that's rocking and rolling there all right now with your calculated property that we just made um there is a bit of abbreviation you could do to it um expression doesn't have to be the full word expression uh can literally just be E equals and then label uh can just be L equals right so that's um so that at least saves a little bit of typing um and again notice that'll work fine okay now uh the label part believe it or not can actually be also I'm not going to do it here but it could also be n equals or name equals uh either of those would also work as well okay but I like using label so that's kind of what I'm going to go with so we'll do L equals all right so we've now fixed the status and we need to mess with the start type now right so so we still need to fix start type so it's an integer but the original start type uh was this type of object right here okay so I'm just going to copy that so I don't have to type you know this out again copy that and let's go make let's bring up the command that worked before um and let's go make another um calculated property right so let's do a comma and then we'll do another calculated property I apologize it's going to carriage return because of the width of my screen uh but we'll do in this case we'll do L equals um and this time we'll call it uh start type which is again what it originally was uh so that's start type then semicolon and then my expression um inside that script block for the expression we'll grab the object in the pipeline we'll grab the data out of the start type property and we'll recast that data as this type of object and there we go so there's the um so there's the two properties now fixed Okay so we've got the name which was again as a string and we fixed status and start type so they are now correct right and again the the whole you know this is again the second fix we had to do uh because remember bringing the data in uh we loaded that ultimately in the variable to start with um and then enumerated the variable okay so again remember that when you do convert from Json the data comes in as one Json object and you need to break it apart to get the orig you know the data that's inside of it so I got to you know load that in a variable call the variable that's the first fix you got to do and then the second fix was fixing the properties uh to what their actual values were because when we exported the data uh into Json to start with right it it kind of lost uh its uh it lost its its stuff right so that's the second fix all right so if you guys have never used calculated properties before it's a good thing to know about right because it allows you to potentially change whatever properties are on the objects and Powers shells pipeline so it's good for this but I use calculated properties all over in other areas as well well so they definitely do come in handy all right so that is the uh that is the importing side uh when we're working with Json data so that's uh going to do it for this video I thank you for watching and hopefully we'll catch you in the next one so have a good one everyone

Channel: Chasse TAC

Views: 71

Keywords:

Id: 1guo4UKI0U0

Length: 39min 50sec (2390 seconds)

Published: Fri Jul 05 2024