Importing Excel Spreadsheet Tables

An Excel file is similar to a set of database tables. The column names each table are stored in the cells of the spreadsheets, above the data that should be imported.

The column names in the example spreadsheet are stored in row four. Instrument Manager will search for the column names in any row between row one and row twenty. The first row, of the first twenty rows, with the highest number of columns containing data will be chosen as the row of column names. The maximum number of columns that can be imported from a spreadsheet table is 200.

Data is located in the rows following the column name row. There can be a maximum of five blank rows between subsequent data rows.

Preparation

Given the flexibility afforded by Excel spreadsheets compared to traditional databases, you must take some precautions to help ensure Excel data is imported reliably and accurately.

Multiple Sheets

The Excel importer is capable of importing from multiple sheets in a single Excel file. By default, all mappings in a the import mapping file are applied to all sheets, meaning that every sheet will have its data imported according to the map file.

If you require some mappings to only apply to sheets with specific names, enter a Specmatch conforming filter in the SHEET column of the applicable mappings in the mapping file.

Table Structure

The Excel importer will search for the column names in any row between row 1 and row 20. The first row containing data in the highest number of columns will be chosen as the row of column names.

Warning: Column headings are expected to be found in either row 1 or row 2 of the spreadsheet. Due to an Excel driver issue, if column headings are in any other row, the data read from those columns may be interpreted incorrectly.

The maximum number of columns that can be imported from a spreadsheet table is 200.
Column headings should be text (not anything Excel would treat as a number, or other datatype).
Column headings should not contain any merged cells.
Ensure all columns have a heading name (if there are blanks, it is best to fill them with a dummy heading).

Data Consistency

The more consistent your data is in your Excel table, the easier it will be to create a working map file.

Ensure that each column is only used for one type of data.
Do not use merged cells.
Do not leave the value in a cell blank if you intend for it to be the same as a cell above. Instead, ensure each cell contains the value, even if it means some columns contain a lot of repetition.
Blank rows in the data are permitted, but if there are more than 5 blank rows together, the Excel importer will consider this to mark the end of the table data.

Procedure

Select "Import Data" from the Instrument Manager file menu.
Select the tag table into which the components should be imported. In a project database created from the default database template there will be only a single tag table.
Select "Excel Table" from the list of "Types of Files to Import".
Click the [Next] button.
Select the import mapping file to use. This will have to be prepared in advance.
Click the [Next] button.
Click the [Add File] button. Select the spreadsheet files to import and click the [Open] button. This adds the files to the file list. Any number of files can be added here. Files can be removed from the list by selecting them and clicking the [Remove File] button.
Click the [Finish] button to perform the importing.
If the imported table contains component names that already exist in your project database, then you will be asked if the existing component data should be overwritten. Existing components will not be completely overwritten. Only the data that is mapped from the imported file will be overwritten.

Drag and Drop

You can now drag and drop one (or many) suitable files, to be imported, on to the "Select Files" dialog rather than browsing for those files.