ETL made easy – TFDBatchMode

In computing, extract, transform, load (ETL) refers to a process where the following applies:

• The Extract process is where data is extracted from homogeneous or heterogeneous data sources
• The Transform process involves in a series of rules or functions applied to the extracted data in order to prepare it for the end target
• The Load process loads the data into the end target

Nowadays, these operations can be everyday operations because we can retrieve information from any source (IoT, big data) and we need to enter this heterogeneous data into our systems. We may simply need to transfer our data to a new and different data source system.

FireDAC provides a component to make these operations really easy: TFDBatchMove.

Getting ready

In this recipe, we will see how to import the old information distributed under heterogeneous sources, CSV and table, into our new data system. We will also be able to export the new data in CSV format.

As already mentioned, TFDBatchMove implements the engine to process the data movement between different types of data sources and destinations. This operation is made possible through reader and writer components. FireDAC provides three types of standard reader and writer:

Component	Use
TFDBatchMoveTextReader	Reader for text file
TFDBatchMoveTextWriter	Writer for text file
TFDBatchMoveDataSetReader	Reader for TDataSet
TFDBatchMoveDataSetWriter	Writer for TDataSet
TFDBatchMoveSQLReader	Reader for SQL
TFDBatchMoveSQLWriter	Writer for SQL

Ensure that you have followed the instructions in the The Amazing FDTable recipe on database preparation. If you don't, then go to it and set up your environment.

How to do it...

Let's look at the following steps:

1. Create a new VCL application and drop these components (every time you add a component, align it to the top)—TComboBox, TButton, TPanel, TDBGrid, TPanel, and TDBGrid (this time, align the component to the client).
2. Ensure you perform caption refactoring, adjust the component size, and so on to make your form look like this:

Figure 1.30: Form layout at design time

3. If you have followed the instructions of the The Amazing FDTable recipe on database preparation, you should see the database connections, as in Figure 14.1, in the Data Explorer tab under the InterBase entry. Select the DELPHICOOKBOOK and EMPLOYEE connections, and drag and drop the CUSTOMERS table from DELPHICOOKBOOK and the CUSTOMER table from EMPLOYEE onto the form.
4. This operation generates four components:
   • DelphiCookbookConnection: The FDConnection to DELPHICOOKBOOK
   • CustomersTable: The TFDQuery component relating to the CUSTOMERS table
   • EmployeeConnection: The FDConnection to Employee
   • CustomerTable: The TFDQuery component relating to the CUSTOMER table
5. Set these SQL statements to TFDQuery components into the form:
   • CustomerTable: select CUST_NO as ID, CONTACT_FIRST as FIRSTNAME, CONTACT_LAST as LASTNAME from {id CUSTOMER}
   • CustomersTable: select * from {id CUSTOMERS}
6. Put the TFDBatchMove component, and two TDataSource components:
   • Rename TDataSource to dsCustomer, set the DataSet property to CustomerTable, and assign it to the DataSource property of the first DBGrid
   • Rename the second TDataSource to dsCustomers, set the DataSet property to CustomersTable, and assign it to the DataSource property of the second DBGrid
7. We'll use the TCombobox component to allow the user to choose the operation to be performed, so set the Items property as follows:
   • CSV to Table
   • Table to Table
   • Table to CSV
8. Declare the CloseDataSets procedure in the private section of the form and use the following code:

procedure TMainForm.CloseDataSets;
begin
  CustomersTable.Close;
end;

9. Declare the OpenDataSets procedure in private section of the form and use the following code:

procedure TMainForm.OpenDataSets;
begin
  CustomersTable.Close;
  CustomersTable.Open;
  CustomerTable.Close;
  CustomerTable.Open;
end;

10. Declare the SetUpReader procedure in the private section of the form and use the following code:

procedure TMainForm.SetUpReader;
var
  LTextReader: TFDBatchMoveTextReader;
  LDataSetReader: TFDBatchMoveDataSetReader;
begin
  case ComboBox1.ItemIndex of
    0:
      begin
        // Create text reader
        // FDBatchMove will automatically manage the reader instance.
        LTextReader := TFDBatchMoveTextReader.Create(FDBatchMove);
        // Set source text data file name
        // data.txt provided with demo
        LTextReader.FileName := ExtractFilePath(Application.ExeName) +
          '..\..\data\data.txt';
        // Setup file format
        LTextReader.DataDef.Separator := ';';
        // to estabilish if first row is definition row (it is this case)
        LTextReader.DataDef.WithFieldNames := True;
      end;
    1:
      begin
        // Create text reader
        // FDBatchMove will automatically manage the reader instance.
        LDataSetReader := TFDBatchMoveDataSetReader.Create(FDBatchMove);
        // Set source dataset
        LDataSetReader.DataSet := CustomerTable;
        LDataSetReader.Optimise := False;
      end;
    2:
      begin
        LDataSetReader := TFDBatchMoveDataSetReader.Create(FDBatchMove);
        // set dataset source
        LDataSetReader.DataSet := CustomersTable;
        // because dataset will be show on ui
        LDataSetReader.Optimise := False;
      end;
  end;
end;

11. Declare the SetUpWriter procedure in the private section of the form and use the following code:

procedure TMainForm.SetUpWriter;
var
  LDataSetWriter: TFDBatchMoveDataSetWriter;
  LTextWriter: TFDBatchMoveTextWriter;
begin
  case ComboBox1.ItemIndex of
    0:
      begin
        // Create dataset writer and set FDBatchMode as owner. Then
        // FDBatchMove will automatically manage the writer instance.
        LDataSetWriter := TFDBatchMoveDataSetWriter.Create(FDBatchMove);
        // Set destination dataset
        LDataSetWriter.DataSet := CustomersTable;
        // because dataset will be show on ui
        LDataSetWriter.Optimise := False;
      end;
    1:
      begin
        // Create dataset writer and set FDBatchMode as owner. Then
        // FDBatchMove will automatically manage the writer instance.
        LDataSetWriter := TFDBatchMoveDataSetWriter.Create(FDBatchMove);
        // Set destination dataset
        LDataSetWriter.DataSet := CustomersTable;
        // because dataset will be show on ui
        LDataSetWriter.Optimise := False;
      end;
    2:
      begin
        LTextWriter := TFDBatchMoveTextWriter.Create(FDBatchMove);
        // set destination file
        LTextWriter.FileName := ExtractFilePath(Application.ExeName) +
          'DataOut.txt';
        // ensure to write on empty file
        if TFile.Exists(LTextWriter.FileName) then
          TFile.Delete(LTextWriter.FileName);
      end;
  end;
end;

12. Now, create event handlers for the Execute button and write the code that follows:

procedure TMainForm.Button1Click(Sender: TObject);
begin
  // ensure user make a choice
  if ComboBox1.ItemIndex = -1 then
  begin
    ShowMessage('You have to make a choice');
    exit;
  end;

  CloseDataSets;

  // SetUp reader
  SetUpReader;

  // SetUp writer
  SetUpWriter;

  // Analyze source text file structure
  FDBatchMove.GuessFormat;
  FDBatchMove.Execute;

  // show data
  OpenDataSets;

end;

13. Run the application by hitting F9 (or by going to Run | Run).
14. In the order they are shown, select the item of TComboBox and click on the ExecuteButton to perform the operation.

15. After the third click, you should see something similar to the following screenshot:

Figure 1.31: Customers table after batchmove operations

16. In addition, at the same level as the executable file, you should find the DataOut.txt file as follows:

Figure 1.32: Output file generated

How it works...

This recipe allowed you to do the following operations:

• CSV to Table
• Table to Table
• Table to CSV

Depending on the chosen operation, specific readers and writers are created and hooked to the FDBatchMove component, to allow it to perform the BatchMove operation.

All the important stuff contained in this recipe resides under these operations—SetUpReader, SetUpWriter, and FDBatchMove.Execute.

FDBatchMove.Execute moves data from the data source to a data destination, but to do it we need to set up the reader and writer to tell FDBatchMove how to perform these operations.

In SetUpReader, we create the reader that will be used to read source data. If it is a Text source (CSV), we need to set the FileName and specify the separator. If it is a DataSet source (DB table), we need only to set the DataSet property only.

In SetUpWriter, we create the writer which well be used to write destination data. If it is a Text destination (CSV), we need to set the FileName to specify the output file path. If it is a DataSet destination (DB table), we need to set the DataSet property only.

Once the readers and writers have been prepared, it is possible to call the execute function that will perform the operations according to the specified instructions. Ensure you use the GuessFormat method to automatically recognize the data source format.

There's more...

You can use the Mappings collection property if you need different fields mapped from source to destination.

You can use the LogFileAction and the LogFileName properties, provided by the TFDBatchMove component, to log data movement.

You can use the ReadCount, WriteCount (or InsertCount, UpdateCount, DeleteCount), and ErrorCount properties to get the batch moving statistic.

Here are some Embarcadero documents about TFDBatchMove: http://docwiki.embarcadero.com/Libraries/en/FireDAC.Comp.BatchMove.TFDBatchMove.