Premium Power Automate connectors to parse csv?

Kevin Schaeffer · August 16

I am working on a fairly complex Power Automate flow (at least by my novice standards) that is taking data from our EFT/Wire transfer excel files and adding it to an aggregated/formatted master excel workbook (since every payer has a different file format). I'm trying to do this with as little human interaction as possible, both for data integrity and to save staff time.

I'm having trouble with this when saving files as xlsx because the data is not formatted as a table. I've considered having Power Automate create a table from the raw data to then read, but this feels overly complicated for some reason. Another option would be to have the file saved as csv, and the parse the data from there. However, it looks like there's no native or free action to do this. Does anyone have any experience with any of the paid options (Encodian, Plumsail, iManage) to parse csv files and if any stand out?

I'm also open to other thoughts/ideas on how to make the xlsx files work. The easy answer is have the staff member open the file for 10 seconds and hit ctrl+t before saving to put the data into a table, but it's more so the principle of not having to open the file at all.

Alex Wong · August 18

few question trying to understand what you are trying to do:

is the intake file in csv or excel (if excel is it a range or table), referring to the EFT/Wire transfer excel file
are you trying to add to an existing excel (when you say "master excel workbook", that sounds like it), by existing, I mean it exist prior to your flow run. Or are you creating the excel file in the flow that consolidate from the various transfer file mention above bullet point.
- if existing, you can manually make that file into Table first, and that would be a one time task that doesn't need automating
a little more info on exactly what you plan to do in the flow will help too

There are multiple ways to save as excel file and create table out of it, some easier than others, and some is more advanced, but does it a lot faster. Some ways are:

use excel connector action in Power automate to create table in excel, then add each rows of data
use Office script (excel script) that intakes the csv data and create the excel file and make into table
use Graph API to save csv data into excel (fastest but need more technical skill)

Austen Brown · August 18

I have experience with the Encodian connector and tend to use that for instances like this, depending on your usage the free trial may cover what you need.

Here's more info on the "Free Subscription" for the Flowr/PA Connector API calls - https://www.encodian.com/pricing/

Kevin Schaeffer · September 5

https://community.blackbaud.com/discussion/comment/305917#Comment_305917

Hi Alex, really appreciate your response and apologies for the delay in getting back to you - I must not have notifications turned on for this community and my work on this flow has moved fast (often breaking things :)).

I was finally able to get Power Automate to take the original EFT file, save as an xlsx file, and turn the data into a table, which solved the whole issue - the next step in the flow takes the data from that new table and copies it to my master processing document (you are correct that already exists) and Excel takes it from there.

I'm actually pretty proud of the flow, especially given my relative naïveté when it comes to Power Automate. This is part of a larger process we are developing for saving, normalizing, reviewing/coding and batching EFT transactions into NXT. Just in case anyone comes across this post I will list out this particular flow below, but when it is final final I'll probably share a new post with more details of the entire process.

Trigger: When a file is created in Sharepoint
Schedule: Wait 30 seconds (I was noticing the file wasn't fully saved sometimes when the flow would trigger)
Get file content: take the identifier from the file
Create table: using the identifier from get file, with a default range of A1:O100 (no files are ever larger than this)
List rows present in table: using the table just created
Filter array: to clear out the blank rows
Apply to each > Add row into a table: points to the corresponding table in my master processing workbook
Send an email: Notifies users that there are new transactions to review
Move file: puts the original xlsx file in an archive folder in Sharepoint

Ivy Keller · October 17

https://community.blackbaud.com/discussion/comment/306527#Comment_306527

Not to resurrect an old post, but thanks for sharing what you did, @Kevin Schaeffer! I was searching for posts about this problem and found your post update helpful. I'm also a Power Automate novice trying to create a workflow that copies a .csv in our tenant fileshare folder over to SharePoint as an .xslx file. I've had some issues with the flow running but the file not updating properly, and this made me realize it's probably a timing issue with the trigger. I'm going to add a delay and see if that helps.

Kevin Schaeffer · October 21

https://community.blackbaud.com/discussion/comment/307874#Comment_307874

Glad you found it helpful! Yes the timing has been a big issue in my flows, but very dependent on whether the user is working in Excel online or the Excel desktop app, which just takes longer to sync changes back to Sharepoint depending on how you have that set up.

I'll also say that I have also had trouble with copying/moving files, especially if I want to change the file type or the file name, mostly in regards to re-using a "template" Excel file. A workaround I've found has been to use a Get File Content action to pull the data out of the Excel file and use the output to "create" a new file (essentially copying it but allowing me to use a new file name).

Not sure if that applies to what you're working on but there are many nuances and tricks to using Power Automate and, while I'm still certainly no expert, I'm happy to try to answer any other questions you have via PM!

Kevin Schaeffer · October 21

@Ivy Keller see above in case the message didn't notify you - I don't post here much if you can't tell.

Premium Power Automate connectors to parse csv?

Answers

Categories