- Hello. Welcome back to another video. In this video. I'm going
to show you how to convert a CSV into JSON with Power Automate. And this is in response to a question that was on the Power
Automate Community Forums. I thought I'd do a quick video for it. So, let's take a quick look at the CSV. Here's the CSV data. Here's the header row. Here is just another line
that was in this CSV, that the person on the
forum had in their CSV. Ideally speaking, this would be easier if these two lines didn't exist. But, for the purposes of this flow, I'm going to leave them in, because you might have
a header row like this and it's not too difficult
to get rid of it. So the first thing I'm going to do is go to my flow here,
and do a Compose step. (keyboard typing) And I'm just going to paste
in the content of the CSV. I'm going to rename it, CSV. Then I'm going to add another step. And this one is just going
to contain a newLine. I'm going to use that
newLine to split this CSV based on the new line. So I'm going to do another Compose and the expression I'm going to use Split. And I'm going to pick my dynamic content, and we'll choose the CSV. And then I'm gonna
split it on the newLine. (keyboard typing) Now at this stage, the output, this would still
contain those two lines that we don't want from the beginning. So I'll just have another step, I'm gonna Compose and I'll call this... I'm just going to say, Skip. Skip will move past some
elements of an array. So I'm going to use the
output of splitNewLine, and I'm going to skip forward two records. Because this is now an
array, the output of this. So if I okay that, and save that. I'll just show you that
before we go any further. I'll just execute this. So here we've got the CSV
in it's standard format. The newLine doesn't show us anything. We then Split the whole lot on a new line/ You can see now, we have an array of data. This Compose here, has then removed... You can see this one has
got these two header lines. Whereas this one is just showing the data. So I'll call this one CSVData. So then we can go into
an Apply to each step and the output will be
the output of CSVData. Here, I'm gonna add another Compose. And what I like to do here sometimes, I'm just going to nip back to notepad... So if we take this, these
are the field names, and I'm just going to
compile some JSON from it. (keyboard typing) Okay, so now we've got the basis of a JSON
that we're going to use. So I'm going to call this JSON and here I will put that. I'm going to, in this JSON record, I'm going to put the value as um... Sorry, I've missed a step. So before this step, we'll
have to add it below. I'm gonna do another Compose. And I will say splitByComma. Let's move that below there. And the expression is going
to be split, item, comma. So, what comes into this,
applied to each, is each line. And each line is delimited, has got commas that split the fields. So we need to make a new array, which splitByComma will generate for us, by splitting the incoming
line based on a comma. Now in our JSON section down here, I can do dynamic expression. I'm going to use the power automate to generate the syntax for me. So, I'm going to use the
outputs of splitByComma. If I do a Peek Code, I can take that out,
where it's splitByComma, I'm just gonna delete that. I'm going to use an expression, and I'm going to do a '?' just in case it doesn't
have any value in there, and zero. And then just for speed, I'm going to take that back over to here, and I'm going to copy that into there, there, there, there, and there. I'm gonna change the reference, one, two, three, four, five. Delete that. Paste that in. And now this Compose will
generate us some JSON for that particular record. Now, I'm just going to
add another Compose. I'm going to delete it
again, straight away. I'm just going to use that to
get us the outputs of JSON. And then I'm going to peek the code. And just copy that. And I'm going to delete that. And then down here, outside of the loop, I'm going to have my Compose. And I'm going to put
in that same expression which is gonna produce
us an array of records. Let's call it CompleteJSON. Save. Let's test it and see what we've got. There's bound to be something
in here that isn't right, but we'll see. Right, so we'll start off with the CSV. It gets split into... Split into new lines here. So we've still got those
top header records. Then we're just down to the data that goes into the Apply To Each. splitByComma changes all of
each field into a record. Then the JSON is produced
for us for the first record, and so on, and so on. And then this final one here, compiles all of the results into an array, which we can use for whatever we want. And that is all there is to it. You should be able to build
that flow in no time at all. Give it a go, see how you get on. If you get stuck and leave a comment, and I'll try to help you out. And if you've lasted this
long through the video, you might as well like, and subscribe it. Cheers. Bye-bye.
