meta data for this page
  •  

EXPORT AS ... INTO

Options

CurrencyFormat Format string for currency fields.
DateFormat Format string for date fields.
DateTimeFormat Format string for datetime fields.
TimeFormat Format string for time fields.
FloatFormat Format string for float/double precision fields.
IntegerFormat Format string for integer fields.
ExportTextBlobs Memo fields (text blobs) will be exported as regular strings if specified, otherwise they will be omitted.

XML Options

Encoding Encoding of xml data (encoding attribute in <?xml version=“1.0” encoding=“utf-8”?>).
StringAsText If specified string data will appear in XML file as is, otherwise it will be MIME-encoded.
MemoAsText If specified memo fields data will appear in XML file as is, otherwise it will be MIME-encoded.
DateTimeAsText If specified date/time fields data will appear in XML file as is, otherwise it will be MIME-encoded.
ConvertToUTF8 Converts 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.
OmitCaptions Field 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

Encoding Encoding of html data (charset attribute in <META content=“text/html; charset=utf8” http-equiv=“Content-Type”>).
ConvertToUTF8 Converts 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.

OmitCaptions Field 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

ConvertToDOS Data will be converted from ANSI to OEM encoding.
LongStringsToMemo Long strings (with length > 254 bytes) will be written as memos, otherwise they will be truncated to 254 bytes.
DateTimeAsDate If 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);