Import Data from Excel

Importing a data set from Microsoft Excel requires that the data be in a named table in Excel and that the columns and rows meet certain requirements.

The entries of the first row of the range of cells you select are treated as column names. The following column names are reserved by OptiPath: Name, Assemblage, Description, Index, Earliest, Latest, Type, Exclude, Order, Date, Distance. Any other column name is treated as a feature name, and the column is assumed to hold the data for that feature. Feature names are restricted to 50 characters. All column names must be unique.

If there is a column with the name Name, the column is assumed to contain the names of the artifacts. If there is no column with the name "Name", the first column must contain the artifact names. Artifact names are restricted to 50 characters. A unique name is required for each artifact and an artifact name cannot be the same as an assemblage name.

If there is a column with the name Assemblage, the column is assumed to contain the name of an assemblage to which this artifact belongs. Assemblage names are restricted to 50 characters. A unique name is required for each assemblage and an assemblage name cannot be the same as an artifact name.

If there is a column with the name Description, the column is assumed to contain the descriptions of the artifacts.

If there is a column with the name Index containing numerical values, the column is assumed to contain the indexes of the artifacts. A column name Index containing non-numerical values may be automatically converted to ~Index.

If there is a column with the name Earliest containing numerical values, the column is assumed to contain the earliest date which can be assigned to this artifact. This restriction has no effect at this time, but is planned as a future enhancement to OptiPath. A column name Earliest containing non-numerical values may be automatically converted to ~Earliest.

If there is a column with the name Latest containing numerical values, the column is assumed to contain the latest date which can be assigned to this artifact. This restriction has no effect at this time, but is planned as a future enhancement to OptiPath. A column name Latest containing non-numerical values may be automatically converted to ~Latest .

Column names Type, Exclude, Order, Date, and Distance are automatically converted to, ~Type, ~Exclude, ~Order, ~Date, and ~Distance respectively.

Data values for each feature can be logical (TRUE, FALSE), nominal (true, false, yes, no, present, absent, red, blue, green, etc.) or numerical (0, 1, 73, 3.14159, 2.71828, etc.). However, within a given column all data should be of the same type.

Warning! If your data contains columns of numerical data, it is a good idea to format those columns as numbers. To do so, select the cells, right click on the selection and select Format Cells... from the popup menu, and then choose the category Number. Due to a quirk in the Microsoft Jet database engine, if the first few cells are blank in a column that is formatted with the category General (the default format category), then numbers that appear lower down in the column may be dropped, leaving you with blank data in OptiPath!

To import data from Excel, choose Data Set | Import | Excel from the main menu of OptiPath. A standard Windows Open File dialog will appear (titled Import from Excel in OptiPath).

By default OptiPath will look for an Excel file named OptiPath.xls in the current directory (which is the directory where OptiPath is installed until you change it) or the directory from which you most recently imported/exported an Excel file. You may use the Look In and File name boxes to locate and import any Excel file.

The data to import must be in a named table in your Excel spreadsheet. By default, OptiPath will look for a table named "OptiPath". If OptiPath fails to find one you will be asked to provide the name of the table to import from your Excel file.

To name a table in Excel, you must open your spreadsheet in Excel, select the cells containing the table of data you want to name. The first row of the range of cells you select must contain the feature names, and the first column must contain the artifact names. Other entries must reflect data values. There is no problem importing a few extra rows or columns as they can be subsequently deleted in OptiPath.

After selecting the cells of your data table, choose Insert | Name | Define... from the main menu in Excel. A Define Name dialog will appear:

Enter the name you want to use for the table (here it is "Chumash") and click OK. You must then enter the same name in the OptiPath Excel Table Name dialog:

Once you have identified the table in Excel to be imported, you will be asked to enter a name for the data set once it is imported into OptiPath. The name must be no more than 50 characters and must differ from the names of any other data sets you might already have in your OptiPath file. By default the data set will be given a name of the Excel file name plus the current date and time.

Once the data is imported, you may edit it by choosing Edit | Data from the main menu of OptiPath.