Hi @Ahammad_Riyaz
Yes, this is achievable - I spent way too long testing this out on my side, haha. I am tired, so this is going to be a quick screenshot dump - with probably a lot of inelegance, as I was just going for function!
My demo CSV...
To start, we get the File Content from your CSV file.
Then, we start processing the File Content. I used these actions:
Split by NewLine: I'm replacing the newlines with a dollar sign, and then splitting on them.
split(replace(outputs('Get_file_content_using_path')?['body'],decodeUriComponent('%0D%0A'),'$'),'$')
Filter array - remove nulls: just what it sounds like. Looking at Compose - Split by NewLine's outputs:
@equals(empty(item()), false)
Compose - get array header values and split on comma
split(first(body('Filter_array_-_remove_nulls')),',')
Compose - array without the header values
skip(body('Filter_array_-_remove_nulls'),1)
Now that we have that going, time to set up the Excel doc.
Create an Excel file, create a Table (using a variable so you have the name, and setting the range to A1), then we use our previous Compose to get the column names, and drop them into the field.
(Note: in my test I did it in a dumb way, so no screenshots, but what you'd do here is just turn your "Compose - get array header values and split on comma" into a string variable and plop them in there.
Now, from here, it's pandemonium.
In an "Apply to each", I am using the Outputs from "Compose - array without headers..".
First, I split them by comma so I have them individually.
Then, in another "Apply to Each" focused on the output from that split, I use the Append to String to build out my values. I set it to
"outputs('Compose_-_Get_array_header_values_and_split_on_comma')[variables('intIncrement')]": "outputs('Compose_-_Clean_out_the_other_linebreak')" and then I increase my intIncrement by 1. I use that to ensure it goes into the right column.
Theeeeeeen before the Apply to Each starts over for another row, I clear the vars.
I think the Office Script that @tom_riha mentioned sounds better, but, this was still fun to try.
R
