cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WebPortal
Post Prodigy
Post Prodigy

Parse a CSV file or convert it into Excel

Hello,

I'm trying to parse a csv file from a SharePoint or OneDrive for business folder without a commercial license.

Initially I thought this was such a trivial thing, but now it is looking incredibly complicated. I did read a lot of blog posts like this one here, but I can't seem to get it working:

WebPortal_0-1614112132377.png

The expression above is:

 

split(outputs('Get_file_content')?['body'],'')
 
But it is returning the error:
 
Unable to process template language expressions in action 'Initialize_variable' inputs at line '1' and column '6413': 'The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Object'. Please see https://aka.ms/logicexpressions#split for usage details.'.
 
Can anyone help?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User III
Super User III

ok, so now you can continue to follow the guide. The difference is your split line will now need to be something like this:

split(outputs('csvFileContent'), decodeUriComponent('%0A'))

(If you called your new compose csvFileContent).

Or you could get rid of the new compose and do it all in one step by changing the split line to:

split(base64ToString(outputs('Get_file_content')?['body']['$content']), decodeUriComponent('%0A'))

View solution in original post

12 REPLIES 12
Paulie78
Super User III
Super User III

Check out this blog post (and video) I wrote, it is more simple and the video shows building it from scratch:

https://www.tachytelic.net/2021/02/power-automate-parse-csv/

 

WebPortal
Post Prodigy
Post Prodigy

@Paulie78 

Thanks for sharing the video.

I believe I'm stuck on the very first step: how to get the file contents?

 

In your video, you just paste the content of the csv, but what I'm trying to do here is to load the csv from SharePoint, so I'm using get file contents, and then? What to type here?

WebPortal_0-1614163538786.png

 

Paulie78
Super User III
Super User III

I've just updated the blog post with more explicit instructions for you, but the expression you need is:

split(outputs('Get_file_content')?['body'], decodeUriComponent('%0A'))
 

 

 
WebPortal
Post Prodigy
Post Prodigy

@Paulie78 

thank you so much for your help!

I get the same error:

Unable to process template language expressions in action 'Compose' inputs at line '1' and column '6408': 'The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Object'. Please see https://aka.ms/logicexpressions#split for usage details.'.

 

It is clear to me that the split function is expecting a string and we're giving it an object (file), so I guess the "Get file content" step is a bit misleading or it needs further processing.

Paulie78
Super User III
Super User III

So just put:

outputs('Get_file_content')?['body']

Into a compose action and see what the output is (for troubleshooting purposes).

 

Paulie78
Super User III
Super User III

What sharepoint action are you using to get the file (it is covered up in your screenshot).

WebPortal
Post Prodigy
Post Prodigy

Sorry, it is this one:

 

WebPortal_0-1614170792032.png

 

Paulie78
Super User III
Super User III

My guess is that your CSV is not being recognised as a CSV and the outputs of get file content are a content type declaration and the content in base64. If you look at your run history, what does the raw outputs of "Get file content" show?

WebPortal
Post Prodigy
Post Prodigy

@Paulie78 

 

They show this:

WebPortal_0-1614178509341.png

 

 

And this:

WebPortal_1-1614178524022.png

 

Paulie78
Super User III
Super User III

Yes, it is what I suspected. For some reason your CSV is not being recognised as a CSV. If you are sure it is one, then you can do this in a compose action, call it csvFileContent:

base64ToString(outputs('Get_file_content')?['body']['$content'])

Then you should see your CSV in that compose action. But I suspect it still won't work, because it if was recognisable as a CSV, then you wouldn't need to do this.

WebPortal
Post Prodigy
Post Prodigy

@Paulie78Yep, that works!

Paulie78
Super User III
Super User III

ok, so now you can continue to follow the guide. The difference is your split line will now need to be something like this:

split(outputs('csvFileContent'), decodeUriComponent('%0A'))

(If you called your new compose csvFileContent).

Or you could get rid of the new compose and do it all in one step by changing the split line to:

split(base64ToString(outputs('Get_file_content')?['body']['$content']), decodeUriComponent('%0A'))

View solution in original post

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (67,919)