Show/Hide Toolbars

Ultra Mileage

Navigation: Examples: One2OneMileage

Example: Using One2OneMileage with an Excel Database

Scroll Prev Top Next More

This example shows you how to use One2OneMileage to compute the mileages for routes in a Microsoft Excel database. We use the same sample data as in the original Microsoft Access Coordinate example, and all routes are specified using longitude, latitude coordinates.

 

This example uses the one2one_coords.xlsx workbook that can be found in the Ultra Mileage examples file:

 

https://www.ultra-mileage.com/downloads/examples_ultra.zip

 

The workbook has a worksheet called Routes which looks like this:

 

ex_one2one_excel_input

 

The first row stores the names of the data fields. The data fields are the same as with the Access example. The output will be written to the distance, time, cost, and error fields.

 

Note that we do not recommend the use of Excel. This is primarily because One2OneMileage is designed to work with a relational database. Although Excel can be made to act like a relational database, it is inefficient and there are a lot of potential pitfalls. One of the pitfalls is that it assumes all numbers are floating point. Floating point numbers cannot be reliably compared for equality, so the primary key (ID in the above worksheet) must be either an integer or text. You must either use text labels ('A', 'B', etc) or explicitly tell Excel to make this column a text column. Do this by selecting the data cells in the ID column, right click and then selecting Format Cells from the pop-up menu. Under the Number tab, select Text. This will force these cells to be treated as text.

 

Another problem with Excel is that it does not support proper indexing. This is not a problem with such a small sample, but it will become significant if you run data with thousands of rows.

 

Setting the Ultra One2OneMileage Parameters

 

Start Ultra One2OneMileage. Press the "..." button in the Road Data box to select the Contiguous USA Fastest road pack (downloaded using the Download Manager).

 

Next press the Database Change button to display the Database Parameters dialog box. Set the database Type to "Excel 2007 onwards (xlsx)". Press the Data Source "..." button to select the Excel xlsx workbook file that will be the data source. One2OneMileage will load this and scan it for the available tables (i.e. worksheets).

 

Select the Routes$ table from the list of available tables. This table lists the required routes and will also store the results. One2OneMileage will scan the table and fill the remaining controls with the possible column options. Select the Locations are as "Coordinates" and press the Set Input Columns button to display the Input Locations dialog box. Set the dialog box to look like this:

 

ex_one2one_excel_locs

 

This sets the primary key and the data fields that store the input coordinates.

 

Press OK to return the Database Parameters dialog box, and then select the required output columns. You must set at least one of the output columns, otherwise One2OneMileage will not have any work to process. The resulting Database Parameters dialog box will look like this:

 

ex_one2one_excel_datadlg

 

Press OK to return to the main dialog box.

 

Select the required routing parameters. Note that some of these options might be disabled if they are not required for your selected data. For example, the time units are not required if you have not selected route times. For this example we set the Distance Units to be Miles and the Time Units to Minutes. We also select Fastest Routes in the Find box.

 

Press the Rate Table Costs button to display the Costs and Rate Tables dialog box:

 

ex_one2one_ratetable

 

You can use this to define a rate table that applies a different cost according to the route distance. Here we simply apply a Constant Rate of 0.50 per mile. Press OK to return to the main dialog box. This should now like this:

 

ex_one2one_excel_maindlg

 

 

Start Processing

 

Press Start to start processing. This is a very short table and should not take long to compute. The resulting Routes$ table should look something like this:

 

ex_one2one_excel_results

 

 

The exact numbers may vary according to the parameters that you have selected or the road pack version. The Cost field was created as a Currency type - hence the dollar signs.

 

Note that all of the rows were computed okay. One2OneMileage will add error notes in the Errors field if there were problems finding a location or calculating a route.