Parse CSV to Excel with out any external connector

(0) Share

Report

Posted on by vamsi_varanasi

152

Dear Team,

I am working on a very very high sensitive data and every day I receive a CSV file with in my inbox. I wanted to create an excel file or I wanted to load this data ( not all columns, only couple of columns ) in SQL. Could you please help me to read and parse this data in power automate with out using external connectors.

please guide any solution.

Thanks

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

Paulie78 8,422 Moderator on at

Like (0)

Report

I did a video on converting CSV to JSON, which would be a goof starting point for you (because once it is JSON you can do whatever you like with it).

https://youtu.be/0RqSco9Til0

see how you get on with the initial conversion, the rest should be easy.

Was this reply helpful? Yes No
vamsi_varanasi 152 on at

Like (0)

Report

Hi @Paulie78 ,
I tried the solution , it is worked. Thanks for that. I have an issue for one of the column values in CSV file.
Customer Name : it is as below

FirstName, LastName

When I tried to split the entire data, customer name also getting splitted into two values and the rest of the columns with in the record is getting adjusted with wrong header names.

do you suggest any solution for this ?

Thanks,

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
It's really a tricky situation to deal with. Dealing with CSV in Power Automate has issues and this is one of them. Is your CSV quoted or not? By that I mean, does it look like this:

"Jimmy", "Johnson", "31"

or

Jimmy, Johnson, 31

Quoted makes it slightly easier to work with, but I guess your CSV is not quoted, otherwise you would have had problems already.

Was this reply helpful? Yes No
vamsi_varanasi 152 on at

Like (0)

Report

Hi @Paulie78 ,

This is my actual data in CSV -

This is my parse data -

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

That field is CSV quoted, so you might be able to do something with it (you can see the CSV quote at the start of the remark property and then end of the P property.

Was this reply helpful? Yes No
vamsi_varanasi 152 on at

Like (0)

Report

This is the data after removed the header record and before split with comma ( , )

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

I will have a think about it, but it is a common weakness of the CSV parsing method I demonstrated. I'm sure the external connectors could handle the quoted string easily. PowerShell handles it no problem, so you could use an Azure function if that was confidential enough for you. Probably is a way to do it in PA, but needs a bit of careful thought.

Was this reply helpful? Yes No