How To Import the Output of Speed Handicapper® V6 to Microsoft® Excel® and Access®

How To Import the Output of Speed Handicapper® V6 to Microsoft® Excel® and Access®

Back to Table of Contents

A major goal in the upgrade from V5.0 to V6 was to be able to save the results of handicapping and reopen saved files with all work intact—and make the data available to new programs that we plan to develop. (With that second goal, some people might have made the saved data a proprietary format, but Carroll prefers to make it useful for anyone who wants to test their ideas.)

The files are saved in .XML format, which makes it possible to open them in almost any spreadsheet or database program.

You will need to be pretty much a power-user of Excel® or Access® to know what to do with the data after you import it, but here is how it is done.

The race files are saved to the Documents\SpeedHandicapperV6 folder which the program sets up when first installed. If you are a real power-user and know what to do with a schema file, the schema for the race files is also there, called “SpeedSchema.XML.”

blue arrow We recommend that you create a working folder for files for these purposes and make copies of race files and the SpeedSchema.XML, if you want to try the methods below.

Microsoft Access®

Access® has a direct, menu driven ‘access’ to .XML data.

Open Access® and select New > Blank Database

On the External Data tab, select .XML file. Navigate to the saved file folder (Documents\SpeedHandicapperV6 — or, preferably, your working file folder) and select a race file to open.

Select Import XML > Structure and Data

Access 1

Voila you have all the tables and their data loaded:

Acess 2

Back to Table of Contents

Microsoft Excel ®


Importing into Excel is not as straightforward, and there are a few more steps.

We have not found a way to establish a “template” for the data so that you can clear one race’s data and import different race files repeatedly into the same Excel worksheet. The best we have found is to save multiple copies of your data map (template) as separate worksheets and import each race file into a fresh copy of the template (instructions below). If you find a better way, please let us know!

We suggest you create a test folder for working with the files and copy some test files from the SpeedHandicapperV6 folder, then open them from Excel.

[Note that you can copy the file “SpeedSchema.XML” to your test folder and that this file is the schema for the saved race files. If you change the file extension from .XML to .xsd you can use this file as a data map in Excel. We tried this method and did not get the hoped for results (it imported all of the data, not just the mapped columns). If you want to look deeper into this, see the Microsoft® Office tech page.]

The following process is cumbersome, but does provide workable results in Excel:

In your working folder, select any race card .xml file to open to create a data map. It doesn’t matter which because you are not going to fill the data map with data until after it is created a saved.

Excel 1

In the Open XML window, select “Use the XML Source task pane.”

Excel 2

Click OK in the create schema message box.

Excel 3

You will now see all of the tables of the XML dataset and can expand them to see the individual fields (columns).

Excel 4

This is where it gets interesting…you have access to every field, which you can drag-and-drop to the top cell of a column of the spreadsheet on the left of the image below. Obviously you don’t want all the fields; what you probably want is what is contained in the handicap table, which you can click on to expand and see the fields. Note in the image below, we have scrolled down to highlight the “HorseName” field of the Handicap table as the first to drag to the spreadsheet.

Excel 5

In the image below we have added additional column headings with TodaysRaceNumber in the first column.

Excel 6


[You may learn how to empty data from a datamap and reuse it with other race files, but right now we don’t know how. We have used backup copies of the datamap for each race file. If you do have the solution, please let us know!]

In a saved backup of your data map, put your curser in the cell directly below a column heading and right-click. This shows a menu. Scroll down to “Import” XML—this opens a File Open window again. Navigate to your folder of copied race .XML files and select Open. (See the first Excel image above of the File Open Window.) The columns you set up become populated as shown below.

You can do a “Save As” of your populated worksheet from here in Excel—using a new file name so you do not overwrite your data map template.

Hopefully, this information will get you started with your own work if you’ve been wanting to test ideas using Excel®.

If you are an Excel® power-user and know a better way, please let us know!

Excel 7