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)
