In this example we learn how to export the results table of a query for use in other applications.
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.
In the illustration above we have opened the sample "books" database used in Chris Fehily's excellent book, SQL Visual Quick Start Guide. Either this book or Chris Fehily's more recent SQL Short Course is a great way to learn the basics of SQL. Both 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.
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.
Important: See the Notes below on the need to use 32-bit Manifold for certain file types, a requirement of Microsoft software used by Manifold.
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.
32-bit Jet and Export formats - As discussed in the Example: Switching between Manifold and Native Query Engines topic, Manifold uses Microsoft's JET database drivers to connect to file formats supported by JET. Unfortunately, JET is primarily a 32-bit technology (yep... still...) so to either read or write JET formats we must launch Manifold in 32-bit mode. That means to export results to JET formats we must have launched Manifold in 32-bit mode. That's annoying but it is easy to get around by exporting to a format, such as CSV, that does not use JET.
The Export Results command can export to CSV, DBF, HTML, LAS, MDB, MML or XLS. CSV, LAS and MML do not use JET, but both LAS (used in LiDAR) and MML (a Manifold format) are not general purpose. That leaves CSV for exporting results from 64-bit Manifold sessions. CSV is not a bad choice because virtually all applications that have anything to do with data can read CSV.
Edit - Join
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.