Any of the most common Excel® file types can be uploaded in their native formats directly into the application:
Excel Workbook (*.xlsx)
Excel Macro-enabled Workbook (*.xlsm)
Excel 97-2003 Workbook (*.xls)
After the file is initially loaded, the Import Options for Excel spreadsheets will be displayed just on the side of the sample set of the worksheet data. The options allow modification of how the data will be parsed prior to committing the data into the application.
Pay close attention to the data preview panel with the sample data at the bottom of the screen when making changes to Import Options. It will dynamically change as you adjust options and will let you know when the options have been adjusted correctly.
Once the options have been verified, click the “Finish” button in the upper right-hand corner of the Import Options panel. To abort an upload of data, click the “Cancel” button.
By default, the workbook name will be populated in this field. This can be changed to whatever name the user prefers.
The default value for this field is the first worksheet in the workbook. If the data show in the preview panel below is not the correct set, use the drop-down arrow on the right side of this field to select the correct worksheet to load.
Discard row(s) before header
To make Excel spreadsheets more human-readable, users will add a number of header rows to a worksheet containing information like title, author, source, etc., along with blank rows for visual spacing. Because this information will not parse neatly into columns within a dataset, these should be discarded from the upload. This option allows a specified number of rows to be skipped at the very top of the sheet so that only valid tabular data and column headers are parsed into the project.
Parse row(s) as header
By default, only one row is assumed to contain the column headers in a worksheet. In cases where column names span multiple rows, specify the total number of rows to be included here.
Allows the user to specify if the header is separated by a space, or by a custom character. Choosing custom activates a free text field where the user can specify the custom character(s) for header separation.
Discard row(s) after header
Sometimes, worksheets with complex formatting will have rows between the column headers and the actual data to be imported. To eliminate these rows, use this option to specify the number of rows to skip in order to produce a clean set of tabular data for the application to parse.
This option can also be used in conjunction with the “Load row(s)” option to only select data from the middle of a worksheet—skipping at the top and / or bottom of sheet.
The application attempts to determine the character set of the file upon import. In general, this is not an option that is usually adjusted. However, if the characters in the data within the preview panel are not rendering correctly, try to use this setting to ensure letters and numbers can be recognized.
To import less than an entire worksheet, use this option to limit the number of rows loaded into the project. This is often done when just a sample of very large datasets is required. To load a limited number of rows from the middle and / or bottom of the worksheet instead of the top, use this option in conjunction with “Discard row(s) after header”.
Parse cell text into numbers
When an all-numeric series of characters are encountered in a data “cell” (intersection between a single column and row) within the file, this option determines whether the application will store the value as a number (this is the default option) or as a text string (click the “no” option to activate this behavior).
Store blank rows
To retain rows that are entirely blank, ensure that this option is set to “yes.” If it is set to “no” and there are blank rows in the worksheet, they will be removed upon import—which will change the total number of rows that appear in the dataset.
Excel® is a registered trademark of Microsoft Corporation in the United States and/or other countries.