In Cell, you can import and use data from a variety of sources, including Access Database. You will not have to enter the data again or analyze or recompile the database after running a query Data imported into Cell through a query can be updated automatically whenever the source database is updated.
Importable Data Sources for Cell
- MS para arquivos texto (*.txt; *.csv)
- MS Access (*.mdb)
- MS dBase (*.dbf)
- Microsoft Paradox (*.db )
- MSExcel Files (*.xls)
- dBase Files (*.dbf)
- Visual FoxPro Table
- Visual FoxPro Database
- MSODBC for Oracle
- MSParadox (*.db )
- SQL Server
In addition, you can use all ODBC data sources that have been registered on the system. You can also check, add, edit, and remove data sources from Data Source under Control Panel > Management Tools.
You can select and extract data records that is needed for your current project from an external database file, and reorganize the data by applying data filtering and sorting criteria.
The Query Wizard lets you easily obtain the data you need without entering or organizing new data by applying appropriate data filtering and sorting criteria.
Step 1 - Select data source
- Click Data > Get External Data > Create New Query.
- In the Step 1 - Select data source dialog box, select the data source you want to access from Data Source List.
In this case, select Xtreme Sample Database-KOR.
- To have the Query Wizard create a new query, select Use Query Wizard and click Next.
- Navigate to the location where the source database file is stored and select the file.
Since we are using a sample data source, this step will be skipped.
Step 2 - To select column
- In the Step 2 - Select Column dialog box, select the data record (column) you want to include in the query.
If the selected database contains multiple tables, specify the table you want to query in Table and Column.
- Select the record (column) you want to query from the column list, and click > to add it to Selected Column list. If you want to move all records (columns) in the column list to Selected Column list, click >> to move them at once.
- Click Next.
Step 3 - To select row to include in query
- In Step 3 - Data Filter, you specify which row to include in query. You can specify up to three filtering criteria; if you do not want to apply filtering, click Next to move on. Select the column to filter, and Condition for the row to include becomes active.
- Click the List button for Condition for the row to include and select the filtering criteria and value to apply.
- Select search criteria; AND or OR.
- Repeat the above steps to filter data.
- Click Next.
Step 4 - To set sort order
- Set a sort order in Step 4 - Sort Order.
Select criteria as First Criteria, and select either Ascending or Descending for the selected criteria. If you are setting multiple sort orders, you can set up to three criteria.
- Click Next.
Step 5 - To preview query result
- You can preview the result data for the query you have created in previous steps in the Finish - Preview Query Result dialog box.
- If the result data is not what is required, click Back to redo the previous steps.
You can click Save Query to save the query result data as Cell query file (*.nxq) or SQL file (*.sql).
- Click Finish.
Step 6 - To select query data location
- Enter the cell address in Existing Worksheet in the Undo External Data with Cell dialog box or click the Select Area button to select the location to insert the query data in.
- Click OK.
- The query result data will be inserted into the active worksheet.
Creating New Queries on Windows 64-bit
A 32-bit Dynamic Link Library (DLL) for ODBC must be installed on Windows 64-bit to get external data. 32-bit DLL files that are for 32-bit Microsoft Access Database Engine, 32-bit Oracle ODBC Driver, and other DB Drivers are required to access data sources.
If MS Office is installed on Windows 64-bit, the 64-bit Microsoft Access Database Engine is installed as well and it can cause to fail to access data sources. In this case, uninstall MS Office and install 32-bit Microsoft Access Database Engine to access data sources.