Data Table
Introduction
The DataTable library in Automate provides a set of actions to perform various operations on tabular data structures. These actions enable users to create, modify, and manipulate tables efficiently within Automate workflows.
The purpose of the DataTable library is to streamline the handling of tabular data, allowing users to create new tables, insert rows, find table lengths, remove duplicate rows, and update cell values.
What is DataTable?
The DataTable library offers several actions to work with tabular data structures within Automate. These actions empower users to perform tasks such as creating new tables, inserting rows, finding table lengths, removing duplicates, and updating cell values with ease.
Key Features
Create New Table: Generate an empty table or define a table with specific rows and columns.
Insert Row: Add new rows to an existing table at specified indices.
Find Table Length: Determine the number of rows in a table.
Remove Duplicate Rows: Remove duplicate rows from a table.
Update Cell Values: Modify the values of cells within a table.
How To Use Action
Assign
Description: The Assign action creates a new data table with specified parameters.
Usage:
Empty Table: Create an empty data table with a defined number of rows and columns.
Table: Create a data table with predefined values and headers.
Variable: Perform operations on an existing data table.
Insert Row
Description: The Insert Row action adds new rows to an existing table.
Usage:
Source Data Table: Select the target table to insert rows into.
Insert a Row at Index: Specify where to add the new row:
First: Add data to the first row.
Last: Add data to the last row.
Specific: Add data to a specified index.
Row Definition: Define the row headers for the table.
Length
Description: The Length action determines the number of rows in a table.
Usage:
Source Data: Select the target table to calculate the length.
Assign the Output to Variable: Store the table length value in a variable.
Remove Duplicate Rows
Description: The Remove Duplicate Rows action eliminates duplicate rows from a table.
Usage:
Data Table Name: Choose the table from which to remove duplicates.
Update Cell
Description: The Update Cell action modifies the values of specific cells within a table.
Usage:
Source Data Table: Select the table to update.
Row Index: Specify the row number for the cell to update.
Column Index: Specify the column number for the cell to update.
Value: Enter the new value for the cell.
Add Row Numbers
Description: This action adds row numbers to a DataTable.
Purpose: It aids in tracking and organizing data by assigning sequential numbers to each row.
Usage: After selecting a DataTable variable, specify the starting number and, optionally, a new header name.
Inputs:
Source Data(Required): Select a variable of type data table.
New Header Name: If you want to add a title to the sorting, fill in this field.
Starts From Number: Default value 1, e.g., 3.
Outputs: The DataTable with row numbers added.
Fill Blank Cells
Description: This action fills blank cells in a DataTable with specified values.
Purpose: It ensures consistency in data representation by replacing blank cells with meaningful values.
Usage: Select a DataTable variable and choose filling type. Specify the value to fill blank cells.
Inputs:
Source Data(Required): Select a variable of type data table.
All Columns or Selected Columns.
Filling Type: Value from above, below, left, right, or custom value.
Outputs: The DataTable with blank cells filled.
Filter Rows
Description: This action filters rows in a DataTable based on specified criteria.
Purpose: It extracts subsets of data that meet specific conditions, facilitating data analysis and reporting.
Usage: Select a DataTable variable and specify filtering criteria. Assign the filtered result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Filter with Headers: Yes or No.
Value to Assign(Required): Select a variable of type data table.
Outputs: The filtered DataTable.
Find and Clean
Description: This action finds and cleans specified elements in a DataTable.
Purpose: It standardizes data format by removing unwanted characters or patterns.
Usage: Select a DataTable variable and specify cleaning criteria. Assign the cleaned result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
All Columns or Selected Columns.
Cleaning Type: Clean all spaces, characters, punctuations, numbers, non-numbers, leading spaces, or trailing spaces.
Contains a Header: Yes or No.
Outputs: The cleaned DataTable.
Find and Replace
Description: This action finds and replaces specified values in a DataTable.
Purpose: It facilitates data correction and normalization by replacing old values with new ones.
Usage: Select a DataTable variable and specify find and replace criteria. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
All Columns or Selected Columns.
Contains a Header: Yes or No.
Find(Required): Value to find.
Replace(Required): Value to replace with.
Case Sensitive: Yes or No.
Outputs: The DataTable with values replaced.
Find Max By Group
Description: This action finds the maximum value in specified columns grouped by unique columns.
Purpose: It identifies the highest values within groups of related data.
Usage: Select a DataTable variable and specify columns for finding maximum values. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Enter the column(s) for maximum values.
Enter the name/index of the unique column(s).
Contains a Header: Yes or No.
Value to Assign(Required): Select a variable of type data table.
Outputs: The DataTable with maximum values by group.
Find Min By Group
Description: This action finds the minimum value in specified columns grouped by unique columns.
Purpose: It identifies the lowest values within groups of related data.
Usage: Select a DataTable variable and specify columns for finding minimum values. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Enter the column(s) for minimum values.
Enter the name/index of the unique column(s).
Contains a Header: Yes or No.
Value to Assign(Required): Select a variable of type data table.
Outputs: The DataTable with minimum values by group.
Limit Rows
Description: This action limits the number of rows in a DataTable.
Purpose: It restricts the size of the DataTable to focus on specific data subsets.
Usage: Select a DataTable variable and specify the row limit. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Limit(Required): Number of rows to retain.
Contains a Header: Yes or No.
Value to Assign(Required): Select a variable of type data table.
Outputs: The DataTable with limited rows.
Math By Group
Description: This action performs mathematical operations on columns grouped by specified criteria.
Purpose: It calculates aggregated values for data subsets based on grouping criteria.
Usage: Select a DataTable variable and specify the math type, columns, and grouping criteria. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Math Type: Sum, Average, or Count.
Select All Columns or Sum These Columns.
Group By Columns.
Value to Assign(Required): Select a variable of type data table.
Outputs: The DataTable with aggregated values by group.
Merge Columns
Description: This action merges specified columns in a DataTable into a new column.
Purpose: It combines information from multiple columns into a single column for analysis or presentation.
Usage: Select a DataTable variable and specify columns to merge. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Delimiter: Separator for merged values.
Contains Headers: Yes or No.
New Column Name(Required): Specify the name for the new column.
Select All Columns or Selected Columns.
Outputs: The DataTable with merged columns.
Merge Duplicate Rows
Description: This action merges rows with duplicate values in specified columns within a DataTable.
Purpose: It consolidates duplicate rows to simplify data analysis and processing.
Usage: Select a DataTable variable and specify columns for uniqueness and merging. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Enter the name/index of the unique column(s).
Enter the name/index of the merge column(s).
Delimiter: Separator for merged values.
Contains Header: Yes or No.
Only Merge Unique Values: Yes or No.
Ignore Blanks: Yes or No.
Value to Assign(Required): Select a variable of type data table.
Outputs: The DataTable with merged duplicate rows.
Merge Tables
Description: This action merges two DataTables into a single DataTable.
Purpose: It combines data from multiple sources into a unified dataset.
Usage: Select two DataTable variables and specify merging options. Assign the result to a new variable.
Inputs:
Main Table(Required): Select a variable of type data table.
TABLES(Required): Select another variable of type data table.
Contains Header: Yes or No.
Merge Data By Column Names: Yes or No.
Value to Assign(Required): Select a variable of type data table.
Outputs: The merged DataTable.
Replace with Regex
Description: This action replaces values in a DataTable using regular expressions.
Purpose: It allows for complex pattern-based replacements in the data.
Usage: Select a DataTable variable and specify regex pattern and replacement value. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
All Columns or Selected Columns.
Regex(Required): Regular expression pattern.
Value(Required): Replacement value.
Outputs: The DataTable with values replaced using regex.
Rotate Table
Description: This action rotates a DataTable so that the first column becomes the header row.
Purpose: It transforms the structure of the DataTable for better visualization or analysis.
Usage: Select a DataTable variable. This action directly modifies the structure of the DataTable.
Inputs:
Source Data(Required): Select a variable of type data table.
Outputs: The rotated DataTable.
Sort Rows
Description: This action sorts rows in a DataTable based on specified criteria.
Purpose: It arranges data in a specific order for easier interpretation and analysis.
Usage: Select a DataTable variable and specify sorting options. Assign the sorted result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Ascending or Descending: Order of sorting.
Column Index or Column Name: Choose one.
Column(Required): Name or index of the column to sort by.
Sort with Header: Yes or No.
Outputs: The sorted DataTable.
Split Columns
Description: This action splits a column in a DataTable into multiple columns based on a specified delimiter.
Purpose: It separates combined data in a single column into distinct categories for analysis.
Usage: Select a DataTable variable and specify the column to split and the delimiter. Assign the result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
Column Index or Column Name: Choose one.
Column(Required): Name or index of the column to split.
Delimiter(Required): Separator for splitting.
Contains Header: Yes or No.
Outputs: The DataTable with split columns.
Text Formatting
Description: This action formats specified columns in a DataTable based on specified criteria.
Purpose: It standardizes the format of data for consistency and clarity.
Usage: Select a DataTable variable and specify formatting options. Assign the formatted result to a new variable.
Inputs:
Source Data(Required): Select a variable of type data table.
All Columns or Selected Column: Choose one.
Format Type: Capitalized, Lower Case, or Upper Case.
Contains Header: Yes or No.
Outputs: The DataTable with formatted columns.
Export to CSV
Description and Purpose: This action aims to export a data table as a CSV file to the Mate Database or a specified local environment. This provides flexibility in exporting operations for data table usage.
Usage: To use this action, the user must first define the Source Data. This involves creating a variable of type "data table" in the variable section or by clicking on the plus button to create the data table directly.
Next, the user must define the separator for the CSV file. Autom Mate offers two options: a comma or a semicolon.
Users then need to specify the folder path within the Mate Drive for storing the CSV file. Alternatively, if exporting to a local environment, the Autom Mate server must be installed in that environment. Users can specify the folder location directly or define the local environment as a string-type variable and assign it accordingly (e.g., C:\Autommate\test folder).
Another essential configuration involves defining the exported file's name. Autom Mate allows users to make the file name unique by appending a random number (e.g., filename_123.csv) or to overwrite an existing file with the same name.
Lastly, the user has the option to assign the file path to a variable, which must be of type string.
Inputs:
Source Data: Define a variable of type "data table" in the variable section or create a new data table.
Separator: Choose between a comma or a semicolon.
Folder Path: Specify the folder path in the Mate Drive or the local environment (e.g., C:\Autommate\test folder).
File Name: Define the exported file name with options to make it unique or overwrite existing files.
File Path Variable (Optional): Assign the file path to a variable of type string.
Action Output: The successful export of the data table as a CSV file to the specified location, either within the Mate Database or the local environment.
Additional Tips
Last updated
