Follow

Pulling Export Data into Excel

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 Sources

This 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 Server

The 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: Connect to Database Server

The 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 at the Select Database Table Step

Okay, 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 Dialog

Excel Import Data, Select Table Dialog

At 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 Dialog

This 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 Tab

There 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 Data Exchange 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 Data Exchange 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 Properties

Don'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 table

Voilà!

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!

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Article is closed for comments.
Powered by Zendesk