Here’s an example of how to create an SQLite database using the contents of a basic Numbers table:

NOTE: The example script assumes that the source table has a single row header whose cell values will be used as the names of the created record fields in the database. Before running the script, make sure that the value of each cell of the row header is unique.

NOTE: By default, the Database Events application creates database file in a folder titled “Databases” located in the Documents folder. The provided script will create this folder if it doesn’t exist.

DO THIS ► DOWNLOAD a Numbers document containing the example table.

Build Database from Selected Table
Open in Script Editor
01		property extractDataWithFormatting : false
02		property coerceNumberToInteger : true
03		property documentsFolder : missing value
04
05		set documentsFolder to (path to documents folder)
06		tell application "Numbers"
07		activate
08		try
09		if not (exists document 1) then error number 1000
10		tell document 1
11		try
12		tell active sheet
13		set the selectedTable to ¬
14		(the first table whose class of selection range is range)
15		end tell
16		on error
17		error number 1001
18		end try
19		display dialog "This script will create an SQLite database with the data contained in the selected table." buttons {"Cancel", "Begin"} default button 2 with icon 1
20		tell selectedTable
21		set databaseName to the name of it
22
23		tell application "Finder"
24		if not (exists folder "Databases" of documentsFolder) then
25		make new folder at documentsFolder with properties {name:"Databases"}
26		end if
27
28		set thisName to databaseName
29		set loopCounter to 1
30		repeat
31		if exists document file (thisName & ".dbev") of folder "Databases" of documentsFolder then
32		set thisName to databaseName & "-" & (loopCounter as string)
33		set loopCounter to loopCounter + 1
34		else
35		set databaseName to thisName
36		exit repeat
37		end if
38		end repeat
39		end tell
40
41		set rowCount to (the count of rows)
42		set headerRowCount to header row count
43		set headerColumnCount to header column count
44
45		if headerRowCount is not 1 then error number 1002
46
47		set the fieldNames to the value of cells (headerColumnCount + 1) thru -1 of row 1
48
49		tell application "Database Events"
50		close every database
51		set targetDatabase to ¬
52		make new database with properties {name:databaseName}
53		end tell
54
55		repeat with i from (1 + headerRowCount) to rowCount
56		if extractDataWithFormatting is true then
57		set thisRowData to the formatted value of every cell of row i
58		else
59		set thisRowData to the value of every cell of row i
60		end if
61		if coerceNumberToInteger is true then
62		repeat with w from 1 to the count of thisRowData
63		set thisDataItem to item w of thisRowData
64		if the class of thisDataItem is in {real, number} then
65		set item w of thisRowData to (thisDataItem as integer)
66		end if
67		end repeat
68		end if
69		tell application "Database Events"
70		tell targetDatabase
71		set thisRecord to make new record with properties {name:""}
72		tell thisRecord
73		repeat with q from 1 to the count of fieldNames
74		set thisFieldName to item q of fieldNames
75		set thisFieldData to item q of thisRowData
76		make new field with properties ¬
77		{name:thisFieldName, value:thisFieldData}
78		end repeat
79		end tell
80		end tell
81		end tell
82		end repeat
83		end tell
84		end tell
85		tell application "Database Events"
86		tell targetDatabase
87		save
88		end tell
89		quit
90		end tell
91		display dialog "The database has been created." buttons {"Reveal", "Done"} default button 2 with icon 1
92		if the button returned of the result is "Reveal" then
93		tell application "Finder"
94		activate
95		open folder "Databases" of documentsFolder
96		end tell
97		end if
98		on error errorMessage number errorNumber
99		if errorNumber is 1000 then
100		set alertString to "MISSING RESOURCE"
101		set errorMessage to "Please create or open a document before running this script."
102		else if errorNumber is 1001 then
103		set alertString to "SELECTION ERROR"
104		set errorMessage to "Please select a table before running this script."
105		else if errorNumber is 1002 then
106		set alertString to "MISSING DATA"
107		set errorMessage to "This script assumes the use of a single row header whose cell values will be used as field names in the created database."
108		else
109		set alertString to "EXECUTION ERROR"
110		end if
111		if errorNumber is not -128 then
112		display alert alertString message errorMessage buttons {"Cancel"}
113		end if
114		end try
115		end tell

The result of a successful execution of the script will be a new SQLite database file with a file extension of “.dbev” and two supporting files with extended versions of the database file extension  (⬇ see below ) 

SQLite database queries using the Database Events application are written using standard AppleScript statements incorporating “whose” and “where” clauses to locate records by the value of their fields. The example script below demonstrates this technique using the created example database.

When referencing the database in query scripts, use a POSIX path reference to the main database file (the one with the file extension of “.dbev”)

Query Examples
Open in Script Editor
01		set databaseFile to (POSIX path of (path to documents folder)) & "Databases/Presidents of the United States.dbev"
02
03		tell application "Database Events"
04		tell database databaseFile
05		get value of field "Last Name" of (every record whose value of field "Start Year" is greater than or equal to 1800 and value of field "Start Year" is less than or equal to 1900)
06		--> {"Adams", "Arthur", "Buchanan", "Cleveland", "Cleveland", "Fillmore", "Garfield", "Grant", "Harrison", "Harrison", "Hayes", "Jackson", "Jefferson", "Johnson", "Lincoln", "Madison", "McKinley", "Monroe", "Pierce", "Polk", "Taylor", "Tyler", "Van Buren"}
07
08		get value of field "Last Name" of (every record whose value of field "Party" is "Republican")
09		--> {"Arthur", "Bush", "Bush", "Coolidge", "Eisenhower", "Ford", "Garfield", "Grant", "Harding", "Harrison", "Hayes", "Hoover", "Lincoln", "McKinley", "Nixon", "Reagan", "Roosevelt", "Taft"}
10
11		get value of field "Last Name" of (every record whose value of field "Start Year" is greater than 1899)
12		--> {"Bush", "Bush", "Carter", "Clinton", "Coolidge", "Eisenhower", "Ford", "Harding", "Hoover", "Johnson", "Kennedy", "Nixon", "Reagan", "Roosevelt", "Roosevelt", "Taft", "Truman", "Wilson"}
13
14		get the first record whose value of field "Last Name" contains "Adams"
15		--> record id 438049003 of database "Presidents of the United States"
16
17		get the value of field "Last Name" of every record
18		--> {"Adams", "Adams", "Arthur", "Buchanan", "Bush", "Bush", "Carter", "Cleveland", "Cleveland", "Clinton", "Coolidge", "Eisenhower", "Fillmore", "Ford", "Garfield", "Grant", "Harding", "Harrison", "Harrison", "Hayes", "Hoover", "Jackson", "Jefferson", "Johnson", "Johnson", "Kennedy", "Lincoln", "Madison", "McKinley", "Monroe", "Nixon", "Pierce", "Polk", "Reagan", "Roosevelt", "Roosevelt", "Taft", "Taylor", "Truman", "Tyler", "Van Buren", "Washington", "Wilson"}
19
20		tell (first record whose value of field "Index" is 16)
21		set dialogText to (value of field "First Name") & space & (value of field "Last Name")
22		set dialogText to dialogText & linefeed & tab & "Index: " & (value of field "Index")
23		set dialogText to dialogText & linefeed & tab & "Party: " & (value of field "Party")
24		set dialogText to dialogText & linefeed & tab & "Served: " & (value of field "Start Year") & "-" & (value of field "End Year")
25		end tell
26		end tell
27		end tell
28
29		display dialog dialogText buttons {"OK"} default button 1