Answered

Extract Data from Excel Email Attachment and Copy Rows into Central Hosted File

(0) Share

Report

Posted on by m_williamson

Hi all,

I have a problem almost identical to this thread: https://powerusers.microsoft.com/t5/Connecting-To-Data/Extract-data-from-excel-email-attachment-and-update-central/m-p/345931#M6191 . However, the solution wasn't explicated in that thread and the attached thread throws up an invalid link. So I will ask it again.

Aim:

To extract rows (all bar the first/title row) from a CSV in a series of email attachments, and append these to an Excel workbook stored in a communal SharePoint file location.

Task:

I have a scraper that accesses an API at pulls some data and outputs this into CSV (in a consistent format).
The code then sends this CSV in an attachment to my inbox.
My outlook rules know to handle this and store it in a folder 'Form-D Filings'
I want a Flow to detect this (When a new email arrives V3) which I can do
Then I want the flow to copy the attachment and paste this in a designated folder. This is the method I am using, is it correct?
Then I want the flow to open the copied CSV file, and extract all rows that are NOT NULL, aside from the first/title row. How do I set up the Excel connector to extract this dynamically? What does this mean? The data will be from CSV and, hence, no formatting?
Then I want the flow to append these rows under current data in a file that exists in one directory up (ie parent folder). How do I achieve this? What connector? What format?

I would appreciate any and all help, as I am new to Power Automate (worked with other Flow software before but not this).

Thanks in advance.

Categories:

Using connectors

I have the same question (0)

All responses (2)

Answers (1)

murshed Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Here is the link to the attached thread you mentioned:
Update Excel Spreadsheet from email attachment - Power Platform Community (microsoft.com)

Was this reply helpful? Yes No
Verified answer

murshed Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
For the copy file operation the current site address looks incorrect. Please take a look at the documentation. SharePoint - Connectors | Microsoft Docs
For editing CSV the Excel connector is probably not a good choice. The excel connector requires an excel table, which isn't available in CSV. There are many ways to solve this problem. Once way I am thinking right now is this: Create Table -> List Rows
For Create Table, specify the file name. Table name, say "FormD". Table range, say "A1:C10". Column name, say "Name, Phone, Email"
For List Rows, specify the table created above. Add a Filter Query. Here is an example: Solved: Filter Query ne null doesnt work - Power Platform Community (microsoft.com)
Let me know if you need help.
If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION ☑️. If you find this reply helpful, please consider giving it a LIKE 👍.

Was this reply helpful? Yes No