Tabular Transformation Command Reference

Connections

Tabular Transformation

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 ")

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 ")

Tabular Transformation Command Reference

Table of Contents

Connections

Commands

Connections

Tabular Transformation

Commands

Add Header

Add row numbers

Advanced Query

Change Delimiter

Clean Unquoted Newlines

Column Filter

Concat Files

Convert CSV to XLSX

Convert JSON to CSV

Convert XLSX to CSV

Convert to Cross-Tab

Copy Column

Extract Value

Filter Rows

Find and Replace

Insert Column

Join Columns

Map Headers

Pivot

Smart Filter Rows

Split File

Split value

Stack files

Transpose

Unpivot