98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Pulling data from online database
Pulling data from online database
Resolved · Medium Priority · Version 365
Emilie has attended:
Excel Advanced Best Practice course
Pulling data from online database
Hello,
I’m currently working with the IHME Global Burden of Disease database to pull data on deaths related to antimicrobial resistance by country. However, the current process requires me to download individual spreadsheets and manually combine country-specific data: https://vizhub.healthdata.org/microbe/?settings=eyIxIjoiYW1yIiwiMiI6ImJhciIsIjMiOiJhbXIiLCI0IjoyMiwiNSI6MSwiNiI6MiwiNyI6MywiOCI6OTYsIjkiOjEsIjEyIjozLCIxMyI6MSwiMTQiOjEsIjE1IjozLCIxNiI6MSwiMTciOjMsIjE4IjoyMDIxLCIxOSI6ZmFsc2UsIjIwIjpmYWxzZSwiMjIiOjEsIjI0IjoiZW4iLCIyNSI6ImJ1Z0RydWdDb21ibyIsIjI2IjpbMSwyLDMsNCw1LDYsNyw4LDksMTAsMjJdLCIyNyI6WzQsMzEsNjQsMTAzLDEzNywxNTgsMTY2XSwiMjgiOlsyLDMsNCw1LDYsNyw4LDksMTAsMTEsMTIsMTNdLCIyOSI6WzEsMl0sIjMwIjpbMSw3LDExLDE3LDIzLDIyXSwiMzEiOlsiMS0xIiwiMS0yIl0sIjMyIjoiMS0xIiwiMzMiOlsxLDJdfQ==
Is there a more efficient method to extract large amounts of data from this or similar online databases directly into Excel? Alternatively, do you have any general tips on automating the extraction and formatting process from online sources?
Thank you in advance!
Best regards,
Emilie
RE: Pulling data from online database
Hi Emilie,
Thankyou for your question to the forum.
My advice would be to use Power Pivot which supports data modelling and could help with combining your country specific data more efficiently.
1. To enable Power Pivot in Excel, go to FILE > OPTIONS > ADD-INS > COM ADD-INS and tick Power Pivot
2. Access the tables on the webpage and copy the URL
3. In Excel go to DATA > FROM WEB and paste in the URL
4. Select the table(s) and go to LOAD > LOAD TO and then tick for 'Only create connection' and 'Add this data to the data model'. Click OK
5. Now go to POWER PIVOT > MANAGE to see your source tables appear in Power Pivot
To create the data model and output to a Pivot Table you can follow the Youtube link below (from 5mins 20secs onwards):
https://www.youtube.com/watch?v=ZQoUpMiPDMA& ;list=PLbDvAYjpWJ7BkZVdyETS4Ec6H_xHrqzzu
Finally, if there are any changes to the data source, you can refresh the Pivot to update the changes.
Hope this has helped. Please let me know if you need any more support.
Kind regards
Martin Sutherland
(IT Trainer)
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Use the Ctrl-key for quick navigation in Excel 2010If you want to move quickly to the right, left, top or bottom of your spreadsheet, just press Ctrl and one of the arrow keys. If you want to then select all the data in that particular row or column, hold down the Shift key and press Ctrl and an arrow key. |