Excel Adapter with update and lookup functions

The Excel Adapter has undergone a complete overhaul for this version and now sports a host of new features. For instance it is now possible to perform updates of Excel rows/records in a similar way to the database Adapters and you can choose whether data is inserted, updated or both options used in combination. According to key fields you define, the Excel Adapter checks whether the record exists for updating or else will insert it into the sheet. The target area of the sheet for imported data can optionally be cleared before the import starts. This makes sure that when the imported amount of data is shorter than a previous import, no redundant data is left behind in the Excel sheet.

Heater Lookup - Excel VLookupAnother new feature is that the Excel Adapter can be used in combination with the Lookup Heater. In essence this performs the Excel function VLOOKUP on the spreadsheet. The only and rather cumbersome workaround previously available to search and replace data in the Definition was first to export it to a CSV file and then perform a CSV Lookup on this using the String Replace Heater. As of this version such a workaround is no longer necessary.

But that’s not all, further enhancements include:

• The Excel Adapter now supports dynamic generation of several Excel workbooks and sheets using Parameters. If the workbook filename or the name of the sheet is changed during an export, then a new Excel workbook or a new sheet is created.
  
  
• In a Definition the same Excel workbook can now be read and written in different sheets.
  
  
• Optional formatting of column headings in bold.
  
  
• It is now possible to define whether data of the FlowHeater data type String is to be imported as simple text or as an Excel formula or function.

Release notes for version 4.0.9 on August 26, 2017

Further changes

New Base64 Heater

The Base64 Heater enables saving (binary) data coded and decoded using Base64. This makes it is possible, for example, to embed binary data (pictures, executable programs, etc.) in CSV text files without generating the control characters that can confuse editors.

FlowHeater Server moving of files with/without timestamp

For tasks of the data monitoring type it is now possible to choose whether after completion of a file move task that a timestamp is added to the filenames or not.

Improved selection of databases and tables in all database Adapters

The selection fields for databases, tables, queries and stored procedures now supports auto-completion to enable more efficient selection in the case of large databases with many tables.

Improved error reporting in Definitions with multiple Adapters

In addition to reporting the applicable field name in errors and warnings, the relevant Adapter name is now also quoted.

Lookup Heater now supports the Excel Adapter

The Lookup Heater can now be used in conjunction with the Excel Adapter. In this case the Excel function VLOOKUP is used. After choosing which Adapter should be used the sheet name with an area or range specification that applies for the lookup.

e.g. SheetName!A1:B100

The column index that should be returned from this range can be specified in the “Field” text entry line.

Bug fixes

• Memory Mode: no longer skips the Heaters/Functions performed only once at the end of run.
  
  
• Designer: corrected when moving a Heater on the Designer area with the CTRL key held down.
  
  
• MySQL Adapter: error when reading VIEWS is fixed. The selection area for tables now also displays queries/views.
  
  
• TextFile Adapter: the preview window was disrupted when line breaks were embedded in the text. Instances of line breaks are now replaced by {CRLF}, {CR} or {LF} in the preview window.
  
  
• FlowHeater Server: there were problems when spaces were included in the path specification for running .FHD or .FHRT files.
  
  
• FlowHeater Server / Batch Module: error fixed that occurred when running a Definition in which an Excel Adapter was on both READ and WRITE sides.

Release notes for version 4.0.9.1 on September 2, 2017

Bug Fix

• Excel Adapter: The reported error “FlowHeater.Core.Adapter.ExcelAdapter contains no Definition for ExcelIsEmpty” has been corrected. This error was only encountered when attempting to use the new Excel Update function.
  
  
• FlowHeater Parameters: optimized saving of Parameters. Only the explicitly defined Parameter is saved, so that this continues to be available for the next run. Previously, whenever a Parameter was defined for saving, all the other Parameter values were also saved at the same time.

Release notes for version 4.0.9.2 on September 9, 2017

Updated “third-party” database drivers

With this version the “third-party” database drivers used by the MySQL, Oracle, PostgreSQLand SQLite Adapters are updated.

Bug Fix

• Access Adapter: corrected for the opening of old Access 97 (MDB, MDE) database files using the 32-bit Microsoft.Jet.OLEDB.4.0 driver.
  
  
• Excel Adapter: corrected for when reading and writing the same Excel workbook. Previously in this case some of the changes made went unsaved.

Release notes for version 4.0.9.3 on September 16, 2017

Database Adapters – optimized Load Schema feature

As of this version the loading of table schema information is optimized substantially. Previously with tables, views or complex queries where many rows were involved that loading the schema information sometimes resulted in timeouts.

Bug Fix

• Excel Adapter - Correction to Test Mode. Also for test runs an Excel workbook on the WRITE side was erroneously being written to or amended.