Import or link files in Microsoft Excel XLS or XLSX format. Microsoft XLSX files appeared in Microsoft Office 2007 and later. In this topic, by XLS we mean both XLS and XLSX files. Excel is a spreadsheet program and not a database. Many people nonetheless used Excel as an ad hoc, small database application.
Manifold uses Microsoft facilities to
connect to all Microsoft Office formats, including .xls,
and other legacy Office formats such as .db,
.html, .mdb,
and .wkx, together with newer
Office formats such as .xlsx and
.accdb. If Manifold cannot import from .xls
or .xlsx, that means the Windows
system we are using is missing the necessary facilities. Please
see the Microsoft
Office Formats - MDB, XLS and Friends topic for a solution.
Important: When importing a XLS file the tables and queries that appear in the Manifold project are Manifold components with no further connection to the XLS file from which they were imported.
Choose File-Import from the main menu.
In the Import dialog browse to the folder containing the .xls file.
Double-click the .xls file desired.
Everything found in that .xls file will be imported into the project.
This particular .xls file contains two tables.
If tables from the .xls are
not created as shown
above, that means the Windows system we are using is missing facilities
necessary for a connection to .xls.
Please see the Microsoft
Office Formats - MDB, XLS and Friends topic for a solution.
We can double-click a table to open it.
The Database table contains values for the cells that were in that worksheet. Manifold imports the table as is, not adding any index since there was no index in the Excel file (Excel is a spreadsheet and not a database). To make the table fully editable and selectable we must add an index, as illustrated in the Add an Index to a Table topic.
Important: When linking a XLS file the tables and queries that appear in that data source in the Manifold project stay resident in the XLS file. They are XLS components even though they may appear in many respects, for the convenience of the user, to be Manifold components. XLS files linked into a Manifold project are read only.
The Save cache button allows setting cache options. Most often when linking to a format like XLS, we will ensure the Save cached box is not checked. Working with a linked XLS file would, in theory, be faster if we check the box, but XLS files tend to be very small so operations are fast anyway. If we are going to cache data within the project we may as well simply import the XLS and use full Manifold speed. We uncheck the box and then we press Link.
To link an XLS format file:
Choose File-Link from the main menu.
In the Link dialog browse to the folder containing the .xls file.
Click the .xls file desired.
Check or uncheck the Save cache box as desired.
Press Link. A linked data source will appear in the project.
Press the + icon next to the data source to expand the data source to see the tables it contains.
That creates a data source called states that contains all of the tables of the .xls file's database.
If a new data source for the .xls is
not created as shown above
that means the Windows system we are using is missing the facilities necessary
for a connection to .xls. Please
see the Microsoft
Office Formats - MDB, XLS and Friends topic for a solution.
We can click on the + icon by the data source cylinder to expand the states hierarchy. In this example, the hierarchy contains two tables from the .xls file plus two system tables that seem to appear courtesy of Manifold's internal functioning.
Opening the Database table we see it is the same table that was imported earlier in this topic. Since it is linked the table is still resident in the .xls file. The table is read-only since we cannot add an index to the table (no indexes in Excel format).
UUID - PostgreSQL, Jet DB, Jet WKx, Jet XLS, and ADO.NET databases support UUID fields as a native UUID type.
Booleans - Jet XLS databases support boolean fields.
File - Create - New Data Source
Microsoft Office Formats - MDB, XLS and Friends