Help Desk

Submit a ticket My Tickets
Welcome
Login

Tabular Transformation Command Reference

This BizApp assists with transforming tabular data (i.e. CSV, DSV). Using this plugin you can transform flat files, as well as run other various operations on them

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
Input file FileField JSON file 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 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 ")

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 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.
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 ")
B
Brian is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.