Table of Contents
Connections
Commands
Connections
Tabular Transformation
A basic connection for tabular transformations. This connection requires no credentials.
Inputs
None
Commands
Add Header
Add a header row to a CSV file.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The CSV file to add the headers to | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) | |
Header Row | TextField | The entire contents of the header row. Separate each header by a delimiter. (i.e. Column1,Column2,Column3) | |
Header Delimiter | TextField | The delimiter you used for the headers in the Header Row input. (i.e. ,) |
Outputs
Output | Output Type | Notes |
CSV with headers | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
Add row numbers
Adds row numbers to the DSV in the first column
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Add Row Numbers Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Advanced Query
Execute a SQL query on a CSV file, or multiple CSV files. You can also join other files that you attach to this command.
Inputs
Input | Type | Detail | Notes |
Tables | GroupField | Add all of the files that will be used in the query, as well as their table name. | |
Query | TextField | The SQL query to execute. INSERT, UPDATE, CREATE are not supported. | |
Input Delimiter | DropdownField | The delimiter of the input CSV file, as well as the join files. | |
Output Delimiter | DropdownField | The delimiter to use for the result of the query. | |
Preview results | BooleanField | If this is checked a preview of the results will be printed. |
Outputs
Output | Output Type | Notes |
Result | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Change Delimiter
Change the delimiter of a CSV file. NOTE: You should always use a single character for a delimiter, preferably a comma or a tab character to comply with the RFC specification.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The CSV file to add the headers to | |
Input Delimiter | TextField | The delimiter that the Input File currently uses. (Use '\t' for a tab character) | |
Output Delimiter | TextField | The delimiter the file will use after the transformation. (Use '\t' for a tab character) | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
CSV Result | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
Clean Unquoted Newlines
Attempts to clean a CSV file that is RFC compliant except for unquoted new line characters. Any other non-compliant issues with the data set will cause this to fail.
Inputs
Input | Type | Detail | Notes |
Preview Result | BooleanField | Whether or not to preview the result. | |
Input File | TextField | The input file to convert to a cross-tab report. | |
File Delimiter | DropdownField | The delimiter for each column in the CSV file. This is NOT the delimiter for each field on the X and Y axis of the report. | |
Use Lazy Quotes | BooleanField | If checked, a quote may appear in an unquoted field and a non-doubled quote may appear in a quoted field. | |
Append Trailing Text | BooleanField | If checked, any lines in the file that contain text with no delimiters present (single column lines) will be appended to the last value in the last column of the previous record. |
Outputs
Output | Output Type | Notes |
Cleaned Newlines Output | File | |
Line Count | Integer | This is the total number of records, including the header, in the resulting file. |
Exit Codes
Code | Type | Detail |
0 | Success | The command completed successfully |
1 | Error | Failed to create the cross-tab report |
Column Filter
Filter the CSV columns with headers that match the specified pattern.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Pattern Type | DropdownField | The type of pattern. Index filters by column index, exact is a comma separated list of exact values, and regex is a regular expression. | |
Pattern | TextField | The pattern to match columns with. (If using Index matching, you can apply the spread operator i.e. 1:5,7:8,10:15) | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) | |
Inverse | BooleanField | If set to true, the columns specified will be kept and all other columns will be removed. |
Outputs
Output | Output Type | Notes |
Column Filter Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Concat Files
Merges one or more tabular data sets into a single data set. The input sources are merged horizontally.
Inputs
Input | Type | Detail | Notes |
Source Files | ArrayField | The files to concatenate. | |
Preview Result | BooleanField | Whether or not to preview the result. | |
File Delimiter | DropdownField | The delimiter for each column in the CSV file. This is NOT the delimiter for each field on the X and Y axis of the report. |
Outputs
Output | Output Type | Notes |
Merged CSV | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | The command completed successfully |
1 | Error | Failed to create the cross-tab report |
Convert CSV to XLSX
Converts a CSV file to an Excel Workbook (XLSX)
Inputs
Input | Type | Detail | Notes |
Input file | FileField | CSV file to be converted | |
Delimiter | DropdownField | CSV's delimiter | |
Sheet Name | TextField | Name of the sheet to be created in the Excel workbook | |
Output File | TextField | OPTIONAL: Path to where the file should be stored (leave blank if using as an output) |
Outputs
Output | Output Type | Notes |
Output XLSX | File |
Exit Codes
Code | Type | Detail |
0 | Success | Command completed successfully |
1 | Error | General failure |
Convert JSON to CSV
Converts a JSON file to a CSV
Inputs
Input | Type | Detail | Notes |
JSON Data | FileField | JSON data to convert to CSV | |
Output file | TextField | Destination for the newly created CSV file. If using the file as an output, you may leave this field blank | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Output CSV | File |
Exit Codes
Code | Type | Detail |
0 | Success | Command completed successfully |
1 | Error | General failure |
Convert XLSX to CSV
Converts a single sheet in an Excel workbook to CSV data
Inputs
Input | Type | Detail | Notes |
Input file | FileField | Excel file to be converted | |
Delimiter | DropdownField | CSV's delimiter | |
Sheet Name | TextField | Name of the sheet in the Excel workbook to be converted to a CSV. Only provide a value here if you have not specified a Sheet Index. | This value is optional if you specify a Sheet Index |
Sheet Index | NumberField | Index of the sheet in the Excel workbook to be converted to a CSV. The first sheet in the workbook is specified with an index value of 1, and the last with -1. Only provide a value here if you have not specified a Sheet Name. | This value is optional if you specify a Sheet Name |
Start Cell | TextField | The cell in the sheet that the CSV data starts at. This will be the cell in both the first row and the first column of data. | |
End Column | TextField | Specify and end column letter using A1 notation. All data after this column letter will be excluded. You should not include the row number. (Examples: A, B, C, D) | |
End Row | NumberField | Specify and end row using A1 notation. All data after this row number will be excluded. If you specify 0, this will be ignored. You should not include the column letter. (Examples: 10, 200, 3000) | |
Preview results | BooleanField | Preview the results of the created CSV data |
Outputs
Output | Output Type | Notes |
Output CSV | File |
Exit Codes
Code | Type | Detail |
0 | Success | Command completed successfully |
1 | Error | General failure |
Convert to Cross-Tab
Converts a tabular data set to a cross tab report. It is assumed that the first column is a delimited set of values to be spread horizontally and the first row is a delimited set of values to be spread vertically.
Inputs
Input | Type | Detail | Notes |
Preview Result | BooleanField | Whether or not to preview the result. | |
Input File | TextField | The input file to convert to a cross-tab report. | |
File Delimiter | DropdownField | The delimiter for each column in the CSV file. This is NOT the delimiter for each field on the X and Y axis of the report. | |
Field Delimiter | TextField | The characters that separate each field to be split into the cross-tab format. | |
Omit the Key (A1 cell) | BooleanField | Whether or not to omit the key. (e.g. If cell A1 contains (A,B) cells A1 and A2 of the cross-tab report will be blank if this is checked, otherwise they would contain A and B) |
Outputs
Output | Output Type | Notes |
Cross Tab Report | File | |
Line Count | Integer | This is the total number of lines in the output file including all header rows. |
Exit Codes
Code | Type | Detail |
0 | Success | The command completed successfully |
1 | Error | Failed to create the cross-tab report |
Copy Column
Copy column from CSV
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Column name | TextField | Name of the column to copy | |
New column name | TextField | Name of the resulting column that was copied | |
Insert index | NumberField | The column index at which to insert the copied column | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Copy column output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Extract Value
Extract a value from a CSV file by the row index and column index. If column index is not specified or 0, the entire row will be returned as a text value.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Row Index | NumberField | The row to extract the value from (This value is based on the first line in the file being row 1) | |
Column Index | NumberField | The column to extract the value from (This value is based on the first line in the file being row 1). Leave this empty to extract the entire row. |
Outputs
Output | Output Type | Notes |
Row | Json | |
Value | String |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
Filter Rows
Filter rows of the CSV by a regex or exact match of one or more columns in the row.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Find pattern | TextField | The pattern to use to find matches. | |
Match pattern type | DropdownField | The pattern type of the pattern, either Regex or Exact. | |
Case Insensitive | BooleanField | Whether or not the match pattern should ignore the case of the text. | |
Search Columns | TextField | Comma seperated list of column indexes to limit the search to | |
Inverse | BooleanField | Keep all rows that match instead of removing them | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Filter Row Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Find and Replace
Find and replace column values in the data.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Find pattern | TextField | The pattern to use to find matches. | |
Match pattern type | DropdownField | The pattern type of the pattern, either Regex or Exact. | |
Replacement value | TextField | The replacement text that replaces text found with the pattern. | |
Case Insensitive | BooleanField | Whether or not the match pattern should ignore the case of the text. | |
Replace matches only | BooleanField | For a Regex search, if this. is set to true only the matching text will be replaced with the replacement value. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) | |
Columns | TextField | A comma separated list of columns to scope the find and replace to. 0 is the first column. e.g. 0,1,2,3 would limit the find and replace to the first 4 columns. |
Outputs
Output | Output Type | Notes |
Find & Replace Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Insert Column
Insert a column into a CSV.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Header text | TextField | The name of the new column header. | |
Data value | TextField | The text to insert into the new column value. | |
Insert index | NumberField | The column index at which to insert the new column. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Insert Column Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Join Columns
Join multiple columns of the CSV and optionally discard the used columns.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Joined column index | NumberField | The column number index for the new column (0 is the first column) | |
Match pattern type | DropdownField | The type of pattern. Index filters by column location, exact is a comma separated list, and regex is a regular expression. | |
Match pattern | TextField | The pattern or index to use to find matching columns to join. | |
Joined column header | TextField | The name of the new column created from the join. | |
Join text | TextField | The text that joins the values together in the new column. | |
Discard | BooleanField | Discard the columns joined to create the new column. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Join Columns Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Map Headers
Takes a list of headers, and a list of headers to replace them with. Separate headers with commas and order matters.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Input Headers | ArrayField | A list of headers to replace with new values (order should match the Output Headers) | |
Output Headers | ArrayField | A list of headers to replace the Input Headers (order should match the Input Headers) | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) | |
Use Indexes | BooleanField | When set to true, denotes that the input headers are numeric indexes |
Outputs
Output | Output Type | Notes |
Map Headers Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Pivot
Creates a pivot table from a tabular dataset and represents it in a tabular format.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The tabular file to pivot | |
Delimiter | DropdownField | The delimiter of the Input File. | |
Aggregation | DropdownField | The aggregation to group the data values by. | |
Values to Aggregate | TextField | Column containing the values to aggregate | |
Pivot Columns | ArrayField | The columns containing values that will become headers | |
Column Delimiter | TextField | When more than one column is specified, the values will be separated by this delimiter in the header | |
Pivot Rows | ArrayField | The headers to keep in the file. Cannot be any of the Pivot Columns or the Data column. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Pivoted Result | File |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Reorder Columns
Reorder the Columns in a File
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The tabuler file to reorder | |
Delimiter | DropdownField | The delimiter of the input file. | |
Column Orders | ArrayField | Array of column ranges or individual columns, in the order they should appear in the output. Columns can be specified by index (starting at 1) or by name. For example, 4:6,ColA:ColC,7,ColH. Any unspecified columns will be placed at the end, in the order of appearance, after the specified columns. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Transformed File | File | |
Transformed Rows | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
Smart Filter Rows
Filter rows of the CSV by a regex or exact match of one or more columns in the row.
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Inverse | BooleanField | Keep all rows that match instead of removing them. | |
Filters | GroupField | The smart filters to apply to the dataset. | |
Preview Result | BooleanField | If checked a preview of the result of the filter will show in the command output. |
Outputs
Output | Output Type | Notes |
Smart Filter Row Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Split File
Splits a file into multiple files based on a record count
Inputs
Input | Type | Detail | Notes |
Input File | TextField | The file to be split into multiple files. | |
File Delimiter | DropdownField | The delimiter for each column in the CSV file. | |
Prepend Header | BooleanField | If selected, the header from the input file will be included in each of the file chunks produced | |
Records per file | NumberField | The maximum number of records to produce in each file chunk. |
Outputs
Output | Output Type | Notes |
Split File Chunks | File | |
Number of Chunks | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | The command completed successfully |
1 | Error | Failed to create the cross-tab report |
Split value
Takes the specified column and value delimiter, and splits the column into multiple columns
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The DSV file to transform | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
New headers | ArrayField | The list of new headers to create from the split value, in order | |
Column name | TextField | The name of the column header that you want to split. | |
Value delimiter | TextField | The delimiter to split the value on. | |
Discard column | BooleanField | Check this if you want to discard the column you are splitting | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Split Values Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Stack files
Stack Files takes a list of comma separated CSV files, and stacks the values of each on top of each other in the order they are specified. The header row from the first file will be the one used in the new file.
Inputs
Input | Type | Detail | Notes |
Files | GroupField | The files to stack. | |
Output file | TextField | The resulting file of the transformation. | |
Delimiter | DropdownField | The delimiter of the input DSV file. | |
Input files | TextField | A comma separated list of files to stack. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Stack Files Output | File | |
Record Count | Integer |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |
Transpose
Transposes a CSV file such that the rows become columns and vice versa.
Inputs
Input | Type | Detail | Notes |
Preview Result | BooleanField | Whether or not to preview the result. | |
Input File | TextField | The input file to transpose. | |
File Delimiter | DropdownField | The delimiter for each column in the CSV file | |
Chunk Size | NumberField | The maximum size in mb of each working file for processing |
Outputs
Output | Output Type | Notes |
Transposed CSV | File | |
Record Count | Integer | This is the total number of lines in the output file not including the header row |
Exit Codes
Code | Type | Detail |
0 | Success | The command completed successfully |
1 | Error | Failed to transpose the csv |
Unpivot
Unpivot carries out almost the reverse operation of Pivot, by rotating columns into rows
Inputs
Input | Type | Detail | Notes |
Input file | FileField | The tabular file to unpivot. | |
Delimiter | DropdownField | The delimiter of the Input File. | |
Aggregation | DropdownField | The aggregation to group the data values by. | |
New Column Label | TextField | Label to use for the newly created column of rotated column labels. | |
Data Column Label | TextField | Label to use for the column containing the data values. | |
Data Headers | ArrayField | The header names which will rotated into a column in the tabular dataset. | |
Starting Pivot Column Name | TextField | Optional. The starting column which contains data values to pivot. Only used if Data Headers are not provided. | |
Ending Pivot Column Name | TextField | Optional. The last column which contains values to pivot. Only used if Data Headers are not provided. | |
Starting Pivot Column Index | TextField | Optional. The zero-based index of the starting column which contains values to pivot. Only used if Data Headers and Starting Pivot Column Name are not provided. | |
Ending Pivot Column Index | TextField | Optional. The zero-based index of the last column which contains values to pivot. Only used if Data Headers and Ending Pivot Column Name are not provided. | |
Preview results | BooleanField | Preview the results of the transformation (First 10 lines and header) |
Outputs
Output | Output Type | Notes |
Pivoted Result | File |
Exit Codes
Code | Type | Detail |
0 | Success | Indicates the task completed successfully. |
1 | Error | Invalid arguments. |
2 | Error | General failure error. |
14 | Error | Invalid output file location. |
15 | Error | The escape character used in the input file (usually a ") |