Working with Excel as a Database
You can use SenseTalk to access data stored in a Microsoft Excel (.xlsx) file as if it were a database. This means it is able to read data from a spreadsheet in the form of database records. Working with Excel files in this way allows you to use most of SenseTalk's database capabilities. For a different approach to using Excel that involves SenseTalk directly reading data from or writing data to an Excel file, see Excel File Interaction.
Define the Connection
To use Excel as a database, run a command similar to the following in order to define the connection. This command includes the path and name of your Excel file:
set myExcelDB to {type: "excel", file: "/<Path>/<MyExcelFile>.xlsx"} -- set the specified variable, myExcelDB, to store the contents of the referenced Excel file and establish a SenseTalk connection.
In the example shown above, the database connection information is being stored in a variable myExcelDB. Although you do not have to store the connection information in a variable, it is recommended as it simplifies referring to the database.
If you establish a connection between SenseTalk and an Excel file, and do not include a writeable: Yes property in the command, SenseTalk cannot write (insert, update, or delete) changes back to the database
Configuring the Excel File Database Connection
Unlike other types of database connections (see ODBC Administration), Excel databases are not writable by default. This writing limitation means that SenseTalk updates any changes you make in memory, but does not write these changes to the Excel file. If you want SenseTalk to update the Excel file using the add record and delete record commands, you must make the database connection writable by specifying the Writable: Yes property as shown in the following example:
set myExcelDB to {type:"excel", file:"C:\Projects\TestFiles\States.xlsx", writable: Yes} -- Set the specified variable, myExcelDB, to store the contents of the referenced Excel file and establish a writable SenseTalk connection
When an Excel connection is Writable, any records fetched will include a _pkey_ property property value. SenseTalk uses this property value to uniquely identify each record that has changes and that should be written to the file.
Generic Database Connection List Properties
Type:Required. Specify the type of database you are connecting to. Specify:ODBCorExcel.Writable:Determine whether or not Eggplant Functional has write abilities for this database. Use this property to change this setting for a database. (Write abilities are enabled by default for ODBC and disabled by default for Excel.)
Excel-Only Connection List Properties
- File:
Required.Specify the file path for the Excel workbook that you want to refer to as a database.
Using Excel as a Database
When using an Excel file as a database, SenseTalk treats each worksheet in the file as a database table.
The first row of the worksheet is treated as a header row, identifying the names of the columns in the table. SenseTalk treats all subsequent rows as database records in the table, ignoring any empty rows.
Consider the following example Excel file:
| State | Capital | Population | Area |
|---|---|---|---|
| California | Sacramento | 39,100,000 | 164,000 |
| Colorado | Denver | 5,300,000 | 104,000 |
| New York | New York | 194,000,000 | 54,600 |
This file contains a single worksheet with four columns and six rows. The last two rows are empty.
If the path to the file is ~/Desktop/States.xlsx, you only need these commands to read the entire contents using the database approach:
set myExcelDB to {type:"excel", file:"C:\Projects\TestFiles\States.xlsx"} -- set the specified variable, myExcelDB, to store the contents of the referenced Excel file
put the records of myExcelDB into states -- fetch all records from the myExcelDB variable and place them into the states variable
put states joined by return -- show the data from the states variable one record per line
Running the above commands returns the following data:
\{area:164000, capital:Sacramento, population:39100000, state:California}
\{area:104000, capital:Denver, population:5300000, state:Colorado}
\{area:54600, capital:New York, population:19400000, state:New York}
Notice that three records were read as property lists, using the column labels from the first row as the names of the properties for each record.
Specifying a Worksheet as a Table
When using an Excel file as a database, SenseTalk treats each worksheet in the file as a database table. The following examples show how to access the information in an Excel file that contains only a single worksheet, or when only the first worksheet in the file is of interest.
To specify a worksheet, include a name property, set to either the name or number (1, 2, ...) of the desired worksheet as shown in the following example:
set myExcelDB to {type:"excel", file:"C:\Projects\TestFiles\States.xlsx", name:"Counties"} -- Set the specified variable, myExcelDB, to store the contents of the referenced Excel file and establish a SenseTalk connection. Specify a worksheet named "Counties".
Another approach is to use the table syntax to specify the desired worksheet, as shown in either of the following examples:
set myExcelDB to table ("Counties") of {type:"excel", file:"C:\Projects\TestFiles\States.xlsx"} -- Set the specified variable, myExcelDB, to store the contents of the referenced Excel file and establish a SenseTalk connection. Specify a database table named "Counties".
set myExcelDB to table (2) of {type:"excel", file:"C:\Projects\TestFiles\States.xlsx"} -- Set the specified variable, myExcelDB, to store the contents of the referenced Excel file and establish a SenseTalk connection. Specify a database table by number.
Excel Table Properties
A table in an Excel database behaves almost identically to a table in a relational (ODBC) database, as described in Excel File Interaction including these properties:
ColumnsToFetchRecordPrototypeAutoSaveUpdatesUseRecordIterator
In addition to the table properties available for all database types, an Excel database table may include the following optional properties:
HeaderRow- When the rows in a worksheet that should be treated as a database don't begin at row 1, set the headerRow property to the row number where the column headers appear and rows below that will be treated as database records. If not specified, row 1 will be treated as the header row.ColumnNames- Set this to a list of names to override the names derived from the header row in the worksheet.
Examples:
set peopleTable to table “People” of myExcelDB
set the headerRow of peopleTable to 3 // the first 2 rows of the people table are descriptive, not data
set peopleTable’s columnNames to [“Name”, “Department”, “Company Phone Extension”]
Creating Multiple Connections to an Excel File
While Eggplant Functional allows you to create more than one connection to an Excel file, doing so can lead to issues and errors. For example, if you connect to an Excel file as both a workbook (described in Excel File Interaction) and as a database (described here), Eggplant Functional might return an error message like InternalWorkbookError - data source needed but no workbook value. You can fix this error by closing one of the connections.
You can also create separate database connections to look at different tables in an Excel workbook, as shown below.
Example:
set myDB1 to {file:ResourcePath("Book1.xlsx"), type:"excel", writeable:"yes"}
set mySheet to table("Sheet1") of myDB1
set myDB2 to {file:ResourcePath("Book1.xlsx"), type:"excel", writeable:"yes"}
set myOtherSheet to table("Sheet2") of myDB2