IMPORT EXCEL SPREADSHEET

Category: Import / File

Description

This action loads data from an Excel file. Supported formats:

.xlsx (Excel 2007 or later)
.xls (Excel 95 - Excel 2003 )

Action settings

Setting	Description
Load file^*	Fully qualified file name of the Excel workbook (includes relative or absolute path).
Source	The source of the data within the workbook. Options include: Worksheet names, Range names, Worksheet positions, and Worksheet/range defined by parameter. See the table below for option settings.
Select sheet name(s)	Once the Excel file has been selected, a list of worksheets within the file will display, allowing you to select which worksheets to import.
Add column with sheet name	Check this box to add a column to the dataset that lists the name of the worksheet the data was imported from.
Skip first lines^*	The number of rows to skip before reading in your data. If your data does not start on the first row (i.e., leading rows with metadata, blank rows), use this setting to skip these rows.
Columns don't have headers	Check this if your dataset does not include a row with header information
Load all columns	This option imports all columns in the spreadsheet.
Load only selected columns	This option allows you to select which columns to import. A list of columns found in the spreadsheet will be presented for you to select.

^* Setting can be specified using a parameter.

Source settings

Setting	Description
Worksheet names settings
Select sheet name(s)	Select from the list which sheets to import. Multiple sheets will be appended ("stacked") to each other.
Range name settings
Select range name(s)	Select which named ranges within the Excel file to import. Multiple ranges will be appended ("stacked") to each other.
Worksheet positions settings
Select sheet position(s)	Select which sheets to import based on their position within the Excel file (1st, 2nd, etc.). Multiple sheets will be appended ("stacked") to each other.
Worksheet/range defined by parameter settings
Parameter	Select or create the parameter containing a reference to either a sheet name or range name within the Excel file. Note: sheet names are searched for before range names, in cases where sheets and ranges have been given the same names.

Advanced options

Setting	Description
Do not automatically offset imported range	By default, EasyMorph will align the first column and first row of imported data with column 1 and row 1 in the dataset. To prevent this, and to preserve the actual positioning of data within the source file, check this option.
When a floating-point number can't be converted to a fixed point number:	Options: Produce error (default), Convert the number to text.

Remarks

This action can import multiple files. See Importing Multiple Files for more information.

Examples

Example 1: You're importing a data range spanning cells C5 to E10:

To start at column 1, row 1 in the imported dataset…

In "Advanced settings", Do not automatically offset imported range (unchecked)

If you want it to preserve the range offset in the imported dataset (start the imported dataset in column 3, row 5)…

In "Advanced settings", Do not automatically offset imported range (checked)

Example 2: You're importing data from an Excel spreadsheet with the following properties, and you want the data to start at column 1, row 1 in the imported dataset.

3 rows of metadata (in rows 1-3)
The data starts at column C
There are no headers

Use the following settings:

Skip the first 3 lines
Columns don't have headers (checked)
in "Advanced settings", Do not automatically offset imported range (unchecked)

Community examples

SUMIFS formula in EasyMorph (Project; Module: Main; Group: Tab 1; Table: Book1.xlsx; Action position: 1)
Data cleaning for data spread over multiple rows and columns (Project; Module: Main; Group: Tab 1; Table: separateddata test.xlsx; Action position: 1)
Product-Of-Rows-In-A-Column (Project; Module: Main; Group: Tab 1; Table: Source data; Action position: 1)
Split Excel spreadsheets into multiple spreadsheets (Project; Module: Main; Group: Tab 1; Table: Spreadsheet A.xlsx;
Action position: 1)
“Merge another table” action - merge all columns (independently of the column name) (Project; Module: Merge; Group: Merge;
Table: Height; Action position: 1)

Shortcuts

Fast ways to create the action:

Press the "Add data" menu button. Pick "Import file" or "Import multiple files".
Drag an Excel file into the application window. Supported file extensions: .xls, .xlsx
Drag a folder with Excel files into the application window.

EasyMorph Help

Table of Contents