How To: Pulling Export Data into Excel
Posted on: February 20th, 2012 by

Stefan Kapusniak

Stefan Kapusniak has over two decades of industry software development experience, using tools from the archaic to the modern, across all project phases from initial design to deployment, and with processes from Waterfall to Agile. Currently he works mostly in HTML5, Javascript, SQL Server and C# for Kildrummy, andNode.js, Postgresql, and a hint of Lua in his personal projects.

Latest posts by Stefan Kapusniak (see all)

Sometimes you want to get your Kildrummy® CostMANAGER Database into Excel without having to generate a new Excel file from the client and integrating it manually into your Excel Workbook.

No problem! In this post I’ll walk you through how to pull data directly from CostMANAGER directly into Excel 2007.

First open up the Excel Workbook where you want your data to end up. In this example I’m going to use a new one. Next go to the ‘Data’ section of the ribbon, and pick ‘From Other sources’:

Excel Ribbon: Data Get From Other SourcesThis will bring up a list of possible source types. We’re going to use ‘SQL Server’:

Excel Ribbon: 'From SQL Server' in 'Data/From Other Sources'Selecting this option brings up the Data Connection Wizard for a SQL Server connection. Here we enter the Server\Instance where our Kildrummy® CostMANAGER database lives and our user credentials so we can connect. Obviously yours will be different from mine:

Excel Data Connection Wizard: Connect to Database ServerThe next step is to select our Kildrummy® CostMANAGER database. We are also invited to select a table to query at this point, since we’re going to override this anyway I’ve chosen not to specify one by unchecking the ‘Connect to specific table’ option:

Excel Data Connection Wizard at the Select Database Table StepThe last step of the Data Connection Wizard is to save this connection information for later use. I’m just going to use the default file name but alter the Description and Friendly Name to something a little bit clearer, then click on Finish:Excel Data Connection Wizard on the Save Data Connection stepOkay, so now we have a connection defined and saved, but we haven’t actually used it for anything yet. Excel notices this too, and on exiting the Data Connection Wizard starts the Import Data process so we can actually set up the import details. Somewhat redundantly this starts out with our being asked for our Kildrummy® CostMANAGER login credentials and a table again. The table is still irrelevant so I’m just leaving it on the default:

Excel Import Data, SQL Server Login DialogExcel Import Data, Select Table DialogAt last! The import data dialog, For this example I’m just going to insert the Kildrummy® CostMANAGER data as a standard table and make it a new worksheet by selecting the ‘New worksheet’ option. We also need to make sure we click the ‘Properties’ button here rather than ‘OK’:

Excel Import Data, Import Data DialogThis gives us the ‘Connection Properties’ box. We make changes on the ‘Definition’ tab here so that instead of getting data from an internal Kildrummy® CostMANAGER table, we get it from a named Export:Excel Connection Properties Dialog, Definition TabThere are two changes I’m making. The first is to change the ‘Command type’ value from ‘Table’ to ‘SQL’, as the second change is to replace the ‘Command text’ with a small chunk of SQL code that calls into the Kildrummy® CostMANAGER DataEXCHANGE API to get the Export data. For my example of an Export called ‘Export’ that SQL is:

SET NOCOUNT ON; DECLARE @id UNIQUEIDENTIFIER;
EXEC spDE_GetConfigId 'Export', 23, @id OUTPUT;
EXEC spDE_Export @id;

A quick explanation of what this does. On the first line we ask SQL Server to return only the data and not the data as well as counts of records (it confuses Excel to get the counts as well as the data); we also set up a variable to hold the internal ID of our Export. On the second line we ask the Kildrummy® CostMANAGER DataEXCHANGE API to give us that internal ID for the Export named ‘Export’, and on the third we ask it to answer the data for the Export with that internal ID.

So, if you need to the data for an Export Configuration named something other than ‘Export’, it is the second line you need to change. Swap ‘Export’ in single quotes for the actual name of your Export in single quotes.

Having made those changes I click ‘OK’ and promptly get a long-winded warning box:

Excel, warning box after editing the Connection PropertiesDon’t worry about this. It’s just telling us that our connection isn’t the same as the one we have saved on disk. Click ‘Yes’ here, then click ‘OK’ on the import data dialogue and, after a wait for the data to be retrieved from the server the Kildrummy® CostMANAGER the Export data should appear as a new Worksheet just as I requested:

Kildrummy® CostMANAGER Export Data in a standard Excel data tableVoilà!

From here you can do any of the things you would normal do with an Excel Data table. For instance refreshing it should always pull the most recent version of the data from Kildrummy® CostMANAGER.

This is obviously not the shortest procedure in the world, but I hope you can see that it can be quite useful. Good luck with your Kildrummy® CostMANAGER Data Exports!