An Excel file is a common format for holding data in rows and columns. The data is held in a spreadsheet, often within a workbook of multiple spreadsheets. Typically, this spreadsheet format is only used for relatively small datasets. This document will explain the options available for importing data from Excel sheets into LityxIQ. Note that this same approach and discussion holds for Google Sheets data as well.
A spreadsheet is a file, so it can be stored and retrieved from a variety of types of file locations. For example, delimited files can be placed on FTP sites, in Google Drive, in an Amazon S3 bucket, or in the LityxIQ File Manager (see https://support.lityxiq.com/374367-Using-the-File-Manager for more information on using the File Manager). Regardless of the location of the file you wish to import, the method and options for importing are the same.
Whether creating a new raw dataset (https://support.lityxiq.com/319229-Create-New-Dataset) or editing an existing one (https://support.lityxiq.com/261835-Define-the-Source-Settings-for-a-Raw-Dataset), the options you will use are on the Data Source tab.
Left Side of Data Source Tab
The left side of the Data Source tab contains settings related to locating the file itself.
Location - As mentioned above, the location of the file can be the LityxIQ Manager or a Data Connection you have setup. Select the location/connection where the file can be found.
Use Filename Template - In this discussion, we will use the setting "Standard Import - No Template". The other options available are described elsewhere, but in any case do not affect the other settings for the delimited text file.
Filename - When you click the Filename box, a file selection dialog will appear similar to that shown below. LityxIQ will make the connection to the chosen location and display the files and folders it finds. Navigate the files and folders to find the one you want to import. See the https://support.lityxiq.com/374367-Using-the-File-Manager document for more help on navigating the files and folders and selecting a file. The document is specific to the LityxIQ File Manager, but navigation is similar regardless of the location.
Compressed File - LityxIQ support import of files that are not compressed, or are compressed in a variety of formats. See the discussion regarding compressed file options in https://support.lityxiq.com/678242-Import-a-Delimited-Text-Filehttps://support.lityxiq.com/678242-Import-a-Delimited-Text-File for more details. Note that spreadsheet files are often not provided in a compressed format, so this option would rarely be used for spreadsheets.
Right Side of Data Source Tab
The right side of the Data Source tab relates to options specific to the formatting and structure or nuances of the spreadsheet file and how it is imported.
File Type - Select Excel/Google Sheets for a spreadsheet document.
Sheet Name/Number - Enter the name of the sheet within the file that you want to import. If you enter an integer number, it will be interpreted as a sheet number. Use this method to specify the sheet you want based on its position in the workbook (1, 2, 3, etc). Alternatively, enter the name of the sheet within the file that you want to import. Leaving this field blank will default to the first sheet in the workbook. Note that you can use the View Worksheet Names button to have LityxIQ evaluate the selected workbook and show you the names of all sheets within.
Header Row Number - Enter the row number of the header row (usually it's in Row 1 which is the default) which has the variable names. Enter 0 if the spreadsheet has no header row, in which case the variable names will be created automatically using the naming convention "Column_n" with "n" being the column number. Do not count any blank lines that may be at the top of the Excel sheet when determining which is the header row.
Convert to Date/Time - Set this checkbox if you want all dates in the Excel/Google sheet converted to date/time values instead of just dates. Note that all relevant fields will get converted to date/time.
Row Limit - Enter the number of rows to import. Use 0 to import all rows, the default. This is a way to test settings on a much larger file by only importing, say, the first 100000 rows before trying the entire file.
Trim Trailing Spaces - With this box checked, spaces at the end of strings that are imported are automatically removed being stored in the LityxIQ dataset.
Preview Window
See https://support.lityxiq.com/428738-Previewing-a-Raw-Dataset-Source.