Research Triangle PowerShell Users Group Extras, 24 Nov 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: PowerShell Lightning Talk: Manipulating data using JSON and PowerShell with Terrance Copling

Description

Terrance Copling shares how he manipulates string data by converting to a JSON and assigning data types. He shares some neat tricks he discovered that allows him to create advanced formatting in his JSON object. He also deletes unneeded data from his source file on the fly so it is not included in the JSON output.

The Research Triangle PowerShell Users Group (RTPSUG) is the largest PowerShell group in the world! We meet twice a month to discuss all topics related to PowerShell and automation. Our topics cover beginner to advanced concepts and offer people a chance to learn new skills and ask questions. Our mission is to create a space for all attendees to feel welcome, have a chance to ask questions and be part of a community.

For more information on our group, please visit any of following links:

WEB: https://rtpsug.com
MEETUP: https://www.meetup.com/Research-Triangle-PowerShell-Users-Group
LINKEDIN: https://www.linkedin.com/company/rtpsug/
TWITTER: https://twitter.com/rtpsug
GITHUB: https://github.com/rtpsug

A

My name is Terrence Copeland I'm, a developer at Elon University. My little problem that I had here was um basically I had to submit information to a vendor using JSON format and, of course, I had to get data out. So what I'm doing is I'm going to show you how you could take how you can go through a PS object, for example, and change, values or even drop values that you don't need, as well as my biggest issue, was actually having a value, a single value and putting it in an array.

A

Alright, just a single value. Usually, will you have an array with JSON and it's just one value Jason doesn't want to put it in alright, but I'm going to show you how I got around that particular problem. Okay, so this grip is right. Here is just a small of what I made here is just a small script. So what you can see is that what I'm doing is I'm reading a CSV file. So here we have my CSV file.

A

You know, as you can see here, all the columns in regards to that CSV file in regards to whatever whoever the student is and all their data. Okay, so we got some everything basically in here is a string. So what we're going to do is I'm going to show you how I went through and just parsed it right, quick within the PS object and converted over to its proper value and then also removed whatever it feels I, don't need it or add it. What fields I didn't need.

A

So the first thing we're going to do is you can see here it imports the CSV and it pipes it to a for each so it as a pipes, a tool for each. You can see here, there's another for each which cycles through each data. Member of that PS object, and it looks at that P asaji and says: is this considered as a date? Does it it uses reg X and says hey? Does this have a date at the beginning, or does it have date at the end?

A

This is one of those tricks I learned at the meeting, so basically I'm using reg, X and um here I go again I'm in the regex I'm saying okay going up. If it passes the blank value that isn't um blank, then I'm gonna sign on no value to it. Otherwise, what I'm gonna do is I'm gonna come down here within that PS object. I'm gonna also assign it as a date time and I'm gonna put a universal time to it.

A

Okay and we'll see what that comes out to be again down here: I'm converting the GPA to a double so I'm, taking that string and converting it to a double and another for each look. That I did was here: I'm checking for boolean values, true or false values, if it's true or it matches that property matches to be true or false, the value of that property then I'm actually coming down here and converting it, because I know it's a boolean to the actual bull-type, okay and down here.

A

What we're doing here is I'm going through the rest, going through the properties again and then I'm, saying: okay, if the property name matches any of these names within my reg exes I'm gonna go ahead and create a hash object for it. Okay, we're just gonna, which is going to include a name property and an ID property all right and then also I got some other things here that it does as well which I which it does to pretty much the same okay.

A

So, as you can see here, I'm going through a loop for for this PS object, all these PS objects to come in and I'm going through cycling. Converting my data over to the proper type all right now happens is when I run the script. What we're going to see is.

A

Okay, so it gets converted over to a JSON format. So if we come up here now and we look at it, we can see that those values now are coming in and you can see here, I have any boolean value is now, as the true is not it's not a string, it's actual boolean noun because there's no quotes around and so forth. My date values you can see here, which was converted over to a universal time date in time like I wanted it.

A

It was just if you come back over here to a date value in my CSV file, and you can see that any dates that we have in here. It was just a regular date, so now I've converted those days over to an actual universal time and date. Okay and then again, as you can see here, here's where I created my hash objects, but each assigned property within the CSV that I wanted to be that contain a name and the ID. Now the initial script has a little bit more to it.

A

This is just a kind of like script that I've kind of written to go along and test data and so forth to get things going now when I was saying earlier, my hardest part was doing this. You see how in student groups, you have it in an array.

A

Well, when you do JSON and you pass one object in it with whether it's got the name and the ID, it treats it as the others, and it just creates a single array without a single array, a single object without the actual array parameter, while the actual red brackets I looked online to see how I could get around this and I, and one of the suggestions was to use a here stream, but when I used here, strings I always got like the characters on the back.

A

The carriage return characters and the live feed characters showing up so I couldn't get those two to come out of my JSON object. So what did I do? Was Anita I thought it was a neat little trick. Was you come in here? What it does is I creating the array, here's my object, I just plugged in some default values, I, creating the array assign that array to my custom, sign that custom object to my array and then I actually assigned that array.

A

I mean that array to my actual list and what that did was create a depth which, in turn, created an array of this particular object for that those properties. Does that make sense to everybody? Yeah, it's cool, okay, okay, so yeah. That was the hardest thing and, like I said, I've seen a lot of things online, saying hey!

A

This is how you do it and a lot of it referred to using a hair string, but I got a weight got around that without using a hair screen by using actual this, this type of code right here by taking them away and putting it within a list and then the another thing that I took advantage of was I know we don't see this a lot. I actually used the class in regards to addressing um so a lot of us use like PS objects or custom objects.

A

I've actually I decided to use a class I thought this was a good use case for a class, so I actually went in and actually use the class and, as you can see here, I created another list which interns I can now just initiate my class and add whatever values that I want and then once I add my values. I can actually get a read of those actual fields so now I'm also going through my PS object and I'm deleting fields that I don't need.

A

So here you have a little bit of adding fields going through changing data types within the field and then actually removing fields that are no longer needed. So now, I got my soul. Address fields that you see here, I don't need them in my JSON object. So what I do is since I'm pulling in since I'm pulling in this JSON object, I'm, removing all the fields that I don't need, and this is a little trick for anybody that says: hey I need to remove these fields from this PS customer I.

A

Really, don't need them, you know, but you now. You know that you can go through properties within a PS custom Asya and remove them or add objects that you want. Okay, cool thanks, Terrence.