NBA
asked on
How to extract live data from an Excel sheet to Power Pivot?
Hello Experts,
I receive live data into an Excel workbook using Microsoft RTD.
The sheet contains between 5000 and 10000 rows of data as well as several columns in which I implement my models.
My purpose is to extract this live data and save it in a database.
Ideally, I would like this data to be extracted and saved to Power Pivot (or if not possible to Power BI).
Hence my questions:
(1) Is it possible, in Excel 2016, to extract and save data from an Excel sheet retrieving data through RTD, into Excel's data model (Power Pivot)?
(2) If it is possible, then how?
(a) What in your mind is the best solution(s) in terms of speed of extraction, resources consumption and ease of implementation?
Is there a native functionality? Should I use VBA? Is there other methods/ tools available?
(b) I imagine that there are two different extraction strategies: one to extract at regular time interval and the other to extract when a cell/ table is updated.
If the second option is possible, is it possible to extract and save only rows in which a cell is updated rather than the whole table? This might prove resource wise?
Thanks in advance for your help
NBA
I receive live data into an Excel workbook using Microsoft RTD.
The sheet contains between 5000 and 10000 rows of data as well as several columns in which I implement my models.
My purpose is to extract this live data and save it in a database.
Ideally, I would like this data to be extracted and saved to Power Pivot (or if not possible to Power BI).
Hence my questions:
(1) Is it possible, in Excel 2016, to extract and save data from an Excel sheet retrieving data through RTD, into Excel's data model (Power Pivot)?
(2) If it is possible, then how?
(a) What in your mind is the best solution(s) in terms of speed of extraction, resources consumption and ease of implementation?
Is there a native functionality? Should I use VBA? Is there other methods/ tools available?
(b) I imagine that there are two different extraction strategies: one to extract at regular time interval and the other to extract when a cell/ table is updated.
If the second option is possible, is it possible to extract and save only rows in which a cell is updated rather than the whole table? This might prove resource wise?
Thanks in advance for your help
NBA
Tom Farrar
Have you looked into bringing the data in via Power Query, an ETL tool that links data to Power Pivot's data model?
ASKER
Hello tomfarrar,
sorry for the delay. I am checking into it. Thanks
sorry for the delay. I am checking into it. Thanks
Not sure what data from Microsoft RTD looks like. Does it come in as a Excel spreadsheet? Do you initiate the download? It sounds like you are only using some of the columns, and maybe there is other data wrangling you are doing? If you use Excel's "Get and Transform" on the Data Tab (formerly known as Power Query), then there are several options for connecting to the data. Also, once downloaded you have great tools to modify, clean or whatever may need to be done with the data before use. The good news is, once you walk through the steps for cleaning up the data, the steps are automated (like a macro) and will perform the same steps the next time the data is updated.
Once the data is formatted as you need it, you can save it to the Power Pivot data model (within Excel) where tables are created and can be related to other tables (like a relational database). Then reporting can be done within Power Pivot.
In summary, there are three steps: bring data in and clean up, add to the data model, and report with Power Pivot. Not to complicate the issue there is a newer tool called Power BI (better visual reporting) that uses the same functionality. Best thing to do is go to the internet and query videos about Power Pivot and/or Power BI. These tools have great functionality.
Once the data is formatted as you need it, you can save it to the Power Pivot data model (within Excel) where tables are created and can be related to other tables (like a relational database). Then reporting can be done within Power Pivot.
In summary, there are three steps: bring data in and clean up, add to the data model, and report with Power Pivot. Not to complicate the issue there is a newer tool called Power BI (better visual reporting) that uses the same functionality. Best thing to do is go to the internet and query videos about Power Pivot and/or Power BI. These tools have great functionality.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.