EXPORT AS ... INTO

Options

CurrencyFormatFormat string for currency fields.
DateFormatFormat string for date fields.
DateTimeFormatFormat string for datetime fields.
TimeFormatFormat string for time fields.
FloatFormatFormat string for float/double precision fields.
IntegerFormatFormat string for integer fields.
ExportTextBlobsMemo fields (text blobs) will be exported as regular strings if specified, otherwise they will be omitted.

XML options

EncodingEncoding of xml data (encoding attribute in <?xml version=“1.0” encoding=“utf-8”?>).
StringAsTextIf specified string data will appear in XML file as is, otherwise it will be MIME-encoded.
MemoAsTextIf specified memo fields data will appear in XML file as is, otherwise it will be MIME-encoded.
DateTimeAsTextIf specified date/time fields data will appear in XML file as is, otherwise it will be MIME-encoded.
ConvertToUTF8Converts field data from ANSI to UTF8 before writing to XML file.

TXT/CSV/DIF/SYLK/JSON options

Readable(JSON) Adds some extra spaces/CRLFs to make result file friendly readable.
WriteBOM(JSON/TXT/CSV) Writes UTF8 BOM into the beginning of result file.
OmitCaptionsField captions will not be included in the result file if specified.
QuoteStrings(CSV) String values will be quoted using double quote char (").
Delimiter(CSV) Delimiter char (semicolon is used by default).

HTML options

EncodingEncoding of html data (charset attribute in <META content="text/html; charset=utf8" http-equiv="Content-Type">).
ConvertToUTF8Converts field data from ANSI to UTF8 before writing to HTML file.

XLS/XML spreadsheet options

It is possible to export data into XLSX format if the XLS or XLSX export type is specified. There is no difference between XLS and XLSX export types.

OmitCaptionsField captions will not be included in the result file if specified.
ConvertToUTF8(XML SpreadSheet) Converts field data from ANSI to UTF8 before writing to result file.

DBF options

ConvertToDOSData will be converted from ANSI to OEM encoding.
LongStringsToMemoLong strings (with length > 254 bytes) will be written as memos, otherwise they will be truncated to 254 bytes.
DateTimeAsDateIf specified datetime values will be exported as an 8-bytes string in the format YYYYMMDD, otherwise they will be converted to string using DateTimeFormat format.

Export to JSON format is also supported (see examples 8 and 9 below).

Examples of usage

1. 

 SELECT * FROM RDB$FIELDS
 EXPORT AS HTML INTO 'E:\TestExport.html'
 OPTIONS 'ColorShema=MSMoney; FontFace=Verdana';

Possible ColorShemes are BW, Classic, ColorFull, Gray, MSMoney, Murky, Olive, Plain, Simple.

2. 

 SELECT * FROM RDB$FIELDS
 EXPORT AS XLS INTO 'E:\TestExport.xls' 
 OPTIONS '';

3. 

 SELECT * FROM RDB$FIELDS
 EXPORT AS TXT INTO 'E:\TestExport.txt'
 OPTIONS 'OmitCaptions';

4. 

 SELECT * FROM RDB$FIELDS
 EXPORT AS CSV INTO 'E:\TestExport.txt'
 OPTIONS 'OmitCaptions; Delimiter=";"';

5. 

 SELECT * FROM RDB$FIELDS
 EXPORT AS XML INTO 'E:\TestExport.xml'
 OPTIONS 'Encoding=windows-1251; MemoAsText; StringAsText';

6. 

 SELECT * FROM RDB$FIELDS
 EXPORT AS DBF INTO 'E:\TestExport.dbf'
 OPTIONS 'ConvertToDOS; LongStringsToMemo; DateTimeAsDate';

7. SELECT ... EXPORT AS supports export into an XML spreadsheet: 

 execute ibeblock
 as
 begin
   select * from rdb$relation_fields
   order by rdb$relation_name, rdb$field_position
   export as xmlspreadsheet into 'D:\rdb$relation_fields.xml'
   options 'ConvertToUTF8;
            DateTimeFormat="dd-mm-yyy hh:nn:ss";
            DateFormat="dd-mm-yyyy";
            TimeFormat="hh:nn:ss";
            CurrencyFormat="$0.00";
            IntegerFormat="0";
            FloatFormat="0.0000";
            ExportTextBlobs';

    ibec_ShellExecute('open', 'D:\rdb$relation_fields.xml', '', '', 0);
 end

8. SELECT ... EXPORT AS supports export to JSON format: 

 select * from "Customer"
   export as json into 'D:\MyData\customer.json'
   options 'DateTimeFormat="dd-mm-yyy hh:nn:ss";
            DateFormat="dd-mm-yyyy";
            TimeFormat="hh:nn:ss";
            ExportTextBlobs; 
            Readable;
            WriteBOM';

9. SELECT ... EXPORT AS supports export to JSON format: 

 ExportOptions = 'DateTimeFormat="dd-mm-yyy hh:nn:ss";
            DateFormat="dd-mm-yyyy";
            TimeFormat="hh:nn:ss";
            ExportTextBlobs; 
            Readable;
            WriteBOM';

 select * from "Customer" order by "CustNo" as dataset MyDS;
 ibec_ds_Export(MyDS, __etJSON, 'D:\MyData\customer.json', ExportOptions);
 ibec_ds_Close(MyDS);

See also:
Example: Export data into DBF
ibec_ds_Export

back to top of page
<< SELECT ... AS DATASET | IBEBlock | CLOSE DATASET >>