Save email attachment with flow and store data in a sharepoint list

[dv1407]

Hello
I have the following case, which I would like to automate:

I get an email with an Excel list in the appendix which is called Test,
now I want to save from the Excel list the data per row as a single entry in a sharepoint list.

Attempt at implementation:
I create a flow that controls the mail intake,
and saves the Excel list in SharePoint when it is called Test. -->Works

Now I read the saved Excel list in a further step, save the temporarily as a table,
and make individual entries out of them. --> Works

If I repeat the whole thing but after one minute I get the error message,
that the file is already open by me and cannot be changed. --> Flow fails

If I now add a timestamp to the file in the name, e.B. Test-2020-12-07-22-00,
then I can save it, but he can't find the list anymore,
because I can't include a variable in the "List rows in table" step under File.

Do you have an idea how to get it?

Greetings David

[annajhaveri]

Hello @dv1407 ,

Can you please provide screenshot of your flow, esp, the actions which are in the repeat loop? to understand more.

[dv1407]

Here it is:

[dv1407]

I found another error, here is the correct flow, where the error also occurs:

Issue:

{"statusCode":423,"headers":{"Pragma":"no-cache","Transfer-Encoding":"chunked","x-ms-request-id":"5c5fe572-e2af-42df-a8c1-10ab711a317e","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","Cache-Control":"no-cache","Date":"Tue, 08 Dec 2020 17:59:55 GMT","X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Content-Type":"application/json","Expires":"-1","Content-Length":"128"},"body":{"status":423,"message":"The resource you are attempting to access is locked","source":"api.connectorp.svc.ms"}}

[annajhaveri]

Is the action "List rows present in a table" and actions below it are added within apply to each action? If yes, can you please move all actions from "List rows present in a table" action outside apply to each, i think the main apply to each loop which loops through email attachments seems to be creating the issue.

[dv1407]

Hi there, sorry, but i don't understand what to do. If I put it completely outside, then it will always be executed, even if the system name does not meet the conditions. Now I have another problem: The Excel file comes either as xlsx or xlsm. Both of them get an error after saving that the file type is not supported when I save it as xlsx.

[annajhaveri]

There is a solution for this issue "Hi there, sorry, but i don't understand what to do. If I put it completely outside, then it will always be executed, even if the system name does not meet the conditions." as below

1. Do initialize variable at top of workflow with type boolean and set its value to No

2. Then do set variable in the Yes branch of condition in the apply to each loop and set value of variable which you initialized in top of workflow, set the value to Yes

3. Then under the loop, outside of it, again add the condition, and check if the value of variable is Yes, then send email.

Regarding the  The Excel file comes either as xlsx or xlsm. Both of them get an error after saving that the file type is not supported when I save it as xlsx - I will get back to you on this one.

[annajhaveri]

@dv1407  can you please provide the screenshot of the error that you getting while saving the file from the flow run

[dv1407]

I think it’s really tough now. If I save the Excel table now and then want to open it, then I always get the error "file format or file extension invalid"

[annajhaveri]

@dv1407  can you please help with screenshot of flow run that shows the error, i would like to know which action is creating error and what is the exact error.

[dv1407]

@annajhaveri 

The problem is that I now get the error when saving the file, so of course I can't go any further. I have now changed the flow as you wrote it, the error in the incorrect Excel list is still there. The Excel list is saved, but I cannot open it. Error trying to select the table: Error saving flow. Code:

DynamicOperationRequestClientFailure, message: "Error during dynamic operation request to API" excelonlinebusiness ", operation" GetTable "with status code" BadRequest ". This may indicate invalid input parameters. Error response: {" status ": 400," message ":" Not supported file format. Please select a different file. \ R \ nclientRequestId: b7a2b7a1-4ff5-405c-b7cf-8d4802d36f07 \ r \ nserviceRequestId: d9671431-6d26-4c8f-9012-af43cf5-405c-b7cf-8d4802d36f07 \ r \ nserviceRequestId: d9671431-6d26-4c8f-9012-af43cf5-405c "," erroriform ": {" message "Date:" Not supported " . Please select another file. "}," Source ":" excelonline-we.azconn-we.p.azurewebsites.net "}"

[annajhaveri]

okay, are you getting this error while saving file to onedrive or in action list rows from excel? Are you using a different excel file now, or have you made changes to flow other than i asked you to do? A full screenshot of your flow run would be helpful to guide further.

[dv1407]

The problem is that I cannot read the file because it is corrupt. The file, which I received by email, is fine. When I save the flow, I get the error message.

[annajhaveri]

okay so i see that saving file to onedrive action you have moved out of the apply to each loop,, please keep that still in the apply to each loop, as it was before, The actions from "List rows present in a table" should be out of the apply to each loop.

[dv1407]

I think the big problem is getting the xlsm files into an xlsx file. Could the one-drive convert?

[dv1407]

I have now changed the flow, the error with the unsupported file format still exists.

[annajhaveri]

Please provide full screenshot of your flow, i wouldnt know whats the issue until i see the flow.

[annajhaveri]

You need to put the actions in your flow in following sequence

[dv1407]

I have now changed it, unfortunately still the error, because the file format is not supported.

[annajhaveri]

Because of xlsm files? Can you first try with xlsx files?