<< ibec_ds_Bof | IBEBlock | ibec_ds_FieldCount >>

ibec_ds_Export

With this function you can export datasets into an XML spreadsheet.

Syntax

function ibec_ds_Export(Dataset : variant; ExportType : integer; FileName : string; Options : string) : integer;

The ibec_ds_Export function exports datasets created with SELECT ... AS DATASET statement into a number of various data formats.

ibec_ds_Export always returns 0.

Since IBExpert version 2014.03.16 export to JSON format is now supported (see examples 2 and 3 below).
IBExpert version 2015.03.14 introduced the WriteBOM option for CSV and text exports.

Parameters

ArgumentDescription
DatasetA dataset created with SELECT ... AS DATASET statement.
ExportTypeType of export.
FileNameName of result file.
OptionsSet of export options.

Export types

__etXLSMicrosoft Excel; obsolete, use __etXMLSpreadSheet instead.
__etTXTPlain text.
__etCSVComma-separated values.
__etDIFDIFF
__etSYLKSYLK
__etHTMLHTML
__etXMLXML
__etDBFDBF
__etXMLSpreadSheetXML spreadsheet.
__etJSONJSON

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

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.

Example 1

 execute ibeblock
 as
 begin
    ExportOptions = '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';

    try
      select * from rdb$relation_fields
      order by rdb$relation_name, rdb$field_position
      as dataset dsFields;

      ibec_ds_Export(dsFields, __etXMLSpreadSheet, 'D:\rdb$relation_fields.xml', ExportOptions);

      ibec_ShellExecute('open', 'D:\rdb$relation_fields.xml', , , 0);
    finally
      if (dsFields is not null) then
        ibec_ds_Close(dsFields);
    end;
 end

Example 2

 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';

Example 3

 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:
SELECT ... EXPORT AS ... INTO
Export CSV data - another example

back to top of page
<< ibec_ds_Bof | IBEBlock | ibec_ds_FieldCount >>