Extracting .CSV Email Attachment from Outlook and Importing into SharePoint

[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

[ChristianAbata]

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

[RP2]

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

[ChristianAbata]

yes @RP2 .

See this example 

[RP2]

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. 

[ChristianAbata]

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.

[RP2]

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

[ChristianAbata]

@RP2  could you show me an screanshot?

[RP2]

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

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

@ChristianAbata 

[RP2]

Removed

[ChristianAbata]

@RP2  please create list rows inside apply to each

[v-alzhan-msft]

Hi @RP2 ,

Please refer to screenshot below to create the flow:

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.

[RP2]

@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]

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.

[RP2]

@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 

[RP2]

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.

[v-alzhan-msft]

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]

@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]

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]

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