Unanswered

Extract Rows from Excel CSV (No Table)

(0) Share

Report

Posted on by m_williamson

Hi all,

I have a series of CSV files stored in a location in SharePoint — that have a title row (to ignore) and a variable number of content rows each time — and I am trying to extract the content rows using Power Automate, to append them to the bottom of an Excel Workbook.

An example of what the CSV looks like:

The connectors for Excel Online (Business) only refer to List Rows/Getting Rows from an Existing Table. How do I extract non-table rows:

With the same columns (in the same order) every time: $A:$I
Ignoring the title row every time: $A$1:$I$1
But with a variable number of content rows each time: A2:I2 at least and up to, around, A2:I502

I have tried dynamically setting up a table then extracting that data but I can't create tables as the input for File keeps failing as it either can't find the file of a name, or the file path is the wrong input:

Anyone any idea how to proceed to achieve my aims?

Categories:

Using connectors

I have the same question (0)

All responses (6)

Answers (0)

v-litu-msft on at

Like (0)

Report

Hi @m_williamson,

The only way to get Excel data in Power Automate is to format the data into a table. You could refer to this post:

Solved: How to extract data from an excel file that has no... - Power Platform Community (microsoft.com)

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

Hello,
I have recently had to do something like this as well, you need to get to grips with the Data operations, I managed to do it by:
1. Save the document to a known location if dynamically loaded, then get the file contents, Convert the csv data from base 64 to a string and replace special characters

2. Split the array by new lines to get the rows
3. Filter out blanks and select the column headers I wanted
4. Join the rows with a comma and put into a variable
5. Parse JSON on the variable so that Flow can read the data and then finally I can apply to each and append to a sharepoint list or in your case, an excel Table
I used this guide as a reference, unfortunately, my data was from an exchange report from Microsoft Graph so I had more issues to deal with, but this should get you going!

https://sharepains.com/2020/03/09/read-csv-files-from-sharepoint/

Let me know if you have any issues!

Rob

Was this reply helpful? Yes No
m_williamson 17 on at

Like (0)

Report

Hi Rob,

Thanks for going into the step-by-step solution for that. I'll have a crack this afternoon (busy morning) and feed back. Off the top of your head, is there an easy way to be dynamic about retrieving the file content: the new content comes through with a different name each time (as the name includes a date for which the output was generated)?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi Again,
Sort of, how is the csv generated, is it from an automatic process like a graph call or is someone physically making the file?

If the rules in the file are pretty static, say Thisreport - Todays date.csv then you can put in custom values in the file name in get file content, just create a variable that has the date in it formatDateTime(Now(), 'ddmmyyyy') <- format this how the file will be, and when calling the file name add the variable into the name:
You will need to be strict and careful about the saving and naming of the file though!

Was this reply helpful? Yes No
m_williamson 17 on at

Like (0)

Report

Hi Rob,

The CSVs are generated by a Python script that accesses an API, checks if there is new data and pulls the data, compiles this into a CSV and emails this to me. My Power Automate then extracts these and puts each in a designated folder in SharePoint.

In terms of the file name, since it's generated by a script, it will be consistently in the format:
prefix_YYYY_MM_DD, where YYYY is year, MM is month, DD is day, and prefix is the same string of letters with underscores (sec_form_D). So I think your suggested method should work — again, I'll need to have a crack at it first this afternoon and I'll let you know if I run into any problems.

Thanks again for your help and for being so explicit with the steps.

Was this reply helpful? Yes No
m_williamson 17 on at

Like (0)

Report
Hi Rob,

I have been making these changes this morning, however, I have a couple of queries:
What's the need to replace your special characters? Is this a necessary step? I have URLs, for example using : / . _ or timestamps using - : or investment values using , so these would these be fine when passed into JSON? What are the ways around this?
In your Split, you use a decodeUriComponent — can I just use ';' instead surely?
I must've missed something, because the next step Filter array calls 'SplitString', and your code doesn't indicate where 'SplitString' comes from — am I missing a predefined variable somewhere?
As a result of above, my Flow crashes due to the Invalid Template or the Filter array:
This is the current setup of my Filter array:

Was this reply helpful? Yes No