cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RP2
Helper I
Helper I

Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

Hello all, 

 

I'm working on building an automated flow and I'm not sure where to start, as I'm still very new to PA. 

 

I need to export a .csv attachment (9KB) that arrives via email (Outlook), and have that data entered into a SharePoint list for tracking and analysis. I'm not sure how to establish this flow or what the best way to approach it would be. I already have a SharePoint list with the elements that would be in the .csv attachment. This data would be dynamic, so I'm trying to keep one master list in SharePoint that could be written to every time a new attachment is fetched from the email (essentially, I do not want to create a new file each time, I would like to enter the new data into new rows in SharePoint each time). It would be best if I could automate this flow based upon a subject line trigger (i.e. every time an email comes in with "XXXXX" check for attachments and export data to SharePoint master list). 

 

The naming format for the attachment could be the same each time or it could be changed, whichever is easier. 

 

I could also extract the .csv into Excel then import it into SharePoint if that would be easier. If anyone has any suggestions please let me know. 

 

Thank you for your help

1 ACCEPTED SOLUTION

Accepted Solutions
v-alzhan-msft
Community Support
Community Support

Hi @RP2 ,

 

Since the File is come from a dynamic content so the dynamic content couldn't be choose manually.

You could use the expression:

1.png

items('Apply_to_each_2')?['ID']
items('Apply_to_each_2')?['Open']

And you could add the another columns also with the expression.

 

 

Best Regards,

Alice

 

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

View solution in original post

22 REPLIES 22
ChristianAbata
Super User
Super User

hi @RP2  get cvs data unfortunally is not supported in PA, to get data inside excel you need a new format time that is xlsx and inside a table created.

 

See the topic that recently solved.

https://powerusers.microsoft.com/t5/Building-Flows/Calculate-total-of-CSV-cell-and-populate-SharePoi...



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

@ChristianAbata 

 

Thank you for responding so quickly. If I had the attachment coming in as an .xlsx format would it be easier to export the attachment data points to a SharePoint list this way?

yes @RP2 .

 

See this example ada.PNG



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

Hello, 

 

I'm not sure if this is what I was looking for. I'll have an incoming .xlsx attachment coming in from an Outlook email that I need to extract into a master SharePoint list. I do not want to create a new file each time separately.

 

For example, say the incoming .xlsx attachment has the following fields.  Name - Open Time - Description - Resolved Time - Status. 

I already have those columns populated in my SharePoint list ("TestList"); however, each time this .xlsx attachment is sent via email I want to add the new data to the master SharePoint list "TestList," instead of creating individual files or overwriting any prior data in the "TestList." I hope that makes sense. 

shure @RP2  I understhand you but there are no way to read a file that is in a email, you need allways to create that file in somewhere to get the data inside.



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

@ChristianAbata  Gotcha. 

 

I ran the flow as described and it ran successfully; however, it doesn't look like it populated either the SharePoint or Excel list. Any ideas? 

@RP2  could you show me an screanshot?



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA

"When a new email arrives (V3)" -The new email arrives into a Test mailbox with a subject line trigger.

 

The "Create File action" - SharePoint folder path is the list that I want all of my data to be extracted into and the column values in that SharePoint list match with what's being extracted from the .xlsx attachment. 

 

"List rows present in table" - The file is my Excel document that also contains all of the same fields as the attachment from the email, and the table is the proper table for the aforementioned list. 

 

 

 

Flow 1 Updated .png

Additionally, When I look at the output of the last action (Excel) all of the values look like this - "Open Time": "", 

 

@ChristianAbata 

Removed

@RP2  please create list rows inside apply to each



Did I answer your question? Please consider to Mark
my post as a solution! to guide others :winking_face:

Proud to be a Flownaut!


If you want you can follow me at www.christianabata.com Quieres contenido en español? Síguenos en Power Automate LA
v-alzhan-msft
Community Support
Community Support

Hi @RP2 ,

 

Please refer to screenshot below to create the flow:

1.png

 

We could only create the file first in the library so then we could get the rows in the excel table and then add each rows to the sharepoint list.

If you don't want the file is created in the path, you could delete the file after the rows is added to the list.

 

Best regards,

Alice   

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

@v-alzhan-msft 

 

Unfortunately, I'm not showing any results for the "Table" section under the "List rows present in a table" action once I add the SharePoint ID to the file tab. 

 

I have the "Create File" action parameters correct. The site address is my SharePoint address, the folder path is where my list is saved containing the same elements of the attachment I'm trying to extract, file name is the dynamic attachment name from the Outlook email and file content is the dynamic attachments content from the Outlook email.

 

List rows present in table - Location is the SharePoint address, document library is the subfolder, File is the SharePoint "create file" ID, unfortunately I can't select a table at this point. Prior I was able to get it to run successfully but none of my data was shown.  Any ideas to what may be causing the issue? 

 

Update: Basically, it's saying the file is not found in SharePoint and I'm getting a 404 error. So the issue is under the 
"list rows present in a table" action. For some reason the table doesn't appear in the dropdown and I'm assuming the location and document library should be the same place that I created the file in SharePoint to begin with, then the file name is the SharePoint ID. 

 

v-alzhan-msft
Community Support
Community Support

Hi @RP2 ,

 

The Id is dynamic content, the table would never be got from the flow, so we need to manually enter the table name.As is refered in the provided link.

 

Best regards,

Alice   

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

@v-alzhan-msft  Thank you for clarifying. 

 

I understand that the ID is dynamic from the SharePoint action; however, even when I use an appropriate naming convention the flow still does not work. For example my attachment table name is export 1, so I have the "list rows present in table" action as the destination where I originally created the file in SharePoint the ID is the dynamic SharePoint ID and the table I typed in a custom name export 1; however this still didn't work for some reason. Is there another way to approach this? 

 

Thank you 

Just a quick update of what I have so far:

 

Flow1Updated.pngFlow2Updated.png

 

First image: The email arrives with the attachment with a trigger that sends it to a specified mailbox, the file is created in SharePoint with the attachment (name/content) "List rows present in table" - The Location is the general SharePoint site and the document library is the folder path where the document is stored. The file is the dynamic ID from the original SharePoint "create file" action. Lastly, the table "export (1)" is the same table name in the attachment that I'm trying to extract data from.

 

Second image: I'm applying the value from the "list rows present in table" action and creating an item in SharePoint at the list that I already have populated with the same fields as the attachment. Ex. My SharePoint list has Open time, Service Recipient and so on.

Where I'm running into issues is that nothing is being exported into my SharePoint list on the last "create item" step and I'm not sure why. The attachment is being extracted from the email and a file is created in SharePoint using the "create file" action, I just can't figure out how to get that data into my SharePoint list.

v-alzhan-msft
Community Support
Community Support

Hi @RP2 ,

 

I suggest that you should provide the run history of the flow so we could understand the issue.

Also please name the table with no special symbol, just take a try with name Table1, Table2.

 

Best regards,

Alice   

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

@v-alzhan-msft 

 

It seems that method still isn't working. I've changed the name to "Table 1" and I'm still getting errors. Specifically, I'm getting the following error - 

{
  "status"404,
  "message""SharePoint file was not found"
}
 
The "Create File" action is working fine and I do see the attachment in my SharePoint. However, the flow is failing at the 'List_rows_present_in_a_table' action. I must not have the correct path, can you please explain what each of these values mean in the 'List rows present in table' action?
1. Location
2. Document Library 
3. File 
4. Table 
 
For these above values, I have the location set as my primary SharePoint address. The Document library is set as the sub-folder where the attachment is saved. The file is the ID from the SharePoint "create file" action and the table is manually entered.  I've tried changing the table name, I've tried saving the file to OneDrive and creating the item in SharePoint after. The case is always the same my flow is failing at the 'List_rows_present_in_a_table' action, It's not recognizing the table name or created file
 
I've tried it multiple ways and received various error codes as follows:
400 "The parameter 'drive' has an invalid value"
404 "No table was found with the name 'Table 1", "Sharepoint file was not found"
 
Any suggestions?
v-alzhan-msft
Community Support
Community Support

Hi @RP2 ,

 

Why don't you share the screenshot?

And the same requirement with the same solution in the provided link is works for the user, you need to learn to trouble shooting your flow, create the the flow step by step with the provided solution then your flow would surely works.

 

Best regards,

Alice   

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

@v-alzhan-msft 

Unfortunately, I can't get the proper output into my SharePoint list now. I did figure out a workaround for the issue I was running into earlier. Instead of creating the file in SharePoint, I had to create it in a OneDrive folder as the SharePoint folder structure wasn't syncing up. 

 

The issue I'm running into is none of the data is being imported into my SharePoint list at action SharePoint "Create Item." I checked the output of the "list rows present in table" action and all of the data I need seems to be there in the output; however when it tries to populate my SharePoint list I get the result shown in the image. Any ideas as to why this may be happening? Flow1Updated.pngFlow2Updated.pngSPUpdated.png

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,439)