Overview

Whether from an Excel sheet or extracted from a document, we deal with tabular data a lot in automation. This guide walks through how to create and manipulate the rows, columns, and values of a table.

If you are dealing with an Excel file that is more than just a table, for example, an Excel sheet that has multiple tables in one sheet or disparate values and tables in one sheet, follow this guide on how to extract and work with workbooks and sheets!

Once you have a table of data you want to work with (could have come from a complicated Excel file), this guide is for you!

Example Table

We'll use this table called the pet table as a reference for the following examples:

Accessing Existing Tabular Data

First we need access to a table to perform commands on it. Tables can come from a few places in your automation:

• Extracted from a Document
• Read in from an Excel/CSV file
• Pulled from a Database Connection

Extracting Tables From A Document

We have a whole guide around how to extract data from documents! Check it out for more in depth instructions and examples!

For example sake though, here's a simple example of accessing a table from a document.

get the document's first table as the items table

Now you can perform table operations on the table extracted from the document!

Reading in a Table from a CSV or Excel File

If you have a CSV or Excel file you want to use in your automation, you can read it in using as a table.

For example, if you want to read in an Excel sheet you can write:

get the file as a table

This line will prompt you if in the Playground to upload and select the Excel sheet you want to work with!

Keep in mind this works well for simple Excel sheets that are comprised of just column headers and rows. If you need to work with a table from a more complicated Excel sheet, please check out our guide for working with Excel specifically!

Creating Tables

Defining Table Headers

Let's say you are processing multiple documents and want to write the extracted fields to rows in a table. Kognitos allows you to easily create tables, starting by defining the column headers:

Create a table where
	the headers are "{a column name}", "{a column name}", and "{a column name}"

Populating Table Rows

Once you have defined your table headers, time to add in values for each row! To add a row, we need to define the values for each column in the row. To add values to the row, you'll specify which Column you want to add a value to.

It will look something like this depending on your use case:

create a table where
	the headers are "VIN" and "Agency"

get the above table as the blank table

add a row to the blank table
	the row's VIN is "123"
 	the row's Agency is "ABC"

Setting Default Column Values

You can set a default value for all the rows of a column like this:

add "interests" as a column to the table where
  the default value is "singing"

Applying One Value to All Rows in a Column

If you want to set the same value for all the rows in a column, this is the syntax:

update the table's column whose name is "animals" where
  the complete column values are "birds"

Creating a Table from Another Table

Let's say you want to make a sub table using only certain columns from one table, you can write:

create a secondary table from the table where
	the included columns are "COLUMN NAME 1", "COLUMN NAME 2", "COLUMN NAME 3"

You can change out the word secondary for a variable name of your choice, but once you run the above you now have a new table that you can refer to by the secondary table.

For example if I ran:

create a secondary table from the pet table where
	the included columns are "Pet Name" and "Pet Age"

This is the table I am returned:

Using Values From Tables

During the course of your automation, you may need to pull specific rows and columns or specific values from the table.

Get the Rows That Contain A Specific Column Value

Let's say you have this table:

And you want to get ONLY the rows that contain a customer that is 24 years old. You would write:

  Get the rows whose "Customer Age" is "24"

This would return the following:

Modify/Edit Tables

We'll use this table as a reference for the following examples:

Column Operations

Renaming Table Columns

Rename the table's "Pet Owner" column to "Owner"

Getting All the Columns From a Table

get the columns of the table 

Kognitos will return the names of the columns in the form of a list for you to use:

Adding Column/s within the Table

add "col1" as a column to the table

add "col2" as a column to the table where
  the column values are "1", "2", "3"

add a column to the table where
  the column name is "col3"
  the column values are "1", "2", "3"

Deleting Column From Table

delete the "Pet Owner" column from the table

Getting Values From a Column

First get the column of your choice, then get the values:

get the table's last column
get the column's values

When Kognitos returns values from a column, you can work with the data like a list of fields:

Getting Columns by Numeric Position

You can query different columns by its position:

get the table's first column
get the table's second column
get the table's third column
get the table's last column
etc....

Getting Specific Column's Values By Name

get the table's "Pet Name"

Rearrange columns

By default a table's columns are arranged alphabetically when extracted by Kognitos. This can be rearranged as per the user's necessity for the automation.

rearrange the table's columns where  
  the column headers are "City", "Address", "Number" and "Name"

Row Operations

Getting Number of Rows From a Table

get the first 10 rows of the table
get the first row of the table
get the last row of the table
get the third row of the table

Getting Specific Rows Based on Column Value

get the table's rows whose Pet Age contains "4"

This will return the following rows:

Setting Row Value To Something New

Once isolating some table rows, you can set all of the row's to a specific column value:

get the main table's rows whose Pet Age contains "4"
get the above as the cat rows

process each row as follows
  Set the row's "Pet Age" to "20"

Removing Specific Rows

Let's say based off a column value you want to remove some specific rows, you could write the following:

get the table's rows whose "COLUMN NAME" is empty
delete the above rows

Sorting The Table

Here's how you can sort the table

Sort the table by <Name of column>

For example, let's sort the rows by the Pet Name:

Sort the table by Pet Name

Add an Empty Row to Table

add an empty row in the table

Merging Two Tables Into a New Table

When we want to create a new table using existing data, the key word to remember is "merged table".

create a merged table with
  the tables are the above tables  
  the excluded columns are \"FRIEND\", \"FOE\"  
  the similar columns are \"ALIAS\" and \"NAME\", \"MONEY\" and \"WORTH\"

Turning a Table Into Excel File Type

You can convert a table into an excel file, by using this example:

convert the table to an excel