cancel
Showing results for 
Search instead for 
Did you mean: 

Cannot use EXCEL file ID on action Insert Row

Insert row can not be used with file ID. Get rows and other actions work fine.

Also, when EXCEL file is replaced,ID changes and flow don't recognise it anymore. The idea would

be to make ID dynamic , not static.

 

 

flow.png

Status: Under Review

Thank you for the suggestion. What you are asking for is the ability to use an action with dynamic input but without selecting a particular file when you're designing the flow. We will evaluate this feature.

Comments
Level: Powered On

@ipsingh79  Thanks you very much, because of your insight, I got further clues to solve the issue to finding out and use dynamic file vs static file.

 

I will be back soon with exact solution 🙂

Level: Powered On

@ipsingh79  @efialttes  @Streamer @jonorl  and All

We can now use dynamic file name to Excel connectors by small change (Verified)

 

1) Please create below flow

1.png

 

 

 

 

 

 

 

 

 

 

 

2 Please add below function in "Initialize variable" by Expression (Below function gives dynamic file name) - In this scenario, below function gives dynamically added file name from OneDrive

 

replace(substring(triggerBody()?['Id'],int(lastIndexOf(triggerBody()?['Id'],'.')),sub(length(triggerBody()?['Id']),int(lastIndexOf(triggerBody()?['Id'],'.')))),'.','')

 

5.png

 

 

 

 

 

 

 

 

 

 

 

 

3) As usual, Please make sure you have selected "Enter custom value" before adding table name of your Excel else you will get error while running the flow.

 

Please ask for any queries regarding above solution.

 

Best Regards,

Amit Kshirsagar

Level: Powered On

@amitK 

Thanks a lot for the solution to find the file ID. It works also for actions like copy file, get file in OneDrive.

Level: Powered On

@amitK , your solution is perfect when to read rows from Excel. Do you have any case to update or insert rows in Excel using dynamic file ID? I tried to replace the file with dynamic ID, but the field list will disappear as song as the ID is choose, I can't design the update nor insert. I have to use a static file and try to copy it to other place after update. But it may be due to the file lock, the copied file usually doesn't have the correct content. I tried to delay actions, but the waiting time is not a stable number. Sorry there are too many but, the Excel connector is really a nightmare for users who wants to automate some existing process which is based on Excel....

Level: Powered On

Hi @zdliang 

 

Microsoft Flow allows to read data from Excel table only.

This table can have single or multiple rows.

 

As per my information we can add rows into excel table, but I have not tried yet.

 

Flow needs single trigger to start the flow some of the e.g. are 1) You have got email is a trigger 2) Some one added file in Onedrive or Sharepoint is a trigger for flow to start.

 

Can you please send me business steps so I can try to generate Ms flow for you.

 

Level: Powered On

@amitK , the following chart is my testing flow. Update a row in an excel table, copy it to place A, then update another row in the same excel, copy it to place B. Sometimes, in place B, the content doesn't include the change made at 3rd step. This morning, I added delay action after update a row, it works better, no more issue. But when I try it in my real flow, sometimes, I can still encounter issues the content is not updated. It seems for a simple excel, the delay time is short, such as my example file, only 11k, for a big excel file, such as the file in my real flow, it is 10 MB, the delay time should be longer, now I use 1 min, failure rate is 50%, I will increase it to 2 min, and do more test. Anyway, working with Excel connector, nothing is expectable....

flow.jpg

Level: Powered On

Hi @zdliang,

Assume below snapshot is of test.xlsx.

Now to replace content of seq "1" from "original content 1" to "test 1", from where do you get the "test 1" data. (I am assuming you are collecting "test 1", "test 2" data from some other excel and copying updated excel into folder like 1, 2, 3 depending on seq column number)

 

Can you please send snap shots of Excel file from where data is to read and destination excel file snapshot, which will be copied in folder like 1, 2, 3 as I am not understood required flow.test.png

Level: Powered On

 

 

Assume below Excel is your source file.Source.png

 

 

 

 

 

and below is your test file

destination.png

 

 

 

 

 

 

And after running flow, test file will be updated as shown below, is this something you are looking for? (copy file function is skipped in below flow)

updated.png

 

Below is the flow snapshot

1.png

 

 

 

 

 

 

 

 

2.png

 

Level: Power Up

Any news on dynamic files for the Excel write connectors?

 

In our case I am having to manage over 100 near identical flows where a single flow would work if I could dynamically assign the Excel read and write files

 

We are generating customer reports, an basic XLSX is emaild to a shared mailbox by our CRM's report tool, the file is saved into a customer specific folder (folder name in email subject line), a table is added to the XLSX, it's then read in and written back out to a XLSX report template that contains pivots and graphs to provide visual overview of the data.

 

It all worked perfectly untill it came to writing the data to the invervidual customers report template. The problem is made worse by it relying on the file ID and not the path, so we cant even overwrite the report templates with a new baseline without breaking the flow, we have to manually update each file independantly.

 

There is simply no scalability without support for dynamic files.

Level: Powered On

In my opinion, this feature should be implemented with the highest priority, especially given the fact that the Excel connector currently allows to dynamically add a table to a workbook. Therefore, the ability to also inject (or update/remove) data this way is the logical consequence of that.