Copy Dynamic Microsoft Excel Content from One File to Another using Power Automate

Рет қаралды 35,785

Күн бұрын

Пікірлер: 38

@Steve-fs7ze 9 ай бұрын

This is amazing in its simplicity! Other instructional videos will have you running 2 scripts and importing them via Power Automate but this circumvents it and gave me a solution in 5 mins. Thankyou so much

@tw-waytek 9 ай бұрын

@Steve-fs7ze, thanks for your feedback and glad I could help.

@michaellange579 5 ай бұрын

This couldn't have been more helpful - thank you!

@tw-waytek 5 ай бұрын

Thanks @michaellange579 for your positive feedback, I'm glad I was able to help.

@JonathanDuggan-x8p Жыл бұрын

Great video! Very helpful.

@tw-waytek Жыл бұрын

Thanks Jonathan, I'm glad you enjoyed the video! There is more content in the pipeline. Let me know if there is something else you'd like see.

@sofiaibarra1883 Жыл бұрын

Hi! Do you know if it's possible to select a range like from A2 to C6 in your example, and then paste it to the other workbook? Thanks for the video:)

@tw-waytek Жыл бұрын

Hello @sofiaibarra1883 , thanks for reaching out and great question, as long as your cell range is set as a table you can copy its contents to other workbooks or worksheets.

@leilamottacunha Жыл бұрын

Hi! thanks for this video. Do you know how to copy data from a SharePoint library to an excel template, and save this template as a new document in another library?

@tw-waytek Жыл бұрын

Hello Leila, thanks for watching and for your question. Before I can answer, I have some questions. What type of data is to be copied from the SP library and can you be more specific on the template in excel, is the template in a Table?

@hempiden 3 ай бұрын

Hello my attachment is csv file, I get attachment content and save the csv file to sharepoint already but we get error at create table stage, what is your advise on that?

@tw-waytek 3 ай бұрын

Hello @hempiden , thanks for reaching out, you cannot do this flow with a CSV file, it only works with Excel files, but I have another video/post that will provide an example on how to convert a CSV file to Excel using an Office Script and Power Automate. Give it a try and let me know how you make out and if you have any other questions. www.tw-waytek.com/directly-query-a-power-bi-dataset-using-power-automate-and-save-it-to-excel-in-sharepoint/

@charisseaustria4711 6 ай бұрын

hello, great video. Sorry noob question, but i do not see the x-ms-file-id from the dynamic content, what should I do? or can I use full path instead? thanks so much in advanced!

@tw-waytek 6 ай бұрын

Hello @charisseaustria4711, thanks for reaching out with an excellent question, so it looks like I need to update my video due to recent updates in Power Automate. The trigger "When a file is created in a folder in SharePoint" is now deprecated and the new trigger is "When a file is created or modified in SharePoint (Properties Only)". For the Create Table action under the File property select the Dynamic Content from the SharePoint trigger called "Identifier". Try that out and let me know how you make out.

@charisseaustria4711 6 ай бұрын

@@tw-waytek OMG! thank you so much for your reply. It still doesn't work. It says branching condition not satisfied :( I'm trying to get data within the excel table and update file property for one of the columns in document library. Do you think it's possible?

@tw-waytek 3 ай бұрын

Hello @charisseaustria4711, did you get my email response? Were you able to finish your flow?

@brooketaylor3698 4 ай бұрын

For some reason the information is not spitting out into the final table. The flow is working, creating a table in step 1, however nothing is being copied into my "master" spreadsheet

@tw-waytek 4 ай бұрын

Hello @brooketaylor3698, thanks for reaching out, I will need more information to help troubleshoot. You can send me screenshots and more details of your flow at the link below: www.tw-waytek.com/contact-me/

@arahabigailbalgos6897 9 ай бұрын

Thanks for the video. Its very helpful. do you also happen to know what if i just want to update data in a row referencing from Excel 1 to Excel 2. Like if there are submissions with the same ID or unique row , it will trigger for it to update the existing row and not to just add data in a row? Hope you can read this! thanks again!

@tw-waytek 9 ай бұрын

Hello @arahabigailbalgos6897 , excellent question, I've never setup a flow in Excel to not only allow appending data but also updating existing data, I've made a note to do a future video on it. In the meantime, you can visit the link below to see how the data comparison part would be done, I hope this helps for now and thanks for turning into my channel. sharepains.com/2020/11/11/compare-2-excel-files-power-automate/

@raversmash Ай бұрын

I have a spreadsheet that i want to extract data for each individual employee into a separate spreadsheet. Is that possible? The names of employees tend to change so i would need PA to be able to recognize new names and extract them into their own separate spreadsheet when it shows up. Is something like that possible?

@tw-waytek Ай бұрын

Hello @raversmash, thanks for reaching out and great question, I will need more information on the request, you can reach out to me at the link below and we can go over the details: www.tw-waytek.com/contact-me/

@KabayanParesan 6 ай бұрын

Hi, Just a question. Why is there no x-ms-file-id on my options? :( how can i find it?

@tw-waytek 6 ай бұрын

Hello @KabayanParesan, thanks for reaching out, there's been some updates from Microsoft that now require some changes to the flow. Change the trigger to SharePoint: When a file is created or modified (properties only) then under Create Table and List rows present change the File dynamic content to Identifier. Let me know how you make out.

@tarshmidha5879 8 ай бұрын

is there a way we can let Power Automate to determine the number of rows in a new file which is needed to create the table?

@tw-waytek 8 ай бұрын

Hello @tarshmidha5879 , thanks for your question, you can by using the OFFSET and SUBTOTAL formulas. Here is an example of the formula: =OFFSET('Sheet1'!A7,0,0,SUBTOTAL(103,'Sheet1'!$A:$A),37). 'Sheet1'A7 is the starting point and 37 is the number of columns in the table, those should be the only two values you need to change. Let me know how you make out and if you have any other questions.

@SofiaRoemplerDellien 3 ай бұрын

Hi, what can i do if the column names don't appear on the "Add row to table" function step?

@tw-waytek 3 ай бұрын

Hello @SofiaRoemplerDellien , thanks for reaching out, there could be several reasons for this issue, can you contact me at www.tw-waytek.com/contact-me/ and share a screen shot of your Create table and List rows present actions and we can go from there.

@prudhvithoran7802 Жыл бұрын

That's Actually Very Great. But What if I have More Spread sheets in My excel File

@tw-waytek Жыл бұрын

Hello and thank-you for your question. Having multiple sheets in your Excel file will not be an issue since you must connect to a table in your worksheet. Each table can be connected to it's own flow.

@shuchenlu3459 Жыл бұрын

I followed your video and created a very successful flow. However, why this flow is only able to copy the first 256 rows from one Excel to another. My file actually had over 10,000 rows? Can you suggest why? Thank you!

@tw-waytek Жыл бұрын

Hello @shuchenlu3459, thanks for reaching out, there is a 256 row threshold limit for the Power Automate Excel action List Rows Present in a Table, to overcome this limitation, go to the List Rows Present in a Table action in your flow, go to Settings, go to Networking, turn on Pagination and enter the number of rows you need. Let me know how you make out with your flow or if you have any other questions.

@sewaerla Жыл бұрын

I tried to build the flow nearly as same as yours (Change Onedrive in stead of Sharepoint). It allway crushes at "Create Table": Error Message: BadRequest. Http request failed: the content was not a valid JSON. Error while parsing JSON: 'Bad JSON escape sequence: \U. Path 'message', line 1, position 73.' Any Idea what caused this problem?

@tw-waytek Жыл бұрын

Hello @sewaeria, sorry for the late reply but the message got blocked for some reason and I just noticed it now. I will review and respond after testing.

@sewaerla Жыл бұрын

@tw-waytek - Did you created the column names in Testfile two before the first input of Testfile one?

@tw-waytek Жыл бұрын

Hello @sewaerla, yes I did, you have to create the master data file and table first as the file and table information is required in the Apply to Each step. I hope this helps. Let me know if you have any other questions.

@sewaerla Жыл бұрын

@@tw-waytek Update. Everything runs fine. My failure was that i had the data of the incomming file allready converted into a table (created the file for testing Step 2 directly. Thanky for your great tutorial and response.

@tw-waytek Жыл бұрын

@sewaerla , great news and great job!! I'm happy to have helped, reach out anytime.