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.
|
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:
| The data can contain more rows than the specified range. Data will be written beyond the specified range until:
| The data can contain more rows than the specified range. Data will be written beyond the specified range until:
|