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.

Tip: 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: "ODBC" or "Excel".

  • 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.

 

This topic was last updated on August 19, 2021, at 03:30:51 PM.

Eggplant icon Eggplantsoftware.com | Documentation Home | User Forums | Support | Copyright © 2022 Eggplant