Documentation

Database Explorer

Configure, explore, and import database data

Description

Using the Database Explorer app, you can quickly connect to a database, explore the database data, and import data from the database to the MATLAB® workspace. If you have minimal proficiency writing SQL queries or want to browse the data in your database quickly, use this app to interact with your database.

You can:

  • Create and configure JDBC and ODBC data sources.

  • Establish multiple connections to databases.

  • Select tables and columns of interest.

  • Fine-tune selection using SQL query criteria.

  • Preview selected data.

  • Import selected data into MATLAB workspace.

  • Save generated SQL queries.

  • Generate MATLAB code.

To use Database Explorer for the first time, migrate from Visual Query Builder and set Database Explorer preferences to initialize the app. For details, see Working with Database Explorer.

Open the Database Explorer App

  • MATLAB Toolstrip: On the Apps tab, under Database Connectivity and Reporting, click the app icon.

  • MATLAB command prompt: Enter dexplore.

Examples

collapse all

Display Data from a Single Database Table

Set up the data source for the tutorial.mdb database and connect to this database. For details, see Microsoft Access ODBC for Windows.

Display data in the Data Preview pane by opening the database table of interest in the Database Browser pane on the Database Explorer Toolstrip. When you select a database table in the Database Browser pane, the table is highlighted and a corresponding entry displays in the SQL Criteria panel. Enter query conditions for the selected table in the SQL Criteria panel.

For any given table, you can select the table information in these ways:

  • Click to highlight the database table name. Clicking the database table name does not display data in the Data Preview pane but does update the SQL Criteria panel.

  • Select (All) to choose all table columns and display them in the Data Preview pane.

  • Select specific check boxes to choose individual table columns and display them in the Data Preview pane.

      Note:   The order of the columns in the Data Preview pane matches the order in which you select them in the Database Browser pane.

Select (All) to choose all database columns or select check boxes for specific table columns.

To change your display, select or clear check boxes in the Database Browser pane. The data updates in the Data Preview pane.

The Data Preview pane displays a limited number of rows. The total number of rows selected in the database appears at the right of the display. You can change the display size by clicking Preferences and adjusting the Data Preview size.

Close the database connection. For details, see Configuring a Driver and Data Source.

Join Data from Multiple Database Tables

Set up the data source for the tutorial.mdb database and connect to this database. For details, see Microsoft Access ODBC for Windows.

Display data in the Data Preview pane by opening the desired database table in the Database Browser pane. The SQL Criteria panel updates in the Database Explorer Toolstrip.

When you select additional tables in the Database Browser pane, the SQL Criteria panel updates.

Display the contents for the selected tables using the SQL Criteria panel to define a join of the selected tables. Click the drop-down lists to specify the table column for joining the selected tables. The join results appear in the Data Preview pane.

Close the database connection. For details, see Microsoft Access ODBC for Windows

Query Data Using a Left Outer Join

Set up the data source for the tutorial.mdb database and connect to this database. For details, see Microsoft Access ODBC for Windows

Expand the table suppliers and select the fields SupplierName, City, and Country.

Expand the table producttable and select the fields productDescription and unitCost. The Data Preview pane displays a message prompting you to enter a join condition. There are two empty conditions in the SQL Criteria panel on the Database Explorer Toolstrip.

From the SQL Criteria panel, in the first condition at the top, change the first combo box for condition type to LEFT JOIN. Change the second combo box to suppliers.SupplierNumber. Change the last combo box to producttable.SupplierNumber. A left join, with the suppliers table on the left, implies that all the rows in the suppliers table are included in the final result. The rows in suppliers that do not have a match with any row in producttable are padded with null values in the final result.

In the Data Preview, there are 11 rows that match the query conditions. There is a null in productDescription and a NaN in unitCost because the supplier The Great Teddy Bear Company supplies no products. If the condition type is set to INNER JOIN instead of LEFT JOIN, this row does not appear in the final result.

From the SQL Criteria pane, click + at the end of the LEFT JOIN condition to add a query condition. Change the first combo box to WHERE, the second to suppliers.Country, and the third to NOT LIKE. In the last text box, enter United States and then enter the new condition using the Enter or Tab keys. The query results appear in the Data Preview pane.

Enter the variable name as data in the text box untitled located above the table preview. Select Import > Import to import the data displayed in the Data Preview pane into MATLAB as a variable named data. For details about using the MATLAB Variables editor, see View, Edit, and Copy Variables.

Close the database connection. For details, see Microsoft Access ODBC for Windows

Import Data to the MATLAB Workspace

Set up the data source for the tutorial.mdb database and connect to this database. For details, see Microsoft Access ODBC for Windows.

Select data using the Database Browser pane from a single table. Or, create a query using the SQL Criteria panel. Display the results in the Data Preview pane.

Name the MATLAB variable by entering it in the untitled text box in the Data Preview pane.

Define the data type for a MATLAB variable in the Imported Data panel to store the data displayed in the Data Preview pane. Supported data types are:

  • Cell Array

  • Numeric

  • Structure

  • Table

  • Dataset (requires Statistics and Machine Learning Toolbox™)

Select Import > Import to import the data displayed in the Data Preview pane.

    Note:   When importing large amounts of data, Database Explorer imports data in batches. The batch size is set to 1,000 rows by default. To change the batch size, click Preferences and adjust Import batch size.

Optionally, display the imported data in the MATLAB workspace using the Variables editor. For details about using the Variables editor, see View, Edit, and Copy Variables.

Optionally, manipulate the data using MATLAB functions.

Close the database connection. For details, see Microsoft Access ODBC for Windows.

Related Examples

See Also

Functions

Was this topic helpful?