Dynamic data import / export

datenbank

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

Adapter Excel

The MS Excel Adapter is responsible for the import/export of Microsoft Excel® files (XLS, XLSX, XLSM). The Adapter supports MS Excel (Office) versions 2003, 2007, 2010, 2013 and 2016.

Important: The FlowHeater Excel Adapter requires one of these versions of Excel installed and fully operational on your computer. Note that a valid Microsoft Excel license is also a requirement and this is not supplied as a part of FlowHeater.

Configuration

General tab

Excel Adapter - ConfigurationExcel Adapter - ConfigurationHere you enter the path and filename (absolute or relative) of the MS Excel Workbook. If the Adapter is presently used in FlowHeater on the WRITE (right hand) side, you can additionally confirm whether a potentially existing Excel file should be overwritten.

Should the Excel file be protected by a password, you can optionally enter the password here. If you also want the password to be stored, it is necessary to also check the option "Save Password". Note: All passwords are saved in encrypted form in the Definition file.

When the Excel Adapter is used in FlowHeater on the WRITE side (right hand side), you can configure the following additional options:

  1. Overwrite if file already exists: This determines whether or not the Excel Adapter should overwrite an existing Excel file.

  2. Append data to a worksheet: If this option is checked, the data is added to the end of an existing Excel table. FlowHeater automatically searches for the last used entry in the specified area and begins to write data from this point on in the Excel table/worksheet.

  3. Update data: When you check this option the Excel Adapter will search for the defined key fields in data rows of the specified target area. All rows or records found that match the key criteria will be updated. Important: At least one field must have “Key field for updating” checked under the “Fields / Data types” tab.

  4. Clear the target area before importing: If you check this option all data in the target area specified will be deleted automatically before the import begins. This ensures that no redundant rows from a previous run are left behind in the sheet when importing a smaller amount of data into the same area.


The "Test opening" button enables you to verify whether the Excel file can be opened properly or not.

 

Fields / Data types tab

Excel Adapter - Fields and data typesExcel Adapter - Fields and data typesWorksheet: Here you can select from the Excel table sheet names. By default, the name of the first sheet is shown. As an alternative to sheet names you can also refer to them by index number (1-n).

Format field names in bold: When this option is checked, Excel will display the column headings in bold.

First row contains field names: Checking this option tells the Excel Adapter to obtain or create field names in the first row of the specified range.

Columns/Rows from/to: Here you specify the range of cells from where the data is to be input or where in the output sheet it is to be written. Note: if no value is entered for "to" here, the Excel Adapter continues to read or write until no further data is found. Completely empty rows are always skipped.

Fields Read: This button will extract the field names and data types from the Excel worksheet for the range specified. The fields list is refreshed when you click this button.

Note about Numeric and date/time fields: At present all numeric entries are assigned the FlowHeater data type Decimal (with decimal places) and all dates and times are assigned the FlowHeater DateTime data type. After loading the fields list, please check manually and if necessary reassign the data types for these fields.

Field attributes

Key field for updating: This checkbox determines whether the field currently marked should be used as a key field when updating. Any number of fields can be regarded as key fields and in combination these form a unique criteria.

Import as text: For fields with the FlowHeater data type String you have the possibility to decide how the value is saved in the Excel cell. By default this option is checked for a FlowHeater data type String and it is imported as text. If you uncheck this option it is also possible to import a formula (e.g. =A1+A2) into the Excel cell that will subsequently be calculated.

Default value: Here you can enter any value that will be used whenever the field is not assigned a value. The default value is also used in cases when it is assigned a value, but this is a zero-length string or NULL. A combination of FlowHeater Parameters and system environment variables can also be used to define a dynamic default value. e.g. %TEMP%\$EXPORT-NAME$.xlsx

 

Adapter settings in the Format tab

The use of the Format tab is described in detail under general information about the usage of Adapters

 

Examples

 

Microsoft®, Windows®, Excel® are registered trademarks of Microsoft Corporation