Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!

General information about usage of Adapters

The Adapter concept is what makes FlowHeater such a powerful tool. Adapters can be used for both input and output and combined together in any way. The only exception to this rule is the TestData Adapter, because it generates data and only makes sense on the READ (input) side. Each Adapter has specific capabilities and prerequisites: e.g. the SqlServer Adapter is only suitable for communicating with a Microsoft SQL Database Server. However, you can use the same Adapter to read from (export from source), write to (import into target) and replace existing data (update).

For each Definition set there is always a minimum of two Adapters necessary, and at present this is also the maximum. It makes no difference whether you want to extract data from an SQL database and generate a text file in CSV format, or do the opposite; import from CSV and write to an SQL database.

For example, you may want to update data in a database without resorting to writing your own SQL statements. In this case you can use the SqlServer Adapter on both sides and specify the identical server and database as both the data source and target. In addition you would set the update property to true (set a checkbox) for the Adapter on the right side (WRITE). This way you tell FlowHeater to fetch the value of the Primary Key from the table being read in order to rewrite existing records. For more details see the individual descriptions of the various Adapters.

 

Adapter properties Each Adapter can store any number of fields and any amount of format information. The individual fields (or columns) are listed beneath the Adapter heading.

This arrangement may be unfamiliar, so let us explain this point:

ID;Name;Vorname;Strasse;...
1;Hinz;Kunz;Musterstrasse;...

For example, in a CSV text file the columns are listed one after the other horizontally, from left to right. The same principle also holds true for a database table or view, but in FlowHeater the column names are arranged vertically, one below the other. See the adjacent diagram.

All properties and control information for the Adapter can either be configured using the properties shown below the field list or adjusted using the Configurator (a popup window).

 

 

Formatting: (number, currency, date, time, yes/no)

adapter-format
Individual format settings
For each Adapter field used, you can save individual format settings using the Format tab in the Configurator popup window. The format settings define how the Adapter should interpret or format each field.

This means:
  • On the READ side
    Here you define how a field that is received as a character string from the source should be interpreted, to enable its proper conversion into internal FlowHeater format.

  • On the WRITE side
    Here you define how a field in internal FlowHeater format should be formatted into a string of characters for output.
    Please note: The settings of the WRITE Adapter also determine how fields will be converted or interpreted during a transformation.

Let us take a simple example: You have a CSV file that you want to import into an SQL Server database.

$1,000.21 or 12/31/2008 9:12 am

However, you do not want to just import the data exactly as it stands – let us say you also want to convert dollars to euros in some fields and extract just the dates from fields with dates and times. To achieve this you specify the locale for the Adapter on the READ side as English (USA) and enter in the DataType property for the actual fields Currency and DateTime respectively. In the Adapter on the WRITE side you do not need to change the locale (assuming your default is for the Euro zone), simply enter the DataType property for these fields once more as Currency and Date respectively. That is all there is to it. Of course, for the exchange rate conversion we need two further Heaters: a multiplication operator and an X-Value. An X-Value defines a constant - in this case we enter the current dollar/euro exchange rate.

Note: The value of the exchange rate must be entered in the locale format for decimals on the WRITE side of the Adapter, in the case of a German locale a decimal comma is expected (enter 0,6712 with a comma rather than 0.6712 with a point).

Tip: You could alternatively make use of the Lookup Heater to obtain a dynamic exchange rate from any data source on either the READ or WRITE sides.

Important to realize: The format settings on the READ side apply only for the interpretation of the Adapter while reading the values in. All other formatting is performed using the format settings of the WRITE side. This also applies, as mentioned above, for the interpretation of manually entered values.

The formatting of fields can be limited to the field property "format" or even better effected using a special Format Heater. For example, you may have formatted real numbers (Decimal or Currency) so that they normally have two decimal digits, but if you need three or more decimal digits for one special field, then you would only need to enter "0.000" in the format for the field. See a further example of numeric formatting