Excel

This book allows you to work with Excel in Kognitos.

Overview

The Excel Book allows you to work with Excel files in Kognitos. This book enables users to perform a wide variety of tasks on Excel workbooks and spreadsheets, including opening, converting, merging, and manipulating tables.

Prerequisites

Learning the Excel Book

In order to work with Excel, you need to first learn the Excel book. To do so:

  1. In the left sidebar, click on Books.
  2. In the search bar, search for "Excel".
  3. Click on + Book. A pop-up window will appear asking to Add New Book. Click Add.

Once the book has been learned, you can start using Excel in Kognitos.

Workbooks vs. Worksheets

A workbook is the complete Excel file that can contain multiple worksheets. In Kognitos, the term sheet refers to the entire workbook, including all its individual tabs.

A worksheet (worksheet) specifically refers to a single tab within the workbook.

Excel Operations

This section serves as a guide on Excel operations in Kognitos.

1. Opening Excel Files

This section provides instructions on how to open Excel workbooks and worksheets.

πŸ“˜

Local Excel Files

If you want to open a local Excel file with these actions, you will encounter a Question prompting you to Please Provide File. Your local Excel file can be uploaded here by selecting the Upload files method.

Question: Please Provide File for Upload

1.1. Opening an Excel Workbook

These operations will open a sheet object, representing an entire Excel workbook. An Excel workbook can be opened from a local file or from a public URL.

πŸ”” Note: This opens a sheet. If you would like to open a worksheet, see Opening An Excel Worksheet.

Syntax

open a sheet at {the file | "<url>"} where
  the worksheet name is "{name}"

Inputs:

  1. {the file | "<url>"}: (one is required)
  • the file: Use the phrase the file to upload a local Excel file.
  • "<url>": The URL to be opened, as a quoted string. Kognitos cannot open files from private cloud drives, so ensure the file is publicly accessible, configured with the necessary access permissions, or use a presigned URL with an expiration date for temporary access.
  1. name: The name of the worksheet within the Excel workbook. (optional)

Examples

1. Using the file

open a sheet at the file

2. Using a public URL

open a sheet at "https://onedrive.live.com/spreadsheet1.xlsx"

3. Using the file and specifying the worksheet name

open a sheet at the file where
  the worksheet name is "Sheet1"

4. Using a public URL and specifying the worksheet name

open a sheet at "https://onedrive.live.com/spreadsheet1.xlsx" where
  the worksheet name is "Sheet1"

1.2. Opening an Excel Worksheet

This operation will open a worksheet, representing a single tab within the Excel workbook.

Syntax

open the worksheet at {the file | "<url>"}
  the worksheet name is "{name}"

Inputs

  1. {the file | "<url>"}: (one is required)
  • the file: Use the phrase the file to upload a local Excel file.
  • "<url>": The URL to be opened, as a quoted string. Kognitos cannot open files from private cloud drives, so ensure the file is publicly accessible, configured with the necessary access permissions, or use a presigned URL with an expiration date for temporary access.
  1. name: The name of the worksheet, as a quoted string (required).

πŸ”” Note: The worksheet name is required for this operation to identify the specific tab in the Excel file to open.

Examples

1. Using the file

open the worksheet at the file
  the worksheet name is "Sheet1"

2. Using "<url>"

open the worksheet at "https://onedrive.live.com/spreadsheet1.xlsx"
  the worksheet name is "Sheet1"

2. Converting a File to Excel Format

This operation converts a file into sheet format.

The input file must be one of the following types:

  • .csv
  • .xlsx
  • .json

Syntax

get the file as a sheet

Example

the file
get the file as a sheet

3. Getting a Table From An Excel File

You can get a table from an Excel file by specifying the column headers. Tables can be operated on using table operations.

πŸ”” Note: This operation applies to a sheet.

Syntax

get the sheet's table whose first header is "{start column}" and whose last header is "{end column}"

Inputs

  1. start column: The starting column for the table.
  2. end column: The ending column for the table.

Example

Consider the following table:

NameAgeFavorite BookFavorite Genre
Alice25Pride and PrejudiceRomance
Bob32The HobbitFantasy
Charlie281984Dystopian
Diana22To Kill a MockingbirdHistorical Fiction
Ethan35The Da Vinci CodeThriller

The following operation will retrieve a subset of the table by selecting the columns from "Name" to "Favorite Book":

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

This is the resulting table:

NameAgeFavorite Book
Alice25Pride and Prejudice
Bob32The Hobbit
Charlie281984
Diana22To Kill a Mockingbird
Ethan35The Da Vinci Code

4. Converting Tables to Excel Workbooks

This section covers how to convert tables to Excel files using Kognitos.

πŸ‘‰

Excel File Creation

Excel files cannot be created from scratch. They must be generated from existing tables. To learn how to create a table, read more on Creating Tables

4.1. Converting a Singular Table to an Excel File

convert the table to an excel

4.2. Converting Multiple Tables to an Excel File

convert the tables to an excel

4.3. Converting Tables to Excel with Specified Sheet Names

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

Note: This operation can also be used to rename sheets inside a workbook.


5. Renaming Excel Files

The rename operation is used to rename a file. It can be used to rename an Excel file.

Syntax

rename the excel to "{new name}"

Inputs

  1. new name: The new name for the Excel file. (required)

Example

convert the invoices table to an excel
rename the excel to "invoices.xlsx"

6. Creating a Merged Spreadsheet from Multiple Files

This operation merges multiple spreadsheet files into one. You can rename columns, ignore specific columns, and choose how the files are joined.

Syntax

create a merged spreadsheet
  the files
  the join is "{join type}"
  the similar columns are "{original column name}", {new column name}"
  the ignored columns are "{ignored column}"

Inputs

  1. the files: The spreadsheet files to be merged. Supported formats are .csv and Excel. (required)
  2. type: The type of join to use when merging the files. (optional) Options:
    • outer (default)
    • inner
    • left
    • right
  3. original column name, new column name: A pair of column names that should be considered the same across spreadsheets. The first in each pair is the original column name, and the second is the new column name all occurrences will be renamed to.You can specify multiple column pairs, separated by commas. Example: "Name", "Full Name", "Date", "Event Date". (required)
  4. ignored columns: A list of columns to be excluded from the final merged spreadsheet. (optional)

Examples

1. Merging Two Files

the first file is "https://onedrive.live.com/file1.csv"
the second file is "https://s3.amazonaws.com/bucket-name/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"

2. Merging Multiple Files

the spreadsheet files are "https://s3.amazonaws.com/bucket-name/sheet1.csv", "https://s3.amazonaws.com/bucket-name/sheet2.csv"
create a merged spreadsheet
    the spreadsheet files
    the similar columns are "Product ID", "ID"

7. Getting an Excel Worksheet's Row

This operation finds a row in a worksheet that contains a specific value.

Option 1: By Row Number

Syntax

get the worksheet's row
  the row number is {number}

Inputs

  1. number: The row number.

Option 2: By Column Label & Cell Value

Syntax

get the worksheet's row
  the column label is "{label}"
  the cell value is "{value}"

Inputs

  1. label: The label of the column, in quotes. Example: A, AE, D
  2. value: The specific value of the cell in the target row that you want to retrieve, in quotes.

Examples

Given the following table:

ABC
1CharacterHouseFavorite Spell
2Harry PotterGryffindorExpelliarmus
3Hermione GrangerGryffindorAlohomora
4Ron WeasleyGryffindorRiddikulus
5Luna LovegoodRavenclawExpecto Patronum

Here are two examples on how to retrieve row #5:

Luna LovegoodRavenclawExpecto Patronum

By Row Number

get the worksheet's row
  the row number is 5

By Column Label & Cell Value

get the worksheet's row
  the column label is "B"
  the cell value is "Ravenclaw"

8. Getting an Excel Worksheet's Row's Cell

This operation finds a cell in a specific row of a worksheet that matches a given value, regex, or column label. To learn how to get a row, see Getting an Excel Worksheet's Row.

Syntax

get the row's cell
  the value is "{value}" | the column label is "{label}" | the regular expression is "{regex}"

Options & Inputs

One of the following options is required:

  1. the value is "{value}", where value is the value of the cell you're targeting.
  2. the column label is "{label}", where label is the label of the cell you're targeting.
  3. the regular expression is "{regex}", where regex is a regex that matches the cell you're targeting.

Examples

Given the following table:

ABC
1MovieYearMain Character
2Iron Man2008Tony Stark
3The Dark Knight2008Bruce Wayne
4Black Panther2018T'Challa
5Spider-Man: No Way Home2021Peter Parker

Here is how we would find cell Black Panther in cell A4:

1. Using the value is

get the row's cell
  the value is "Black Panther"

2. Using the column label is

get the row's cell
  the column label is "A"

3. Using the regular expression is

get the row's cell
  the regular expression is "[Bb]lack [Pp]anther"

9. Getting an Excel Worksheet Cell's Column Label

This operation gets the column label of a specific cell in a worksheet. To learn how to get a cell, see Getting an Excel Worksheet's Row's Cell.

Syntax

get the cell's column label

Example

Given the following table:

ABC
1HeroWeaponCompanion
2KnightSwordDragon
3WizardStaffFamiliar
get the cell's column label 

This operation would retrieve C as the column label for cell C2.


10. Setting an Excel Worksheet Cell to a String

This operation sets the value of a cell in a worksheet to a specified string. To learn how to get a cell, see Getting an Excel Worksheet's Row's Cell.

Syntax

set the cell to "{new value}"

Inputs

  1. new value: The new text value to set the cell to.

Example

Given the following table and cell C3:

ABC
1Plant TypeEnvironmentCompanion
2FernShadeMoss
3CactusDesertSucculent
4BambooTropicalIvy
set the cell to "Agave"

This operation will replace Succulent with Agave in cell C3. The new table would be:

ABC
1Plant TypeEnvironmentCompanion
2FernShadeMoss
3CactusDesertAgave
4BambooTropicalIvy

11. Getting an Excel Worksheet's Changelog

This operation retrieves the changes made to the worksheet and returns them as a JSON object.

get the worksheet's changes

12. Apply Changelogs to an Excel Worksheet

This operation takes a list of changelogs and adds them as changes in the worksheet's data.

apply the changelogs to the worksheet

13. Saving An Excel Worksheet

This operation reads the original excel workbook and the relevant worksheet in it. Applies all the accumulated changes to the worksheet. Then saves the entire excel workbook to a temporary location.

save the worksheet

Working with .xls and .csv Files

The .xls and .csv formats are not directly supported in the Excel Book.

To work with these files, you can use the following code to extract the data as a table. Once you've done this, you can perform table operations and convert to Excel for further Excel operations.

get the file as a table
get the above as the temp table