Dynamic data import / export

datenbank

simply more productive
Up to 100,000 records can be processed with the freeware version!
  • Home
  • News
  • Excel Adapter with support for multiple sheets

Excel Adapter with support for multiple sheets

Excel Adapter - multiple sheet supportExcel Adapter - multiple sheet supportIt is often the case that several worksheets have to be processed from an Excel workbook. Until now, these had to be addressed more or less manually using the Designer or by means of the Batch Module with a static CMD script file. If Excel worksheets were added or removed from the workbook, the CMD script had to be manually adapted to the new conditions before running.

This tedious manual effort has largely been eliminated. As of this version, when specifying the worksheet to be processed in the Excel Adapter, asterisk can be used as a wildcard operator. This means that by simply specifying "*", all worksheets in the Excel workbook will be processed, or for example "Report_*" to select only certain worksheets that match this wildcard pattern.

The data of the individual Excel worksheets are read as if they were one contiguous worksheet. The individual worksheets must of course share the same format. The System Heater can be used to discover the current worksheet name during the Excel export. Thus, the individual Excel worksheets can be recognized and classified during the import process.

Release notes for version 4.2.5 on October 16, 2021

Extended FlowHeater Server email notifications

In an email notification text for the FlowHeater Server it is now possible to include the currently generated file names with placeholders. The placeholder "{CREATED_FILENAMES}" is used to insert all file names generated by the FlowHeater Server task. The individual file names are listed separated by line breaks. Alternatively, placeholders "{CREATED_FILENAME_1}", "{CREATED_FILENAME_2}", "{CREATED_FILENAME_3}" and so forth, can be used in the email notification text to access specific file names. There are as many placeholders available in this way as there are file names created in the FlowHeater Server task.

FlowHeater Server backup and maintenance functions

FlowHeater Server has been extended with new database maintenance functions. As of this version it is possible to backup, restore or repair the underlying FlowHeater Server database. By a FlowHeater Server task, backups can be automated or scheduled.

MySQL Adapter now supports SSH connections

Connecting using a tunneled SSH connection has been supported by the MySQL Adapter for some time. However, until now this feature was not so obvious and had to be enabled manually in the adapter properties. As of this version the MySQL SSH tunnel connection settings can be made directly in the Configurator dialog of the MySQL Adapter.

Looking ahead: For the next version it is planned to implement an option for SSL/TLS certificate-based logins in the MySQL Adapter, to complement the SSH tunnel connection configuration.

TextFile and Excel Adapters with alias field names for dynamic column recognition

The TextFile Adapter and the Excel Adapter already support dynamic column mapping when reading/importing structured text, CSV files or Excel tables. In this way, each column can occur at any position within the current CSV/Excel file and columns are dynamically assigned to the corresponding column names during the data import.
As of this version, several aliases can be assigned per field to identify the respective column. Moreover, there is the possibility to use wildcards in the alias name: e.g. "*number*" will detect fields like "house number", "street and house number" or simply "number".

Note: Case sensitivity is generally ignored in dynamic column detection.

Further enhancements

  • System Heater: can now return the current Excel worksheet name being processed.

  • Now Heater: the FlowHeater output data type is changed to either DATETIME (default), DATE or TIME. This makes it possible to directly use or output only date information (without time) as well as time information (without date) without additional formatting.

  • XML Adapter: further optimization for data grouping to make writing more complex XML structures possible.

  • For error analysis the TRACE file writing has been optimized and extended.

  • Individual processing steps can now be activated or deactivated.

  • XML Adapter, grouping or updates also possible based on XML comments. This possibility can be helpful for data enrichment without violating the given XML schema.

  • Oracle, MySQL, PostgreSQL and SQLite Adapters: third-party database drivers have been updated.

Corrections (bug fixes)