Unanswered

Moving data from one excel to another

(0) Share

Report

Posted on by Community Power Pla...

Hello,

My current situation is that every week I receive a report that I need to manually move data from into a master file with a few years of data. This is only about 40 lines of excel data moving each week.

I'm curious if it's possible to automate this, I have it set up so the email comes in with the excel attachment and it goes straight to a one drive folder. From there I'm trying to figure out how to automatically take data from the new excel into the master file. There are about 40 lines/rows of data and 20 columns.

Let me know if you have any ideas or have done this!

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report

Hi @Anonymous

If you are using an excel file without a table, you can use excel office scripts. You can build a bespoke solution that can grab content from the smaller file and pass it to the larger file.

Some details of Office Scripts:
https://docs.microsoft.com/en-us/office/dev/scripts/overview/excel

Take a look at the following demos https://www.youtube.com/playlist?list=PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

Thank you Damien, I'll be looking these over now!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

So this mostly works for my needs, my only issue is lets say for May and June the excel file is 180 lines, when it hits July it will add 30 lines for the newer data. It grows every month. Is there a way to adjust to that?

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Anonymous

Can you explain that again? Are you saying that the source file is varying in size or the destination or both? You can detect the last row or column via the script.

the full scripts are published here
https://techcommunity.microsoft.com/t5/microsoft-365-pnp-blog/excel-scripts-and-cloud-flows-data-manipulation/ba-p/2356956

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Definitely, sorry my brain was a little fried yesterday.

So the situation is the following:

Every week I get emailed a YTD Excel document that contains company info. So as of right now I can see from 1/1/2021 to 6/7. Every Monday the report is regenerated and sent to me via email. I need to extract lines from the report weekly and add it to a master file at the bottom that is linked to power BI to show me the past few years of Data.

So my latest question is since the sheet that gets emailed to me on Mondays is always having new lines/rows added how can I always pull the latest lines and not the old stuff I already have? There are identifiers I could pull it by such as date which are always the 1st of every month.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Anonymous

You can definitely perform a filter on the sheet and then extract only the filtered data. I demonstrate this concept in my office script chart demo on YT. So it is possible. I would suggest you use the office script recording tool and record your filtering action. You can then introduce a variable and dynamically change the filter you’ve recorded.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Was this reply helpful? Yes No