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 a column contains a mix of data types, its data type is set to String/WString. |
String Values | Narrow strings are returned when possible. |
Number Rounding | The formatting assigned to the cell is ignored. For example, if a cell contains the value 1.27952 and is formatted to show 2 decimal places, the full value of 1.27952 is 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, and 123 are read as: abc, abc2, abc3, 123, 123_2, and 123_3.
Column names formatted as dates: Column names formatted as dates in the original Excel file (for example, 1/1/2014, 2/1/2014, 3/1/2014) generate column names formatted using the default Alteryx date format: 2014-01-01, 2014-02-01, 2014-03-01.
Special characters in sheet and range names: Alteryx now supports all characters supported by Excel for sheet names. If Excel allows a character to be used when naming a sheet, the new driver can read it and does not convert or change any characters.
These characters are not valid for sheet names:
\,/,?,*,[, and].
Formatting in Empty Column Cells
Formatting (for example, borders) applied to empty cells can make a row appear present in Alteryx. This affects column header detection. Use the "Start Data Import on Line" option or ensure consistent formatting and avoid unexpected behavior.
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.
Note
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.
Dates and Excel
Date-Time Precision
While Alteryx now supports date-time precision to sub-seconds, the same high-precision date-time (date-time data with a higher resolution than seconds) is not supported in XLSX. If you attempt to write high-precision date-time data from Alteryx to XLSX, you will receive a Field Conversion Error that indicates that your high-precision date is truncated to seconds, for example:
High precision Time is not supported in XLSX and values are truncated to seconds: field "@1".
There are a couple of options you might consider:
As a potential workaround, you might consider formatting your date-time data as a string before you write to XLSX. This allows Excel formulas to work with the data, though fractional seconds are ignored.
Alternatively, you can use a Select tool to truncate your date-time data and avoid triggering the above warning in Alteryx.
Dates Prior to 1900
Excel does not support dates before 1900. Calculations involving dates from Jan 1, 1900, to Mar 1, 1900, produce incorrect results. For more information on this issue, go to Microsoft documentation.
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. To write an Excel file to a Microsoft SQL Server FileTable, the format-specific option Enable SQL Server FileTable Support must be checked in the Output Data tool.
Output Options for XLSX Write Support
Create New Sheet
If a file exists, a new sheet is created. If there is already a sheet with the same name, an error displays.
If a file doesn't exist, a new file and sheet are created.
Append to Existing Sheet
If a file exists, data is written in the specified sheet. If the specified sheet doesn’t exist, there is an error.
If a file doesn't exist, data is not written.
Overwrite Sheet or Range
If a file exists, the specified sheet is deleted if it exists, and then data is written in a new sheet with the specified name.
If a file doesn't exist, a new file and sheet are created.
Overwrite File (Remove)
If a file exists, the file is deleted, and data is written in a new sheet in a new file.
If a file doesn't exist, a new file and sheet are 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 are 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 are 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 is written beyond the specified range until...
| The data can contain more rows than the specified range. Data is written beyond the specified range until...
| The data can contain more rows than the specified range. Data is written beyond the specified range until...
|