Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

Like (0) Share

Report

Posted on 21 Apr 2020 15:21:50 by RP2

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

Categories:

Business Process Flows

All responses (23)

Answers (1)

Yi66 58 on 24 Jun 2022 at 14:24:38

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

it doesn't work, the table name in the list rows present in the table.
v-alzhan-msft on 30 Apr 2020 at 02:18:45

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

Hi @RP2 ,

Hope the link below could help you:

https://www.c-sharpcorner.com/blogs/handling-date-time-in-excel-to-sharepoint-list-threw-msflow

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.
RP2 50 on 29 Apr 2020 at 16:54:04

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

@v-alzhan-msft Thank you for all of your help!

I have one final question regarding my flow. My Open & Resolved time formats look like the two images below in my Excel file (attachment) the columns are formatted for date in Excel.

However, I'm getting the following result once everything is imported into my SharePoint:

I'm sure there's some formatting I need to do in flow in order to have the correct time imported into my SharePoint; however, I'm not sure what expression to use and where it should be used. Currently my expressions to retrieve the Open/Resolved time from the Excel attachment are -
items('Apply_to_each_2')?['Open Time']
items('Apply_to_each_2')?['Resolved Time']

I would like to have the date format in my SharePoint the same as it is in the Excel file, ex. 2/21/17 10:23AM.

Thank you again for all of your help!
Verified answer

v-alzhan-msft on 29 Apr 2020 at 01:40:29

Like (1)

Report
Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

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:

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.
RP2 50 on 28 Apr 2020 at 15:50:28

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

@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?
v-alzhan-msft on 27 Apr 2020 at 01:34:03

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

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.
RP2 50 on 24 Apr 2020 at 14:12:52

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

@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 on 24 Apr 2020 at 01:31:10

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

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.
RP2 50 on 23 Apr 2020 at 14:29:22

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

Just a quick update of what I have so far:

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.
RP2 50 on 23 Apr 2020 at 14:03:35

Like (0)

Report

Re: Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

@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