Connect to EQuIS SQL-Server databases with Excel

At AMEC we’re always looking for ways to give our project team direct access to their data. We have many staff who like to drill down into their data and dig around, tallying detects, evaluating trends, and calculating risk values. To meet staff needs where they are looking to muck around and don’t have a specific query in mind for the data management team to run, we will “dump” a subset of data into Excel so they can filter it, crunch it, and crush it.

In the past I’ve used open database connectivity (ODBC) connections to connect end users to our EQuIS databases with SQL server views. OCBC is a little clunky and needs to be configured for each computer connected to the database. I’ve just discovered that the “Get External Data” features in Excel 2007 are a dead easy way to grab EQuIS data from SQL Server.

To try it out I created a detects-only groundwater VOC view using Management Studio. Then I clicked on Get External Data>From Other Sources>From SQL Server. In the little dialog box that popped up I entered my server name and clicked “next”.

Then I picked the database name with the view.

Finally, I picked the view I wanted to display, made sure to pick cell “$A$1” so the data would be placed in the upper left-hand corner of the destination worksheet and clicked “finish” leading view data to appear in my worksheet.

If I save the Excel workbook and re-open it at any time and the data automatically refreshes. I can even refresh the data while the workbook is open.

I envision laying out a work book with a tab for each analyte group. Then, the project team could open the workbook during an active investigation when we’re loading lots of data get an up-to-date view of their project’s analytical results.

Other more advanced uses I see with the Excel “Get External Data” include adding query input features and creating a dashboard with constantly updating time-series charts.

Let me know how you find ways to use Excel as a database front end.