Skip to main content

Microsoft Excel - Alteryx Driver

Alteryx has built-in native read and write drivers for Microsoft Excel. No driver installation or other system configuration is required to read and write .xlsx sheets in Alteryx Designer.

Configuration

The File format options in the Output data tool available for Microsoft Excel (.xlsx) files via the Alteryx driver provide the following functionality:

  • Create new sheet

  • Overwrite file (remove)

  • Overwrite sheet (drop)

  • Append to existing sheet

Length and Limits

The Alteryx driver fully supports the documented Excel limits for rows and columns when reading and writing .xlsx files.

Maximum number of rows (limit specified by Excel)

1,048,575

Maximum number of columns (limit specified by Excel)

16,384

Column name length and field name limits

256 recommended for compatibility with other formats

Exceeding record limits (1,048,575 rows and 16,384 columns)

Exceeding column limits results in an error and exceeding row limits results in a truncated file

Exceeding file size limit (4,294,967,295 bytes, ~4GB)

Displays the error message: “The data being written to this sheet is too large. Aborting process.”

Data Types

The Alteryx driver analyzes the sheet data to determine a data type and field name for each column.

Parsing Data Types

If there is a mix of data types in a column the data type for the column will be set to String/WString.

String Values

Narrow strings are returned when possible.

Numbers Rounding

The formatting assigned to the cell will be ignored. For example, if a cell contains the value 1.27952 and the cell is formatted to show 2 decimal places the full value of 1.27952 will be returned instead of 1.28.

Date, Time, and DateTime

All Dates and Times are displayed in Alteryx format. A Date data type is formatted based on an analysis of the data in the column.

  1. If every value in the column is date only (e.g., 2014/11/04 or May 4th with no time component), then the data type will be set to Date as YYYY-MM-DD.

  2. If every value in the column contains only time information with no date component, then the data type will be set to Time as HH:MM:SS.

  3. If data values in the column consist of both data and time components, then the data type will be set to Datetime as YYYY-MM-DD HH:MM:SS.

  4. Custom date formats are ignored. Any custom formatting applied to a date in Excel is ignored - only the actual data values are analyzed by Alteryx. For example, if a cell contains a full date and time value but is formatted to display only the date or only the time, Alteryx will ignore the formatting and instead evaluate the raw data value contained by the cell. This may lead to unexpected results if custom formatting has been used to show or hide parts of the date/time value.

Field Names

Duplicate field/column names

The following column names in the original Excel file:

abc, abc, abc, 123, 123, 123

will be read as follows:

abc, abc2, abc3, 123, 123_2, 123_3

Warning

This change could impact workflows created in versions previous to 9.5.

Column names formatted as dates

Column names formatted as dates in the original Excel file:

1/1/2014, 2/1/2014, 3/1/2014

will generate column names formatted using the default Alteryx date format as follows:

2014-01-01, 2014-02-01, 2014-03-01

Warning

This change could impact workflows created in versions previous to 9.5.

Special characters in sheet and range names

All characters supported by Excel are now supported by Alteryx with respect to sheet names. If Excel allows the character to be used when naming a sheet, the new driver is able to read it and no characters will be converted or changed. The following characters not valid for sheet names: \ / ? * [ and ].

Warning

This change could impact workflows created in versions previous to 9.5. If you have Inputs pointing to xlsx files that were created by an Output tool that handled special characters, the sheet names will be different.

Formula Errors

When a formula in a sheet fails with an error, Alteryx will return a NULL value for fields that are non-string types, and the actual error string for fields that have a string type.

Ranges

The Alteryx driver for Microsoft Excel provides:

  • Read support for named and explicit ranges.

  • Write support for explicit ranges. Supports Overwrite for named ranges but cannot create new.

Caution

Alteryx supports the retention of cell formatting during overwriting, excluding templates.

Data Output Sheet Names

The Alteryx driver for Microsoft Excel provides read support for a list of sheet names.

FileTables Support

The Alteryx driver supports reading and writing an Excel file located in a Microsoft SQL Server FileTable by browsing to the UNC path for the FileTable and mapping in the file like a file stored on a network drive. The format-specific option Enable SQL Server FileTable Support must be checked in the Output tool to write an Excel file to a Microsoft SQL Server FileTable.

Output Options for XLSX Write Support

Create sheet

Append sheet

Overwrite sheet

Overwrite file

If there is an existing file: A new sheet is created. If there is already a sheet with the same name an error will display.

If there is an existing file: Data is written in the specified sheet. If the specified sheet doesn’t exist there will be an error.

If there is an existing file: The specified sheet is deleted if it exists and then data is written in a new sheet with the specified name

If there is an existing file: The existing file is deleted and data is written in a new sheet in a new file.

If there is no existing file: A new file and sheet is created.

If there is no existing file: Data will not be written.

If there is no existing file: A new file and sheet is created.

If there is no existing file: A new file and sheet is created.

Additional Information for XLSX Write Support with Ranges

Create sheet/Overwrite file

Append sheet

Overwrite sheet

Explicit ranges can be used.

A named range cannot be used since a named range is tied to a sheet and a sheet does not exist.

Both explicit ranges and named ranges can be used.

A named range must have been previously defined for the sheet.

Both explicit ranges and named ranges can be used.

A named range must have been previously defined for the sheet.

The data cannot contain more columns than the specified range but can contain fewer columns.

If there is not enough incoming data to fill the range, columns will be filled with nulls.

The data cannot contain more columns than the specified range but can contain fewer columns.

If there are fewer incoming columns in the range the unmapped columns will be filled with nulls.

The data cannot contain more columns than the specified range but can contain fewer columns.

If there is not enough incoming data to fill the range, columns will be filled with nulls.

Data is written starting at the top left cell in the first row of the specified range.

Data is appended starting on the first available row after the original data. When appending, no existing data will be overwritten.

Data cannot be written to a sheet that contains merged cells.

Column names should be included in the range. If the first row in the range is empty, the first row in the range that contains data will be used as column names. If the column names don’t match the incoming data, they must be manually mapped to the output names using the Custom Append Mapper in the Output Data tool.

Data is written starting at the top left cell in the first row of the specified range.

Data cannot be written to a sheet that contains merged cells

The data can contain more rows than the specified range.

Data will be written beyond the specified range until:

  • All data is written

  • The maximum number of rows (1,048,575) is exceeded

The data can contain more rows than the specified range.

Data will be written beyond the specified range until:

  • All data is written

  • The maximum number of rows (1,048,575) is exceeded

The data can contain more rows than the specified range.

Data will be written beyond the specified range until:

  • All data is written

  • The maximum number of rows (1,048,575) is exceeded

  • Existing data is encountered