Microsoft Excel files with the file extension .XLS can be imported to create a dataset. In a Microsoft Excel file, data is organized within grids on worksheets, or tabs. A dataset can be created from a single worksheet only. If you want to import a file that contains multiple worksheets, you must create multiple datasets, one for each worksheet that you want to import.
In Microsoft Excel files, information is stored in columns that are identified by letters and rows that are identified by numbers. The data itself is stored in the intersecting cells, which are identified according to their location within the grid (e.g., the cell at the intersection of Column A and Row 1 is A1). Understanding the grid layout will be necessary if you want to create a dataset using a certain range of cells within a Microsoft Excel file.
To create a dataset that looks like our example, the Microsoft Excel file should look like the one shown in the following image.
In this example, the first row in the Microsoft Excel file contains labels that identify the information that is stored in each column, such as ENTRY, ID, and NAME. Each subsequent row represents a single entry into the building and contains all the information related to that entry. For example, row 2 contains the information for one authorized entry by John Doe, at 7:45 A.M. on January 10, 2010.
Note: If the first row in a Microsoft Excel file contains values that you want to use as column names for the dataset, as shown in the example, when you create the dataset, you should leave the First row contains header definition? check box selected in the Dataset Builder. If the first row in the Microsoft Excel file does not contain values that you want to use as column names, you should clear this check box and allow the columns to be created with default names F1, F2, F3, and so on.
When you import a Microsoft Excel file to create a dataset, the Meridium APM system will import the data and choose a data type for each column based upon the values stored in each column.
Columns that store only numbers will be assigned the Numeric data type.
Columns that store only date or time data will be assigned the Date data type.
Columns that store letters and/or symbols will be assigned the Character data type. The column may also contain numbers, but unless the column is limited to containing only numbers, it will be imported using the Character data type.
Columns that contain only the values True or False will be assigned the Logical data type.
Note: In a Microsoft Excel file, the stored value is not necessarily the same as the displayed value. For example, a cell may store the value 2 but may be formatted to display 2%. In this case, the percent symbol is not actually stored in the cell. In Microsoft Excel, you can view the formatting options that are applied to a given cell to determine how the stored value differs from the displayed value.
If needed, on the Modify Column Descriptions screen of the Dataset Builder, you can modify any data type that is assigned incorrectly during the import process. For example, any column that contains only numbers will be interpreted as a Numeric column. If that column can contain numbers, letters, and symbols, you will want to change the data type to Character so that users can modify the data to include letters and symbols after the dataset has been created.
Additionally, a column that contains only the values 0 (zero) and 1 (one) will be assigned the Numeric data type. You can change the data type to Logical if you want to import the values as logical data.
Note: If you change the data type for a column to a selection that is not appropriate for the data that has been imported, the data for that column may not be imported or may be displayed incorrectly. For example, if you change the data type for a column containing only numbers from Number to Logical, the values will be overwritten by True values.
If you select the Enforce Unique Values? check box on the Modify Column Descriptions screen of the Dataset Builder for a column that has repeated values, only the first row with a unique value will be imported. Subsequent rows containing the same value will not be imported. For instance, if you selected the Enforce Unique Values? check box for the NAME column in the example, only the first row of data for each person would be imported.