<< ibec_IIF | IBEBlock | ibec_IntToHex >>

ibec_ImportData

ibec_ImportData function implemented. This function returns the number of imported (inserted) records.

Syntax

 function ibec_ImportData(TargetConnection : variant; TargetTable : string; ImportType : integer; 
 SourceFile : string; SourceTable : string; 
                      Options : string; Mappings : string; CallbackBlock : string) : variant;

ibec_ImportData imports records from the specified source (SourceFile, SourceTable) into the target database (TargetConnection, TargetTable).

Please note that when using the FixedWidths option for importing data from text files with fixed column widths, the Mappings parameter is mandatory if you import data from a text file with fixed column widths. The syntax of each item in the Mappings string is:

 <source_field_name>=<start_position>,<length>

IBExpert automatically produces an IBEBlock for data import directly in the Import Data window (Block page) so you can always get it there.

Parameters

TargetConnectionHandle of the target connection created with ibec_CreateConnection or ibec_CreateDatabase functions.
TargetTableName of the target table. The table must exist in the target database before importing data.
ImportTypeType of data source. Following data sources are currently supported:
__impTextPlain text files, comma-separated or fixed widths values.
__impClipboardWindows clipboard.
__impExcelMS Excel file.
__impLotus123Lotus 1-2-3 file.
__impQuattroProQuattro Pro file.
__impOpenOfficeOpenOffice spreadsheet.
__impParadoxParadox file.
__impDBasedBASE file.
__impAccessMS Access database.
__impAdvantageAdvantage table.
__impDBISAMDBISAM table.
__impClarionClarion table.
__impHTMLHTML file.
__impXMLXML file.
__impWABWindows Address Book.
__impVCalendarVCalendar file.
SourceFilePath to the source file. Provide an empty string when importing data from the Windows clipboard.
SourceTableName of the source table if necessary (import from MS Access database).
OptionsList of import options, delimited with a semicolon. The following options are currently supported:
RowFirst=<integer>Number of the first row to be imported. Row numbering starts from 1. The default value is 1.
RowLast=<integer>Number of the last row to be imported. By default all rows will be imported.
RowNames=<integer>Number of rows with field names. The default value is 0.
TrimStrings(See Example #3.)
TrimRightThis option removes trailing spaces and control characters from string values whilst importing data. (New in IBExpert version 2015.03.14.)
FixedWidthsOption for importing data from text files with fixed column widths. (See Example #3.)
DateOrder=DMY|DYM|MDY|MYD|YDM|YMDOrder of date parts within a string representation of date values. By default the system defined order is used.
DateSeparator=<char>Date parts separator. By default the system defined separator will be used.
TimeSeparator=<char>Time parts separator. By default the system defined value will be used.
DecimalSeparator=<char>Decimal separator. By default the system defined value will be used.
ThousandSeparator=<char>Thousand separator. By default the system defined value will be used.
CSVDelimiter=TAB|SEMICOLON|COMMA|SPACE|<char>Delimiter for CSV-files. The default value is a semicolon (;).
RecordSeparator=CRLF|CR|LFRecords delimiter. The default value is CRLF.
CommitAfter=<integer>Number of records inserted before committing. The default value is 500.
AnsiToUTF8Converts string values to UTF8 before being inserted into the database.
MappingsSpecifies the column mappings for an import operation. Mappings string should contain a list of pairs <target_column>=<source_column>, delimited with a semicolon. It is possible to use a column name either a column index as <target_column> and <source_column>. If the Mappings options is not specified (is NULL or an empty string) each source column will be mapped to a target column by their index, i.e. the first source column will be mapped to the first target column, the second source column to the second target one, etc.
CallbackBlockCall back IBEBlock, which will be executed for every imported record.

Example #1

 execute ibeblock
 as
 begin
   MyDB = ibec_GetDefaultConnection();

    delete from test_import;
    commit;

   cbb = 'execute ibeblock (RecCount integer)
       as
       begin
         if (ibec_mod(RecCount, 100) = 0) then
         ibec_Progress(Records inserted:  || RecCount);
       end';

   res = ibec_ImportData(MyDB, 'TEST_IMPORT', __impText, 'D:\import\test_import.csv', '',
                        'RowLast=15000; RowFirst=1001;
                         CSVDelimiter=";"; DecimalSeparator=","; DateSeparator="-"; TimeSeparator=":";
                         DateOrder=DMY; CommitAfter=1000',
                         '',
                         cbb);
   if (res is not null) then
     ibec_ShowMessage(res || ' records imported successfully.');
 end; 

Example #2

 execute ibeblock
 as
 begin
   MyDB = ibec_GetDefaultConnection();

   res = ibec_ImportData(MyDB, 'TEST_IMPORT', __impAccess, 'D:\import\demo.mdb', 'CUSTOMER',
                         '',
                         'CUSTOMER_ID="Customer ID"; 2=3; 3=4',
                         '');
   if (res is not null) then
     ibec_ShowMessage(res || ' records imported successfully.');
 end; 

Example #3

    execute ibeblock
    as
    begin
      ...
      sMappings = 'Field1=1,26;' +
                  'Field2=27,26;' +
                  'Field3=53,45';

      Res = ibec_ImportData(DB, 'MYTABLE', __impText,
                            'D:\Import\country_fixed_colnames.txt', '',
                            'RowFirst=1; RowLast=255555;
                            TrimStrings;
                            FixedWidths;', :sMappings, cbb);
      ...
    end

See also:
Import data
Import & export using IBExpert (1)
Import & export using IBExpert (2)
IBEBlock examples including data import and export
Firebird administration: import & export
ODBC Viewer

back to top of page
<< ibec_IIF | IBEBlock | ibec_IntToHex >>