Kognitos Spreadsheet Operations Library Documentation

This manual is divided into several sections, each focusing on a specific aspect of the Kognitos Spreadsheet Operations Library.

Introduction

The Kognitos Spreadsheet Operations Library provides a powerful and intuitive way to interact with spreadsheet files and their contents. This library enables users to perform a wide range of operations on spreadsheet files, including opening, converting, merging, and manipulating tables within sheets. By leveraging Kognitos' natural language processing capabilities, users can automate complex spreadsheet tasks with ease. This section provides an overview of the Kognitos Spreadsheet Operations Library and outlines the prerequisites for using it effectively.

Prerequisites for Using Kognitos Spreadsheet Operations Library

Before you can start using the Kognitos Spreadsheet Operations Library, there are several prerequisites that need to be met:

1. Kognitos Account: You need to have an active Kognitos account. If you do not have one, you can sign up for a Kognitos trial account to get started.
2. Access to Spreadsheet Files: Ensure that you have access to the spreadsheet files you intend to work with. These files can be stored locally or in cloud storage services like AWS S3.
3. Basic Understanding of Spreadsheet Concepts: While Kognitos simplifies interactions, having a basic understanding of spreadsheet concepts (e.g., sheets, tables, columns) will help you automate more effectively with Kognitos.

Once these prerequisites are met, you are ready to start leveraging the power of Kognitos to enhance your spreadsheet operations, making them more accessible, efficient, and customizable.

Overview

The Excel Book allows you to work with Excel files when they are more than just a simple column row structure. For example we may have a sheet that looks like this:

We may only need to get the table that starts with the Pet Name column and ends with the Pet Type column. Excel sheets can get complicated so we can use Excel operations to get the data we need and then further process it.

As a reminder, once you have access to a table (may be one of many within an Excel sheet), you can follow this guide to perform a variety of actions on that table.

Learning the Excel Book (integration)

Navigate to the Department tab of Kognitos and select the Excel book to learn.

Get started

Once your book has been learnt (integrated) you can starting using Excel in Kognitos.

The key phrase to use if you want to work with an Excel workbook is open the sheet at:

the files are the spreadsheet files
open the sheet at the spreadsheet file

Line 2 above shows how to use the keyword open the sheet at which allows Kognitos to recognize your file as an Excel Workbook.

Very Standard Excel Sheets

If you have an Excel sheet that is very simple with just column headers and rows, you can also read in the Excel sheet directly as a table then work with the data as a table.

Extract tables from the Excel Document

Get the Table by Specifying Start and End to the Table

get the spreadsheet file
open the sheet at the spreadsheet file
get the sheet's table whose first header is "Pet Name" and whose last header is "Pet Type"

The above lines will grab this table from the example above INSTEAD OF THE WHOLE sheet:

Turning A Table Into an Excel File

We cover table operations in the Table Data guide, but you may find yourself with a table, for example an extracted table from a document. Run the following command to turn it into an Excel file type:

convert the table to an excel

File and Sheet Operations

Opening a Sheet from a File or String

To interact with spreadsheet files using Kognitos, the first step is to open the sheet from a file or a string. This section outlines the commands required to open sheets from different sources.

Opening a Sheet from a File

the file is "s3://bucket-name/path/to/file.xlsx"
get the file as a sheet

Opening a Sheet from a String

the string is "s3://mybucket/my_spreadsheet.xlsx"
open the sheet at the string

Opening a Sheet from a File Path

the file is "s3://mybucket/my_spreadsheet.xlsx"
open a sheet at the file

Creating a Merged Spreadsheet

To create a merged spreadsheet from multiple files, you can specify the files and the criteria for merging.

Merging Two Files

the first file is "s3://bucket/file1.csv"
the second file is "s3://bucket/file2.xlsx"
create a merged spreadsheet with
    the files
    the join is "inner"
    the similar columns are "Name", "Full Name", "Date", "Event Date"
    the ignored columns are "ID", "Timestamp"

Merging Multiple Files

the spreadsheet files are "s3://bucket/sales_q1.csv", "s3://bucket/sales_q2.csv"
create a merged spreadsheet
    the spreadsheet files
    the similar columns are "Product ID", "ID"

Table Detection and Manipulation

Interacting with tables within sheets is a core functionality when working with spreadsheets in Kognitos. This section covers how to detect and manipulate tables based on specific criteria.

Detecting and Working with Tables

Detecting a Table in a Sheet

open the sheet at "s3://bucket/key"
get the sheet's table

Detecting a Specific Table by Name

open the sheet at "s3://bucket/key"
get the sheet's "Deal ID"

Detecting a Workbook by Name

open the sheet at "s3://bucket/key"
get the sheet's workbook whose name is "Format"

Conversion to Excel

Converting data into Excel format is a common requirement for many business processes. This section covers how to convert tables to an Excel file using Kognitos.

Converting Tables to an Excel File

Basic Conversion

get the document's tables
convert the tables to an excel

Conversion with Specified Sheet Names

get the document's tables
convert the tables to an excel with
    the sheet names are "Summary", "Details", "Appendix"