Run a query from two data sets programmatically

Question

I am trying to reconcile data from a website and a database programmatically. Right now my process is manual. I download data from the website, download data from my database, and reconcile using an Excel vlookup. Within Excel, I am only reconciling 1 date for many items.

I'd like to programmatically reconcile the data for multiple dates and multiple items. The problem is that I have to download the data from the website manually. I have heard of people doing "outer joins" and "table joins" but I do not know where to begin. Is this something that I code in VBA or notepad?

Answer 1

Python is a scripting language. http://www.python.org

There are tools to allow you to read Excel spreadsheets. For example:

http://michaelangela.wordpress.com/2008/07/06/python-excel-file-reader/

You can also use Python to talk to your database server.

http://pymssql.sourceforge.net/ http://www.oracle.com/technology/pub/articles/devlin-python-oracle.html http://sourceforge.net/projects/pydb2/

Probably the easiest way to automate this is to save the excel files you get to disk, and use Python to read them, comparing that data with what is in your database.

This will not be a trivial project, but it is very flexible and straight forward. Trying to do it all in SQL will be, IMHO, a recipe for frustration, especially if you are new to SQL.

Alternatively:

You could also do this by using VBA to read in your excel files and generate SQL INSERT statements that are compatible with your DB schema. Then use SQL to compare them.

Answer 2

Generally I do this by bulk inserting the website data into a staging table and then write select statments to join that table to my data in the database. You may need to do clean up first to be able to match the records if they are stored differently.