Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

pulling data online database

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pulling data from online database

Pulling data from online database

ResolvedVersion 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)

Wed 16 Oct 2024: Automatically marked as resolved.

Excel tip:

Keyboard Shortcuts to Add Rows or Columns

Couple of other keyboard shortcuts. Shift+spacebar selects a row, Ctrl+spacebar selects a column. Select either row or column (or several) and use Ctrl and + to insert or Ctrl and - to delete rows or columns.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.