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:
- In the left sidebar, click on Books.
- In the search bar, search for "Excel".
- 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.
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:
{the file | "<url>"}
: (one is required)
the file
: Use the phrasethe 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.
name
: The name of the worksheet within the Excel workbook. (optional)
Examples
1. Using the file
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
the file
and specifying the worksheet nameopen 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
{the file | "<url>"}
: (one is required)
the file
: Use the phrasethe 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.
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
the file
open the worksheet at the file
the worksheet name is "Sheet1"
2. Using "<url>"
"<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
start column
: The starting column for the table.end column
: The ending column for the table.
Example
Consider the following table:
Name | Age | Favorite Book | Favorite Genre |
---|---|---|---|
Alice | 25 | Pride and Prejudice | Romance |
Bob | 32 | The Hobbit | Fantasy |
Charlie | 28 | 1984 | Dystopian |
Diana | 22 | To Kill a Mockingbird | Historical Fiction |
Ethan | 35 | The Da Vinci Code | Thriller |
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:
Name | Age | Favorite Book |
---|---|---|
Alice | 25 | Pride and Prejudice |
Bob | 32 | The Hobbit |
Charlie | 28 | 1984 |
Diana | 22 | To Kill a Mockingbird |
Ethan | 35 | The 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
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
the files
: The spreadsheet files to be merged. Supported formats are.csv
andExcel
. (required)type
: The type of join to use when merging the files. (optional) Options:outer
(default)inner
left
right
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 theoriginal column name
, and the second is thenew 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)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
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
label
: The label of the column, in quotes. Example:A
,AE
,D
value
: The specific value of the cell in the target row that you want to retrieve, in quotes.
Examples
Given the following table:
A | B | C | |
---|---|---|---|
1 | Character | House | Favorite Spell |
2 | Harry Potter | Gryffindor | Expelliarmus |
3 | Hermione Granger | Gryffindor | Alohomora |
4 | Ron Weasley | Gryffindor | Riddikulus |
5 | Luna Lovegood | Ravenclaw | Expecto Patronum |
Here are two examples on how to retrieve row #5:
Luna Lovegood | Ravenclaw | Expecto 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:
the value is "{value}"
, wherevalue
is the value of the cell you're targeting.the column label is "{label}"
, wherelabel
is the label of the cell you're targeting.the regular expression is "{regex}"
, whereregex
is a regex that matches the cell you're targeting.
Examples
Given the following table:
A | B | C | |
---|---|---|---|
1 | Movie | Year | Main Character |
2 | Iron Man | 2008 | Tony Stark |
3 | The Dark Knight | 2008 | Bruce Wayne |
4 | Black Panther | 2018 | T'Challa |
5 | Spider-Man: No Way Home | 2021 | Peter Parker |
Here is how we would find cell Black Panther
in cell A4
:
1. Using the value is
the value is
get the row's cell
the value is "Black Panther"
2. Using the column label is
the column label is
get the row's cell
the column label is "A"
3. Using the regular expression is
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:
A | B | C | |
---|---|---|---|
1 | Hero | Weapon | Companion |
2 | Knight | Sword | Dragon |
3 | Wizard | Staff | Familiar |
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
new value
: The new text value to set the cell to.
Example
Given the following table and cell C3
:
A | B | C | |
---|---|---|---|
1 | Plant Type | Environment | Companion |
2 | Fern | Shade | Moss |
3 | Cactus | Desert | Succulent |
4 | Bamboo | Tropical | Ivy |
set the cell to "Agave"
This operation will replace Succulent
with Agave
in cell C3
. The new table would be:
A | B | C | |
---|---|---|---|
1 | Plant Type | Environment | Companion |
2 | Fern | Shade | Moss |
3 | Cactus | Desert | Agave |
4 | Bamboo | Tropical | Ivy |
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
.xls
and .csv
FilesThe .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
Updated 4 days ago