{"id":3419,"date":"2018-06-07T15:04:02","date_gmt":"2018-06-07T15:04:02","guid":{"rendered":"https:\/\/max-drake.cc\/?p=3419"},"modified":"2018-08-04T19:20:29","modified_gmt":"2018-08-04T19:20:29","slug":"excel-web-scraping-and-connecting-to-database-and-dynamic-updating-of-data","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=3419","title":{"rendered":"Excel web scraping and connecting to database. Dynamic updating of Excel imported data"},"content":{"rendered":"<p>After writing the previous post on web scraping and the example given of using Google Spreadsheet to obtain a web table I started wondering about Excel. I was aware that it had a web connection so decided to explore those.<\/p>\n<p>This first video shows how to automate the data scraping&nbsp; from websites into Excel:<\/p>\n<p><iframe title=\"Automated data scraping from websites into Excel\" width=\"678\" height=\"509\" data-src=\"https:\/\/www.youtube.com\/embed\/qbOdUaf4yfI?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p>Then there is this video on how to dynamically update the data that has been imported:<\/p>\n<p><iframe title=\"MS Excel - Import Live Data From Web to Excel\" width=\"678\" height=\"381\" data-src=\"https:\/\/www.youtube.com\/embed\/2yTAyVXzFGg?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p>You can also import data to Excel from an API, there are a couple of videos on YouTube on this. Here is <a href=\"https:\/\/www.youtube.com\/watch?v=mmT4qxOnEZk\" target=\"_blank\" rel=\"noopener\"><strong>one of them<\/strong><\/a>. The only thing that i can see that is slightly different from the standard URL web request is that the data could come back in an XML or JSON format and this needs to be adjusted when coming into Excel.<\/p>\n<p>So I thought I would play with the basic import of data from the web to a Table in Excel.<\/p>\n<h3>Importing Sharemarket Table from Web to Excel and allow for Automatic update<\/h3>\n<p><img decoding=\"async\" class=\"wp-image-3429 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp10-1024x985.jpg\" alt=\"\" width=\"1517\" height=\"1459\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp10-1024x985.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp10-300x289.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp10-768x739.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp10.jpg 1916w\" data-sizes=\"(max-width: 1517px) 100vw, 1517px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1517px; --smush-placeholder-aspect-ratio: 1517\/1459;\" \/><\/p>\n<p>&nbsp;<img decoding=\"async\" class=\"wp-image-3428 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp11-1024x734.jpg\" alt=\"\" width=\"1433\" height=\"1027\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp11-1024x734.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp11-300x215.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp11-768x551.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp11.jpg 2024w\" data-sizes=\"(max-width: 1433px) 100vw, 1433px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1433px; --smush-placeholder-aspect-ratio: 1433\/1027;\" \/><\/p>\n<p>The first document below is not the data we want, so we&#8217;ll check the table <img decoding=\"async\" class=\"wp-image-3427 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp12-1024x684.jpg\" alt=\"\" width=\"1534\" height=\"1025\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp12-1024x684.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp12-300x200.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp12-768x513.jpg 768w\" data-sizes=\"(max-width: 1534px) 100vw, 1534px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1534px; --smush-placeholder-aspect-ratio: 1534\/1025;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-3422 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp13-1024x696.jpg\" alt=\"\" width=\"1510\" height=\"1027\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp13-1024x696.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp13-300x204.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp13-768x522.jpg 768w\" data-sizes=\"(max-width: 1510px) 100vw, 1510px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1510px; --smush-placeholder-aspect-ratio: 1510\/1027;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-3426 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp14.jpg\" alt=\"\" width=\"1143\" height=\"922\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp14.jpg 879w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp14-300x242.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp14-768x619.jpg 768w\" data-sizes=\"(max-width: 1143px) 100vw, 1143px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1143px; --smush-placeholder-aspect-ratio: 1143\/922;\" \/>&nbsp;&nbsp; <img decoding=\"async\" class=\"wp-image-3425 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp15-1024x600.jpg\" alt=\"\" width=\"1506\" height=\"882\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp15-1024x600.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp15-300x176.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp15-768x450.jpg 768w\" data-sizes=\"(max-width: 1506px) 100vw, 1506px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1506px; --smush-placeholder-aspect-ratio: 1506\/882;\" \/><\/p>\n<p>So, the steps to the above image import the table.<\/p>\n<p>Now we want to be able to refresh that data when we reopen the file and I have also set it to refresh every 5 minutes. Just to make sure that the data is current.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-3424 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp16.jpg\" alt=\"\" width=\"975\" height=\"393\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp16.jpg 975w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp16-300x121.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp16-768x310.jpg 768w\" data-sizes=\"(max-width: 975px) 100vw, 975px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 975px; --smush-placeholder-aspect-ratio: 975\/393;\" \/> <img decoding=\"async\" class=\"wp-image-3423 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp17-1024x599.jpg\" alt=\"\" width=\"1464\" height=\"857\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp17-1024x599.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp17-300x175.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp17-768x449.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp17.jpg 1994w\" data-sizes=\"(max-width: 1464px) 100vw, 1464px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1464px; --smush-placeholder-aspect-ratio: 1464\/857;\" \/><\/p>\n<h3>Importing Table from Database to Excel and allow for Query of Data<\/h3>\n<p>Here is a video that shows the process of importing a table from MS SQL to Excel:<\/p>\n<p><iframe title=\"How to import data from SQL Server to Excel\" width=\"678\" height=\"381\" data-src=\"https:\/\/www.youtube.com\/embed\/VQrKg0GgZR0?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<h3>My Process<\/h3>\n<p>First In the Data Tab I click on Get Data and Choose From Database &amp; then from SQL Server Database. <em>(There does not seem to be very many non- microsoft options here, like MySQL or PostGresSQL etc)<\/em><\/p>\n<p><img decoding=\"async\" class=\"wp-image-3441 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp20.jpg\" alt=\"\" width=\"983\" height=\"671\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp20.jpg 939w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp20-300x205.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp20-768x524.jpg 768w\" data-sizes=\"(max-width: 983px) 100vw, 983px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 983px; --smush-placeholder-aspect-ratio: 983\/671;\" \/><\/p>\n<p>Choose Advanced options:<\/p>\n<p><img decoding=\"async\" class=\"wp-image-3440 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp21-1024x591.jpg\" alt=\"\" width=\"1625\" height=\"937\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp21-1024x591.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp21-300x173.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp21-768x443.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp21.jpg 1389w\" data-sizes=\"(max-width: 1625px) 100vw, 1625px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1625px; --smush-placeholder-aspect-ratio: 1625\/937;\" \/><\/p>\n<p>Choose server, and database as you most probably have an idea of what you want to query in the database, and this shows you the SQL statement box.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-3436 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp22-1024x888.jpg\" alt=\"\" width=\"1486\" height=\"1289\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp22-1024x888.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp22-300x260.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp22-768x666.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp22.jpg 1454w\" data-sizes=\"(max-width: 1486px) 100vw, 1486px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1486px; --smush-placeholder-aspect-ratio: 1486\/1289;\" \/><\/p>\n<p>Go to your SQL Management Studio to get your Server Name &amp; Database name<\/p>\n<p><img decoding=\"async\" class=\"wp-image-3439 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp23-1024x583.jpg\" alt=\"\" width=\"1525\" height=\"868\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp23-1024x583.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp23-300x171.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp23-768x438.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp23.jpg 1873w\" data-sizes=\"(max-width: 1525px) 100vw, 1525px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1525px; --smush-placeholder-aspect-ratio: 1525\/868;\" \/><\/p>\n<p>For querying a specific table ( you may want to query several tables with INNER, OUTER JOINS etc) just do a basic query on the table<\/p>\n<p><img decoding=\"async\" class=\"size-large wp-image-3438 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp24-689x1024.jpg\" alt=\"\" width=\"678\" height=\"1008\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp24-689x1024.jpg 689w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp24-202x300.jpg 202w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp24-768x1142.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp24.jpg 820w\" data-sizes=\"(max-width: 678px) 100vw, 678px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 678px; --smush-placeholder-aspect-ratio: 678\/1008;\" \/><\/p>\n<p>You can see the SQL query, so you can copy\/paste to the Excel Query Box<\/p>\n<p><img decoding=\"async\" class=\"wp-image-3437 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp25-1024x485.jpg\" alt=\"\" width=\"1464\" height=\"693\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp25-1024x485.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp25-300x142.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp25-768x364.jpg 768w\" data-sizes=\"(max-width: 1464px) 100vw, 1464px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1464px; --smush-placeholder-aspect-ratio: 1464\/693;\" \/><\/p>\n<p>I have altered it slightly by using select * (all) anmd using a WHERE statement to only choose one buildings Room Data.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-3435 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp26-1024x911.jpg\" alt=\"\" width=\"1171\" height=\"1041\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp26-1024x911.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp26-300x267.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp26-768x684.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp26.jpg 1438w\" data-sizes=\"(max-width: 1171px) 100vw, 1171px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1171px; --smush-placeholder-aspect-ratio: 1171\/1041;\" \/><\/p>\n<p>After pressing OK it has a pop-up in Excel showing the tabulated data. You can check that you have the right information (<em>if not, go back and alter query- I personally would develop the Query in SQL Management Studio, as it would be quicker- then paste it into Excel<\/em>)<\/p>\n<p><img decoding=\"async\" class=\"wp-image-3434 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp27-1024x705.jpg\" alt=\"\" width=\"1540\" height=\"1061\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp27-1024x705.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp27-300x207.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp27-768x529.jpg 768w\" data-sizes=\"(max-width: 1540px) 100vw, 1540px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1540px; --smush-placeholder-aspect-ratio: 1540\/1061;\" \/><\/p>\n<p>Use the Load To.. as this will give you options on where you want to place the data (New\/Existing Worksheets and which Cell)<\/p>\n<p><img decoding=\"async\" class=\"size-large wp-image-3433 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp28.jpg\" alt=\"\" width=\"678\" height=\"654\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp28.jpg 819w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp28-300x289.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp28-768x741.jpg 768w\" data-sizes=\"(max-width: 678px) 100vw, 678px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 678px; --smush-placeholder-aspect-ratio: 678\/654;\" \/><\/p>\n<p>And the data comes in. If the database is being constantly updated you can use the REFRESH ALL button to update it , and if you want to modify the QUERY click on the highlighted QUERY on the right <img decoding=\"async\" class=\"wp-image-3432 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp29-1024x379.jpg\" alt=\"\" width=\"1529\" height=\"566\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp29-1024x379.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp29-300x111.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp29-768x284.jpg 768w\" data-sizes=\"(max-width: 1529px) 100vw, 1529px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1529px; --smush-placeholder-aspect-ratio: 1529\/566;\" \/><\/p>\n<p>Then Click on the SOURCE text in the box on the right hand side and this brings up the query that is for the table. You can alter it ( eg change project ID to another) press the OK button and update the table.<\/p>\n<h3><img decoding=\"async\" class=\"wp-image-3431 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp29a-1024x559.jpg\" alt=\"\" width=\"1471\" height=\"803\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp29a-1024x559.jpg 1024w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp29a-300x164.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp29a-768x419.jpg 768w\" data-sizes=\"(max-width: 1471px) 100vw, 1471px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1471px; --smush-placeholder-aspect-ratio: 1471\/803;\" \/> End Comments<\/h3>\n<p>The web scraping and dynamically updating tables\/data is interesting. A good way to keep tables current if they are using web data that is changing over time.<\/p>\n<p>The Excel from Database is also quite useful. I have loaded CSV files that have a phenomenal amount of rows and so Excel just slows to a crawl as its trying to manage that data. Putting the data into a SQL table and then using Excel to extract only the data it needs to analyse is definitely an alternative method. This will allow all the Excel tools to be used rapidly on a much smaller data set, which it does very well.<\/p>\n<p>Definitely both processes are useful.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>After writing the previous post on web scraping and the example given of using Google Spreadsheet to obtain a web table I started wondering about Excel. I was aware that it had a web connection so decided to explore those. This first video shows how to automate the data scraping&nbsp; from websites into Excel: Then [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3423,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,3,36,32,29],"tags":[],"class_list":["post-3419","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-api_json","category-data-extraction","category-databases","category-power-bi","category-web"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp17.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2018\/06\/impp17.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/3419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3419"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/3419\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/3423"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}