Import Excel

Description

Import specific worksheets from Microsoft Excel files from PlaidCloud Document. Analyze supports the legacy Excel format (XP/2003) as well as the new format (2007/2010/2013). This includes, but is not limited to, the following file types:

• XLS
• XLSX
• XLSB
• XLSM

Binary workbooks (XLSB), legacy XLS, and ODS files import worksheets and cell ranges the same way as XLSX.

Excel imports stream through temporary storage rather than loading the whole workbook into memory, so very large files import without running out of memory. Source data types — decimals, dates, and timestamps — are preserved in the target table instead of passing through text.

Examples

No examples yet…

---

Import Parameters

The file selector in this transform allows you to choose a file stored in a PlaidCloud Document location for import.

You can also choose a directory to import and all files within that directory will be imported as part of the transform run.

Caution

When choosing a directory to import, ensure all files have the same format

Source Account

Choose a PlaidCloud Document account for which you have access. This will provide you with the ability to select a directory or file in the next selection.

Inside a project, the list also offers Project Default at the top — a stand-in for whatever account the project currently has set as its default. A new step preselects Project Default when the project has one; an existing step keeps the account it was already saved with until you change it. A step left on Project Default runs against whichever account is the project’s default at the time — if none is set, the run fails and names the project rather than falling back to another account.

Search Option

The Search option allows for finding all matching files below a specified directory path to import. This can be particularly useful if many files need to be included but they are stored in nested directories or are mixed in with other files within the same directory which you do not want to import.

Note

Using a common naming convention for files of similar type and purpose makes it easier to use a single import step

The search path selected is the starting directory to search under. The search process will look for all files within that directory as well as sub-directories that match the search conditions specified. Ensure the search criteria can be applied to the files within the sub-directories too.

The search can be applied using the following conditions:

• Exact: Match the search text exactly
• Starts With: Match any file that starts with the search text
• Contains: Match any file that contains the search text
• Ends With: Match any file that ends with the search text

Source FilePath

When a specific file or directory of files are required for import, picking the file or directory is a better option than using search.

To select the file or directory, simply use the browse button to pick the path for the Document account selected above.

Variable Substition

For both the search option and specific file/directory option, variables can be used with in the path, search text, and file names.

An example that uses the current_month variable to dynamically point to the correct file:

legal_entity/inputs/{current_month}/ledger_values.csv

Target Table

The target selection for imports is limited to tables only since views do not contain underlying data.

Dynamic Option

The Dynamic option allows specification of a table using text, including variables. This is useful when employing variable driven workflows where table and view references are relative to the variables specified.

An example that uses the current_month variable to dynamically point to target table:

legal_entity/inputs/{current_month}/ledger_values

Note

If the table does not exist it will be created dynamically when the import process runs

Static Option

When a specific table is desired as the target for the import, leave the Dynamic box unchecked and select the target Table.

If the target Table does not exist, select the Create new table button to create the table in the desired location.

Table Explorer is always avaible with any table selection. Click on the Table Explorer button to the right of the table selection and a Table Explorer window will open.

Remove non-ASCII Characters Option

By selecting this option, the import will remove any content that is not ASCII. While PlaidCloud fully supports Unicode (UTF-8), real-world files can contain all sorts of encodings and stray characters that make them challenging to process.

If the content of the file is expected to be ASCII only, checking this box will help ensure the import process runs smoothly.

Caution

If your data contains text from locations throughout the world it may contain non-ASCII names and phrases

Delete Files After Import Option

This option will allow the import process to delete the file from the PlaidCloud Document account after a successful import has completed.

This can be useful if the import files are generated can be recreated from a system of record or there is no reason to retain the raw input files once they have been processed.

Only files that imported are deleted. A file that was skipped or produced a warning during the import stays in the folder.

Skip Re-Import If Source Files Unchanged Option

When this option is on, a workflow run of this step skips the whole import — download, conversion, and table rebuild — if the source files and the step’s settings are unchanged since the last successful run. This avoids repeating the work when a scheduled workflow re-runs against source files that have not changed.

The step compares each source file by its path, size, and last-modified time (plus a content hash where the source provides one). If any file is added, removed, or changed, or the step’s configuration changes, the import runs normally. The option is on by default — untick it to always re-import — and manual, one-off imports always run.

The skip also requires the target table to still hold the rows the last import loaded. If the table has since been emptied or its row count can’t be read, the import runs again instead of skipping and leaving downstream steps without data.

Some sources (for example SFTP) do not provide a content hash, so on those the change signal is the file’s size and last-modified time alone. A file replaced in place with the same size and modified time would then be treated as unchanged and skipped — untick this option on a step whose source can be updated that way.

Header

Since Excel files may or may not contain headers, PlaidCloud provides a way to either use the headers, ignore headers, or use column order to determine the column alignment.

• No Header: The file contains no header. Use the source list in the Data Mapper to determine the column alignment
• Has Header - Use Header and Override Field List: The file has a header. Use the header names specified and ignore the source list in the Data Mapper.
• Has Header - Skip Header and Use Field List Instead: The file has a header but it should be ignored. Use the header names specified by the source list in the Data Mapper.

Leading and trailing spaces and line breaks in header cells are trimmed from the column names.

Row Selection

For input files with extraneous records, you can specify a number of rows to skip before processing the data. This is useful if files contain header blocks that must be skipped before arriving at the tabular data.

Workbook

Workbook Password Variable opens password-protected workbooks. Enter the workflow or project variable that holds the password, such as {xlsx_password}, not the password itself: a step whose password field holds anything but a variable reference is refused when you save it, so the password never appears in the step, its versions or its copies. Steps that held a typed password before this rule had it moved into a new project variable automatically, visible to Architects only, and now reference that variable.

What to Read

Each entry in the import selects a worksheet, a workbook-defined named range, or a specific cell range — the three are peers, and an import can mix them across multiple entries.

• Worksheet: Reads a full sheet, selected by name.
• Named Range: Reads a range the workbook itself defines a name for. Named ranges are listed alongside worksheets in the picker (see Find Sheets in Selected File below), so you select one the same way you’d select a sheet.
• Cell Range: Reads a specific rectangular range that isn’t backed by a worksheet or workbook name. Pick the sheet the range lives on, then enter the range after a $. Three forms are supported:
  • Sheet1$A1:J100 — a bounded range.
  • Sheet1$A1:J — open-ended; reads from A1 down through the last row of columns A to J.
  • Sheet1$A:BJ — whole columns; reads columns A to BJ of the sheet, the form Alteryx writes for an Excel input.

Named ranges are read from XLSX and XLSM workbooks. XLSB, XLS, and ODS workbooks offer worksheets and cell ranges.

Caution

A named range that refers to more than one disjoint area, or a cell range that is out of the sheet’s bounds or inverted (e.g. the end cell above or to the left of the start cell), is rejected with an error rather than imported — so an import never silently reads the wrong cells.

Worksheets to Import

Because workbooks may contain many worksheets with different data, it is possible to select which worksheets should be imported in the current import process. The options are:

• All Worksheets
• Worksheets Matching Search
• Selected Worksheets

Using Worksheet Search

The search functionality for worksheets allows inclusion of worksheets matching the search criteria. The search criteria allows for:

• Starts With: The worksheet name starts with the search text
• Contains: The worksheet name contains the search text
• Ends With: The worksheet name ends with the search text

Find Sheets in Selected File

The find sheets button will open the Excel file and list the worksheets and named ranges available in the table. Mark the checkboxes in the table for the worksheets and/or named ranges to be included in the import. A workbook inside a .zip file works too: its worksheets and named ranges are listed.

Note

When populating the Data Mapper, the first worksheet, named range, or cell range found in the list will be used. Ensure everything included in the import step has a similar format.

Sheet Map

A Sheet Map reads a different set of worksheets from each workbook. Choose a Sheet Map Table with one row per worksheet to read, its Workbook Column holding the workbook’s path or file name, and its Sheet Column holding the worksheet. When a sheet map is set, only the workbooks the table names are read, each for the worksheets it lists, in the table’s row order. A blank worksheet reads the workbook’s first worksheet.

Files Without the Selected Worksheet

When an import reads several files, a file that doesn’t contain the selected worksheet, or that has no worksheet matching your worksheet search, is skipped. The step finishes with a warning that names the skipped file and lists the worksheets it does have, and the other files still import. If every file is skipped, the step still succeeds, the target table is left empty, and a warning explains why. Worksheet names must match exactly, including upper and lower case.

A skipped file stays in the folder, even with Delete Files After Import turned on, and warns again on each run until you remove or fix it. Warnings from a multi-file import appear on the step: up to five are listed, plus a count of the rest.

Note

An empty worksheet in a multi-worksheet import loads zero rows from that worksheet — it does not fail the import.

Table Data Selection

Data Mapper Configuration

The Data Mapper is used to map columns from the source data to the target data table.

Inspection and Populating the Mapper

Using the Inspect Source menu button provides additional ways to map columns from source to target:

• Populate Both Mapping Tables: Propagates all values from the source data table into the target data table. This is done by default.
• Populate Source Mapping Table Only: Maps all values in the source data table only. This is helpful when modifying an existing workflow when source column structure has changed.
• Populate Target Mapping Table Only: Propagates all values into the target data table only.

If the source and target column options aren’t enough, other columns can be added into the target data table in several different ways:

• Propagate All will insert all source columns into the target data table, whether they already existed or not.
• Propagate Selected will insert selected source column(s) only.
• Right click on target side and select Insert Row to insert a row immediately above the currently selected row.
• Right click on target side and select Append Row to insert a row at the bottom (far right) of the target data table.

Danger

Selecting Propagate All will duplicate columns if they already exist in the target list

Deleting Columns

To delete columns from the target data table, select the desired column(s), then right click and select Delete.

Source Columns That Are No Longer Listed

A target row can name a Source Column that the source data table no longer lists, for example after the source was inspected again. The step never removes such a row for you. Instead, the column name is shown in red behind a ⚠ warning sign, and hovering over it says the column is not in the source columns. In a step with several tables, such as a union, each table is checked against its own source columns.

A row with a Constant, an Expression or a Count (including nulls) summary is never flagged, because the step doesn’t read its source column. Nothing is flagged while the source data table lists no columns, for example before the source is first inspected.

When you save a step that still has flagged rows, a Stale Columns Found dialog lists them under the tab or table they are on, and offers three choices:

• Remove All and Save — remove the flagged rows, then save.
• Save Anyway — save the step as it is.
• Cancel — go back to the step without saving.

To keep a row and clear its warning, add the column back to the source data table, or pick a column the source data table lists.

Column Data Type

Every target column carries a Type. A column propagated from the source keeps the source column’s type; a row you add by hand starts as Text, and you set Type to say what it should actually be — a column you intend to sum or sort needs its numeric or date type, because text sorts and sums as text.

A step saved with a column that has no type at all still runs. The type is worked out when the step starts, from the source column that column reads. Where there is nothing to read it from, it is treated as text: an Extract SQL step’s columns come from the query you wrote rather than from a mapped source, and where several sources carry the same column name under types that disagree, text is used rather than guessing one of them — a wrong guess produces a conversion the warehouse can reject, and anything converts to text.

Caution

Multi-Table Join is the exception, deliberately. It refuses to run and names the column instead, which is more useful than a guess in a step joining several tables. Set that column’s Type and re-save.

Setting Type yourself is still how you say what a column should be. Leaving it unset means accepting whatever the source column happens to be.

Changing Column Order

To rearrange columns in the target data table, select the desired column(s). You can use either:

• Bulk Move Arrows: Select the desired move option from the arrows in the upper right
• Context Menu: Right clikc and select Move to Top, Move Up, Move Down, or Move to Bottom.

Reduce Result to Distinct Records Only

To return only distinct options, select the Distinct menu option. This will toggle a set of checkboxes for each column in the source. Simply check any box next to the corresponding column to return only distinct results.

Depending on the situation, you may want to consider use of Summarization instead.

The distinct process retains the first unique record found and discards the rest. You may want to apply a sort on the data if it is important for consistency between runs.

Danger

Selecting all columns to determine distincriveness might make it appear as if it isn’t being applied. Select only the columns you feel define the distictivness of the data.

Aggregation and Grouping

To aggregate results, select the Summarize menu option. This will toggle a set of select boxes for each column in the target data table. Choose an appropriate summarization method for each column.

When Inspect Source fills the columns, a number column starts as Group By rather than Sum if its name reads as an identifier, such as id, customer_id, Order No or CustomerKey, if its table sets it as a key, or if the join uses it as a key.

• Group By
• Sum
• Min
• Max
• First
• Last
• Count
• Count (including nulls)
• Mean
• Standard Deviation
• Sample Standard Deviation
• Population Standard Deviation
• Variance
• Sample Variance
• Population Variance
• Advanced Non-Group_By

Note

When using aggregation, all columns must have a summarization type specified

For advanced data mapper usage such as expressions, cleaning, and constants, please see the Advanced Data Mapper Usage

One Source Column in Several Target Columns

A single source column can feed more than one target column. Map the same source column on two or more target rows, each with its own name, type or expression. For example, read Amount once as a text column and again as a numeric column, or build two text columns from the same cell. A numeric target no longer converts the source before your expression runs, so an expression can clean a value such as 1,500 first.

This works the same way in every file import that uses the Data Mapper other than Import CSV: Import Excel, fixed-width, JSON, Avro, Parquet, DBF, XML and YXDB.

Data Filters

To allow for maximum flexibility, data filters are available on the source data and the target data. For larger data sets, it can be especially beneficial to filter out rows on the source so the remaining operations are performed on a smaller data set.

Select Subset Of Data

This filter type provides a way to filter the inbound source data based on the specified conditions.

Apply Secondary Filter To Result Data

This filter type provides a way to apply a filter to the post-transformed result data based on the specified conditions. The ability to apply a filter on the post-transformed result allows for exclusions based on results of complex calcuations, summarizaitons, or window functions.

Final Data Table Slicing (Limit)

The row slicing capability provides the ability to limit the rows in the result set based on a range and starting point.

Note

For consistency, results that are sliced should have a sort specified

Filter Syntax

The filter syntax utilizes Python SQLAlchemy which is the same syntax as other expressions.

View examples and expression functions in the Expressions area.