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

Did this answer your question?