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?

[lardo5150]

going to try that next.

I know nothing about the gateways, so I will have to research that and report back.

[abm · Accepted solution]

Hi @lardo5150 

Please follow this documentation.

https://docs.microsoft.com/en-us/power-automate/gateway-reference

Thanks

[lardo5150]

Does not look like that Excel Online Business connector works with File System.

[abm]

Hi @lardo5150 

I didn't realise that. You only get the data as excel format? I am thinking is any other way we could achieve your requirement or not.

Thanks

[lardo5150]

working on it now.

Creating the file on PC

We then upload it to SharePoint

I then delete the file on PC.

I am now working on the part to create the table while it is in Sharepoint.

Are we able to use dynamic content with the Excel connector?

[abm]

Hi @lardo5150 

You can pass the Dynamic variables except the filename for Excel connector.

https://powerusers.microsoft.com/t5/Connecting-To-Data/Can-t-set-dynamic-quot-file-quot-value-for-ex...

Thanks

[lardo5150]

So here is where I am at.

I am checking to see if a file is CREATED.

I get file content

I then create the file in SharePoint.

I then delete the file from the local system.

Here is what I am running into.

1.  The flow is kicking off TWICE.  Why I am not sure, but this could be part of my issue.  Any idea why or how to prevent this?

When I test, the folder is empty, and I just copy a premade file into that folder to kick off the trigger.

2.  I am running into the resource not found error when trying to create the table in the excel file I uploaded to SharePoint.  This could be a delay that I will work on, or it could be something to do with the flow running twice?  

Flow stops at list rows, as I am not done, just trying to fix the steps before that first before moving on.

[abm]

Hi,

For your first issue make expand the run history and check the trigger output. This will indicate what file it is created and running at that time.

Your second issue always place a file in SharePoint before running the flow. Yes also add a one minute delay will be good.

Thanks

[lardo5150]

For the SharePoint thing, I will create a separate flow.

For the trigger twice, the only difference I see is body is [] for the first initial trigger, but then there is a whole mess of stuff on the second.

 

[abm]

Hi @lardo5150 

Thanks for the screenshot. 

Not sure why you getting null in your first run. Add a Power Automate IF condition immediately after the trigger to check whether the trigger body is not null or not? If it's null use Terminate action to stop the flow. 

[lardo5150]

So I split up the flow into two parts.

First part grabs the file, sends it to SharePoint and then deletes it from the local system.

That is working.

The second flow, is set to trigger when a file is added to the SharePoint folder.

I am STILL failing at the Create table.  I am frustrated because i do not understand how the create table will tell me it can't find the resource, but the flow TRIGGERED off of the file being added???

Tried 1 minute, then tried 2 minutes, same result.

[lardo5150]

ran it again, 9 minutes later, as a test, with the file still sitting there.

Same error.

I have to be missing something.

[lardo5150]

Something else that is odd, not only does the flow trigger off of the file being there, but I can leave the file there and run a test, and it fails with the same error, even though I can go back to my excel connector and actually pick the file itself.

[lardo5150]

so one thing I tried was i changed the file name to something else, to start fresh.

and instead of deleting it from Sharepoint, I move it to another folder.

This worked.

I was messing around with my list in sharepoint and wanted to test the whole thing again.

Ran it through from start to finish, with the new file name and having it moved instead of deleted in sharepoint....failed on creating table, but this time, it said it can't overlap table.

So this tells me that Flow, still thinks the PREVIOUS file is still there, even though we just uploaded it, and it actually triggered flow.

[lardo5150]

As expected, when I run the Flow for the first time againts a file, for example, FileName1.xlsx, everything works.

If I export my data again, keep the same file name of FileName1.xlsx (because I have to do this because of the Excel Create Table does not take dynamic for file name), each of my three flows will fail.

Here is the third flow, where I actually take the table data in excel, and import it into the sharepoint list, and excluding any empty rows.

I will wait 24 hours then try it again.  Not sure if it is a cache thing or what.  If I can figure out a way to make create table dynamic easily, this might be better.

[abm]

Hi @lardo5150 

Looks like Dynamic filename / Deleting or replacing filename causing issues. It could be behind the scene connector might have some sort of specific identifier it might be keeping (metadata). I need to test this and let you know.

Thanks for the update.

[lardo5150]

I am having the owners of the program that exports the excel file, re-code so that when it exports, it exports with a Table already 🙂

So for now, i am building a new flow to import these into Sharepoint.

Thanks for everything!