cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
m_williamson
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
murshed
Microsoft
Microsoft

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

View solution in original post

2 REPLIES 2
murshed
Microsoft
Microsoft

murshed
Microsoft
Microsoft

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

View solution in original post

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

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

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (28,961)