The Export Tool allows data from databases, tables and other database objects to be exported in a variety of formats. The following steps describe how to export data.
Export will not work for Generic JDBC or Generic ODBC connections
If you want to export data that contains a Binary Large Object (BLOB) data type, make sure to enable the option Convert binary to hex.
Select File > Options > Results and check the box next to Convert binary to hex, then follow the steps below:
1. Select Tools > Export Data from the Menubar. Choose a server from which to export data. Select the server, navigate the database and schema objects, and click ok. This will bring up the Export dialog. It is also possible to right-click on items in the Schema Browser and select Tools > Export Data.
2. The first tab in the Export Data Dialog is the General Tab. In this section, specify the source of the data to be exported. The first option is to specify a table to export which will export the contents of the table. The next choice is to specify a SELECT statement of which the results will be exported. If the SELECT statement is chosen, then select a database in the database combo box in which the SELECT statement will be executed. Click Next to select Export Options.
3. In the Options Tab, browse and select a file location in which to save the exported data. Specify the encoding and platform into which the file will be saved. One of two formats may be specified: delimited data or Insert statements. When Delimited Data is specified, choose the delimiter, quote identifier, NULL value and whether to include the column names as the first of the export. Choosing the INSERT statement format allows executing the export later in the Query Analyzer. Indicate the Data Quote Identifier for parsing quotes within the data. Click Next to begin the Export.
If you are exporting BLOB data types, make sure to export the data to a .CSV file (Delimited Data) rather than an Excel file to avoid the column size limitation that Microsoft Excel files have.
4. The Status Tab displays Export progress and any errors or warnings in the message text window as the Export proceeds. Clicking the Cancel button halts the export. Exports stream data directly to disk and it is highly recommended that Export or "Save Result" be used instead of copying large amounts of data from a Grid Result. Using the Operating System's clipboard for a large copy effectively doubles the amount of RAM in use because the Grid Result is already holding those items in memory. Exporting to a file uses much less RAM, as a file is streamed to disk instead of being kept whole in memory.
Date and time formats are specified by date and time pattern strings. Within date and time pattern strings, unquoted letters from ’A’ to ’Z’ and from ’a’ to ’z’ are interpreted as pattern letters representing the components of a date or time string. Text can be quoted using single quotes (’) to avoid interpretation. "’’" represents a single quote. All other characters are not interpreted; they are simply copied into the output string during formatting, or they are matched against the input string during parsing. It is possible to create a custom date, time and date/time format within File -> Options -> General [Appearance].
| Letter | Date or Time Component | Presentation | Examples |
|---|---|---|---|
G | Era designator | Text | AD |
y | Year | Year | 1996; 96 |
M | Month in year | Month | July; Jul; 07 |
w | Week in year | Number | 27 |
W | Week in month | Number | 2 |
D | Day in year | Number | 189 |
d | Day in month | Number | 10 |
F | Day of week in month | Number | 2 |
E | Day in week | Text | Tuesday; Tue |
a | Am/pm marker | Text | PM |
H | Hour in day (0-23) | Number | 0 |
k | Hour in day (1-24) | Number | 24 |
K | Hour in am/pm (0-11) | Number | 0 |
h | Hour in am/pm (1-12) | Number | 12 |
m | Minute in hour | Number | 30 |
s | Second in minute | Number | 55 |
S | Millisecond | Number | 978 |
z | Time zone | General time zone | Pacific Standard Time; PST; GMT-08:00 |
Z | Time zone | RFC 822 time zone | -0800 |
The following examples show how date and time patterns are interpreted in the U.S. locale. The given date and time are 2001-07-04 12:08:56 local time in the U.S. Pacific Time time zone.
| Date and Time Pattern | Result |
|---|---|
"yyyy.MM.dd G ’at’ HH:mm:ss z" | 2001.07.04 AD at 12:08:56 PDT |
"EEE, MMM d, ’’yy" | Wed, Jul 4, ’01 |
"h:mm a" | 12:08 PM |
"hh ’o’’clock’ a, zzzz" | 12 o’clock PM, Pacific Daylight Time |
"K:mm a, z" | 0:08 PM, PDT |
"yyyyy.MMMMM.dd GGG hh:mm aaa" | 02001.July.04 AD 12:08 PM |
"EEE, d MMM yyyy HH:mm:ss Z" | Wed, 4 Jul 2001 12:08:56 -0700 |
"yyMMddHHmmssZ" | 010704120856-0700 |
Export of a JSON file for MongoDB
In the Schema Tree Menu or the toolbar menu, right-click on Tools > Export Data.
- In the General tab, select the Objects to be exported and click Next
- In the Options tab, enter a Folder/File output path name, select the JSON Format, select the options that you need in the JSON Options section and click Next
- In the Status tab, you will see the export status and eventual completion.
Date, Time, and Number Formatting options are not available for this feature.
