In this example we learn how to export the results table of a query for use in other applications.
The Export Results command can export to CSV, DBF, GDB (Esri File Geodatabase), HTML, JSON, LAS, MDB, MML, SQLITE, and XLS formats. HTML, MDB, and XLS formats are Microsoft Office formats that use Microsoft facilities, as discussed in the Microsoft Office Formats - MDB, XLS and Friends topic. Export to DBF is handled by a special driver within Manifold and does not rely on Microsoft facilities.
Important: For simplicity, the following examples do not include a THREADS SystemCpuCount() command in the query. When writing queries manually using the Command Window we should make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in our system. See the THREADS command for more info.
To fit into this documentation, illustrations show a small Manifold desktop, with only a few panes, docked to the right side. In real life we use a much larger Manifold desktop, and more panes would be turned on, with panes docked to the left or to the right, or undocked, as we prefer. Right-click a pane's tab to change where it is docked. Manifold will remember that new arrangement for our next session.
In the illustration above we have opened the sample "books" database used in Chris Fehily's excellent books on SQL. Chris Fehily's books are a great way to learn the basics of SQL. They are highly recommended for anyone new to SQL who will be working with Manifold System, Radian Studio or other Radian-based Manifold products.
We have written a short query in the Command Window. The query text is:
SELECT [au_lname], [phone] FROM [Authors];
To execute the query we press the ! Run button in the main Manifold toolbar.
The results table appears in the Command Window. It is grayed out indicating that it is read-only since we did not include any fields in it that are indexed. If we had included an indexed field the results table would be read/write. That is OK as we can still export the results.
The ? appears in the Counts pane in the Status bar since queries start reporting their results even while query computation is in progress, thus resulting in a possibly indeterminate number of records as results records stream into the results table. To see the total number of records returned in the results table, right-click on the Counts pane and choose Refresh. That will update the Counts pane with the total number of records returned by the query, in this case 23 records.
Choose Edit - Export Results.
In the Export dialog we browse over to folder where we want to place the exported data. Choose a different file name if the default name Results is not desired, choose a type for the file, and then press Export. For this example we will use CSV files, which are ordinary comma-separated value text files.
Manifold exports the results table into a Results.csv file, which in a Windows installation that includes Microsoft Office will be considered an Excel file by default. If we browse over to that file in Windows Explorer and double-click on it that will launch Excel to open the file.
Within Excel we see that the results table has been correctly exported.
Exporting to Microsoft Office Formats - Manifold uses Microsoft facilities to connect to all Microsoft Office formats, including .xls, and other legacy Office formats such as .html, and .mdb. If Manifold cannot export to .xls, .html, or .mdb, 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. Export to .dbf is handled by a special driver within Manifold and does not rely on Microsoft facilities.
Of the other formats to which Export Results can export, CSV, GDB, JSON, LAS, MML, and SQLITE are not Office formats, but GDB, JSON, LAS (used in LiDAR) and MML (a Manifold format) are not universally supported. That leaves CSV and SQLITE for exporting results without having to think about the idiosyncracies of Microsoft Office formats. CSV is not a bad choice because virtually all applications that have anything to do with data can read CSV. SQLITE is a good choice for interchange with applications that are database-aware, because it preserves data types better than CSV.
Command Window - Query Builder
Example: Refer to a Table within a Data Source - Within a query, how to refer to a table that is in a data source.
Example: Switching between Manifold and Native Query Engines - How to use the !manifold and !native commands to switch a query in the Command window from use the Manifold query engine to whatever query engine is provided by a data source.
Example: Automatically Generating CREATE Queries - How to use the Command window to automatically generate SQL in the form of CREATE queries that create a desired component.