XLS .xls, XLSX .xlsx

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.    

 

ico_nb_arrow_blue.pngManifold 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.

 

Import

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.  

 

dlg_import_xls.png

 

To import from XLS format:

 

  1. Choose File-Import from the main menu.

  2. In the Import dialog browse to the folder containing the .xls file.

  3. Double-click the .xls file desired.

  4. Everything found in that .xls file will be imported into the project.

 

eg_import_xls01_01.png

 

This particular .xls file contains two tables.    

 

 

ico_nb_arrow_blue.pngIf 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.

 

eg_import_xls01_02.png

 

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.

Linking an XLS

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.

 

dlg_link_xls.png

 

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:

 

  1. Choose File-Link from the main menu.

  2. In the Link dialog browse to the folder containing the .xls file.

  3. Click the .xls file desired.

  4. Check or uncheck the Save cache box as desired.

  5. Press Link.  A linked data source will appear in the project.

  6. Press the + icon next to the data source to expand the data source to see the tables it contains.

 

eg_link_xls01_01.png

 

That creates a data source called states that contains all of the tables of the .xls file's database.   

 

ico_nb_arrow_blue.pngIf 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.

 

eg_link_xls01_02.png

 

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

 

See Also

Tables

 

Editing Tables

 

Selection

 

Add an Index to a Table

 

File - Create - New Data Source

 

DBMS Data Sources - Notes

 

Microsoft Office Formats - MDB, XLS and Friends