Icon Importing and Exporting Tables and Query Result Sets

Introduction
Importing and exporting tables and query result sets to and from delimited text files is accomplished through the ImportTable and ExportTable methods of the TDBISAMTable and TDBISAMQuery components. The properties used by the ImportTable and ExportTable methods include the DatabaseName, TableName, and Exists properties. Importing a table copies the entire contents of a delimited text file to an existing table or query result set. Exporting a table copies the contents of a table or query result set to a new delimited text file. The records that are copied can be controlled by setting a range or filter on the source table or query result set prior to calling the ExportTable method. Please see the Setting Ranges on Tables and Setting Filters on Tables and Query Result Sets topics for more information.

Importing Data
To import a delimited text file into an existing table, you must specify the DatabaseName and TableName properties of the TDBISAMTable component and then call the ImportTable method. When using a TDBISAMTable component, the table can be open or closed when this method is called, and the table does not need to be opened exclusively. If the table is closed when this method is called, then DBISAM will attempt to open the table before importing the data into it. It is usually good practice to examine the Exists property of the TDBISAMTable component first to make sure that you don't attempt to import data into a non-existent table. If you do attempt to import data into a non-existent table an EDBISAMEngineError exception will be raised. The error code given when an import fails due to the table not existing is 11010 and is defined as DBISAM_OSENOENT in the dbisamcn unit (Delphi and Kylix) or dbisamcn header file (C++Builder). To import a delimited text file into a query result set, the TDBISAMQuery SQL property must be populated with a SELECT SQL statement and the Active property must be True.

The following example shows how to import a delimited text file into a table using the ImportTable method:

Incoming text file has following layout:

Field #    Name              DataType
-------------------------------------
1          CustomerName      ftString
2          ContactName       ftString
3          Phone             ftString
4          Fax               ftString
5          EMail             ftString

Table has following structure:

Field #    Name              DataType     Size
----------------------------------------------
1          CustomerID        ftAutoInc    0
2          CustomerName      ftString     30
3          ContactName       ftString     30
4          Phone             ftString     10
5          Fax               ftString     10
6          EMail             ftString     30
7          LastSaleDate      ftDate       0

Index Name      Fields In Index      Options
----------------------------------------------
(none)          CustomerID           ixPrimary

{ In this example we'll use a comma as a delimiter }

var
   IncomingFields: TStrings;
begin
   IncomingFields:=TStringList.Create;
   try
      with MyTable do
         begin
         DatabaseName:='d:\temp';
         TableName:='customer';
         if Exists then
            begin
            with IncomingFields do
               begin
               Add('CustomerName');
               Add('ContactName');
               Add('Phone');
               Add('Fax');
               Add('Email');
               end;
            { Date, time, and number formatting left
              to defaults for this example }
            ImportTable('d:\incoming\customer.txt',
                        ',',False,IncomingFields);
            end;
         end;
   finally
      IncomingFields.Free;
   end;
end;

Information Tables or query result sets in remote sessions can only import delimited text files that are accessible from the database server on which the tables or query result sets reside. You must specify the path to the incoming text file in a form that the database server can use to open the file.

In addition to using the TDBISAMTable and TDBISAMQuery ImportTable methods for importing delimited text files, DBISAM also allows the use of the IMPORT TABLE SQL statement.

Tracking the Import Progress
To take care of tracking the progress of the import we have provided the TDBISAMTable and TDBISAMQuery OnImportProgress events.

Exporting Data
To export a table to a delimited text file, you must specify the DatabaseName and TableName properties of the TDBISAMTable component and then call the ExportTable method. When using a TDBISAMTable component, the table can be open or closed when this method is called, and the table does not need to be opened exclusively. If the table is closed when this method is called, then DBISAM will attempt to open the table before exporting the data. It is usually good practice to examine the Exists property of the TDBISAMTable component first to make sure that you don't attempt to export data from a non-existent table. If you do attempt to export data from a non-existent table an EDBISAMEngineError exception will be raised. The error code given when an export fails due to the table not existing is 11010 and is defined as DBISAM_OSENOENT in the dbisamcn unit (Delphi and Kylix) or dbisamcn header file (C++Builder). To export data to a delimited text file from a query result set, the TDBISAMQuery SQL property must be populated with a SELECT SQL statement and the Active property must be True.

The following example shows how to export a table to a delimited text file using the ExportTable method of the TDBISAMTable component:

Outgoing text file should have the following layout:

Field #    Name              DataType
-------------------------------------
1          CustomerName      ftString
2          ContactName       ftString
3          Phone             ftString
4          Fax               ftString
5          EMail             ftString
    
Table has following structure:

Field #    Name              DataType     Size
----------------------------------------------
1          CustomerID        ftAutoInc    0
2          CustomerName      ftString     30
3          ContactName       ftString     30
4          Phone             ftString     10
5          Fax               ftString     10
6          EMail             ftString     30
7          LastSaleDate      ftDate       0       
    
Index Name      Fields In Index      Options
----------------------------------------------
(none)          CustomerID           ixPrimary

{ In this example we'll use a comma as a delimiter
and only export records that have a non-blank email address }

var
   OutgoingFields: TStrings;
begin
   OutgoingFields:=TStringList.Create;
   try
      with MyTable do
         begin
         DatabaseName:='d:\temp';
         TableName:='customer';
         if Exists then
            begin
            Open;
            try
               Filter:='EMail IS NOT NULL';
               Filtered:=True;
               with OutgoingFields do
                  begin
                  Add('CustomerName');
                  Add('ContactName');
                  Add('Phone');
                  Add('Fax');
                  Add('Email');
                  end;
               { Date, time, and number formatting left
                 to defaults for this example }
               ExportTable('d:\outgoing\customer.txt',
                           ',',False,OutgoingFields);
            finally
               Close;
            end;
            end;
         end;
   finally
      OutgoingFields.Free;
   end;
end;

Information Tables or query result sets in remote sessions can only export data to delimited text files that are accessible from the database server on which the source tables or query result sets reside. You must specify the path to the text file in a form that the database server can use to create the file.

In addition to using the TDBISAMTable and TDBISAMQuery ExportTable methods for exporting data to delimited text files, DBISAM also allows the use of the EXPORT TABLE SQL statement.

Tracking the Export Progress
To take care of tracking the progress of the export we have provided the TDBISAMTable and TDBISAMQuery OnExportProgress events.
Image