New Excel - Create table - Copy and Delete

[lardo5150]

So I have a program that exports excel data once a day.

I have it exporting to a folder on my Business OneDrive - PowerBI\Microsoft Flow

The file name of this excel spreadsheet is different each time, same name but it adds the date and time of the export to the file each time.

I have a flow setup like below.  When a file is created in that folder, list the files, then for each one (and there will only be one), since the excel sheet does NOT have a table, I need it to add a table.

This is where it constantly fails.

From there I want it to list the files again so that I can copy the file to another folder (where I will create a second flow to do some importing into SharePoint, but that is a different problem later) and then delete this file.

I may not be using the dynamic content right?  How is the dynamic content supposed to be used?

I only get back "value" for some reason.

Since the file name is different each time, I think I have to use the dynamic stuff?

[abm]

Hi @lardo5150 

Choosing a dyamnic content in the "File" field to choose a excel file in excel online(Business) connector is not supported in Microsoft flow currently,

Thanks

[abm]

Hi @lardo5150 

This post will give you some idea about how to dynamically pass the names.

http://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow

Thanks

[lardo5150]

ok, so I am back to where I originally started.

I have it setup for when a file is CREATED, as I found that when i chose modified, when the create table flow did actually run, it would cause the flow to kick off again.

So this is what I have, and I keep failing with this resource not found.  I even added a 1 minute delay.

this is driving me nuts.

So what I am doing is when I run the export, I will have give it a fixed name of closedcases.xlsx

I need to create a table with the data in the excel file, copy it and then delete it.

I am deleting it because as I mentioned above, if I do modified, it will cause the flow to kick off again.

[abm]

Hi @lardo5150 

Thanks for your reply. Generally one minute delay is good enough to create the file and setting the right metadata behind the scene. I would say please increase the delay for another minute or so and try.

Thanks

[abm]

Hi @lardo5150 

Please see this post and here also mentioned one minute delay.

https://powerusers.microsoft.com/t5/Using-Flows/Excel-business-Create-Table-404-Resource-not-found/t...

Generally one minute delay is good enough.

Thanks

[lardo5150]

I gave it five minutes, and still got the error.

The resource could not be found.
clientRequestId: 7696ec6d-98de-417f-b1ca-6316cc644d27
serviceRequestId: 7b4e6e8c-659f-492a-ab43-70d9cf81c226

Maybe it is something to do with me deleting and re-adding the same file in OneDrive?

I guess I could just set it back to modified?  Once the table is created though, it will modify the file and cause the flow to kick off again, unless there is a way to prevent that?

The second time it kicks off it SHOULD fail, because it will be trying to create a table, and the table will already be there.

[lardo5150]

So I changed it to modifed, and it still errors on the creating table

The resource could not be found.
clientRequestId: 6364bdba-ac20-40a3-bdaf-3ec7c11865e5
serviceRequestId: 814c558a-700a-44b0-bc55-a492773523f3

[lardo5150]

[lardo5150]

It keeps trying to run now.

So if I do modified, each time the flow goes in and creates a table, or fails at creating a table because it is already there, it gets into a loop and keeps trying to run.

is it possible to resolve this?

Or is the only way to do it is to do a OneDrive action of file created instead of modified?

[abm]

Hi @lardo5150 

You can add exceptions in flow by adding the parallel branch. Set one as success and other as failed condition.

Thanks

[lardo5150]

Can you give me an example?

[lardo5150]

 I might have to go the modified route, as keying off a created file does not work for me.

It just keeps saying resource not found, no matter what delay there is, and I am starring at both my physical onedrive and online, and the file is there.

[abm]

Hi @lardo5150 

I did tried to do this a different way

The create table part is failing for me not sure why and it says the argument is invalid or missing or has an incorrect format. I am investigating this now. Will update you soon.

Thanks

[abm]

Hi @lardo5150 

Ok I did some more test around this. Dynamic filename is not supported in Excel connector.

Thanks

[lardo5150]

Does the create table piece work when you do it like me?  By telling the excel connector the actual file name.

I have it in the screenshots above.

[abm]

Hi @lardo5150 

Yes create table worked for me as long as file exists in that path (not dynamic).

[lardo5150]

Starting to to think this is some weird OneDrive thing.

Depending on what I do, the error is either can't find it or can't overlap the table.

From my side, I see the file, I see it online, but Flow must be trying to pull it from somewhere, where the data is not in sync.

[lardo5150]

I even set a delay for 1 hour.  said it could not overlap a table.

Any idea where Flow is trying to pull from?  Is there some cache with onedrive somewhere?

[abm]

Hi
Can't you create the file locally using Power Automate File System. You need to install Gateway to acess the local file system.
Thanks