Extract Rows from Excel CSV (No Table)

[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?

[v-litu-msft]

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....

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.

[Anonymous]

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

[m_williamson]

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)?

[Anonymous]

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!

[m_williamson]

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.

[m_williamson]

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: