5/04/2016

Snarfing Data from a website and importing via the Import and Export Wizard

Step 1, Snarf the data.  In my example I'm going to a publicly facing website and simply copying and pasting the html table into excel:





Next save this as a csv file.

Now jump into SQL Server Management Studio, drill down to your database (you may want to create a new, empty database for your snarfing), right-click and start the Import and Export wizard, via "Import Data":


This is pretty much a next, next, next, finish scenario (with a few selections that need to be made).  Just follow along with the screenshots:

Next

Flat File Source (choose your .csv)



Next


Under Choose a Destination, change this to the "SQL Server Native Client"


Next, Next, Finish  (you can preview your data if you like).  By default your data will end up in a table with the same name as your csv file.  You can also save the SSIS package for reuse if you like.



In the end you'll have all your data, copied from an html table, to a csv, and finally imported into SQL for your use: