The MS Excel Adapter is responsible for the import/export and converting of Microsoft Excel® files (XLS, XLSX, XLSM) to CSV or many other formats. The Adapter supports MS Excel (Office) versions 2003, 2007, 2010, 2013, 2016, 2019, 2021 and Microsoft 365.
The Excel Adapter is used to import and export data from and to various data sources. The Adapter also supports the updating of Excel rows in a workbook.
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 - ConfigurationHere you enter the path and filename (absolute or relative) of the MS Excel Workbook. There is no support for wildcards such as *.xlsx here. If you want to process several Excel files in a folder you need to use the Batch Module with the option “/ReadFileName *.xlsx”.
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:
- Overwrite if file already exists: This determines whether or not the Excel Adapter should overwrite an existing Excel file.
- 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.
- 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.
- 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 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).
Moreover, an asterisk "*" can be used as a wildcard for the worksheet name. When a wildcard is used, all worksheets in the Excel workbook that match the pattern will be read as one contiguous worksheet. Obviously, all Excel worksheets to be processed this way must share the same format. The actual worksheet name of the data being processed is returned by the System Heater.
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.
Note: Excel formulas can also be used. If Excel functions are used in a formula, only the English function names may currently be used. Excel function names in other languages are not supported at present.
Allow image import: When you check this option, it will attempt to fetch an image specified by filename in the field to the coordinates of the Excel cell. The image is adjusted to the size of the cell and superimposed on the cell. Note: This means the contents of the cell is not changed. If you check the option under the General tab “Clear the target area before importing” any previously imported images will persist and a renewed import will result in images being repeatedly imported “superimposed” on one another! Warning: The image import option is still experimental – use with caution!
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
Examples
- Importing and exporting an Excel CSV (Beginner)
- MS Excel CSV export example with grouping (Advanced)
- Merging CSV and Excel data using an SQLite database
Microsoft®, Windows®, Excel® are registered trademarks of Microsoft Corporation
