How to select linked records from multiple sources

I would like to link/join and analyse data from many sources (READ side) and create a one output file (WRITE). For example, my input data is in many .txt,.xlxs files as well as in many SQL/DB tables/entities/queries.At the https://flowheater.net/documentation/adapter I have read "For each Definition set there is always a minimum of two Adapters necessary, and at present this is also the maximum." Since 2 adapters is maximum (1=READ and 1=WRITE) how can I combine linked data from multiple inputs, validate, calculate, and create a one output file. Please see attached file for an example of Access query I have created, but would like to do it in Flow Heater. What is the best approach?
Thank you,
AW

Hi Andrew,

By now multiple adapters are allowed on the WRITE side. For READ side of course this feature is planned for a further version.

You could merge/join more than one data sources together with the help of a temporary database. Here you’ll find a detailed example how you could do this with a SQLite database.

Merging CSV and Excel data using an SQLite database

NOTE: For SQLite you do not need an additional database like MS Access , MySQL , MS SQL-Server or so on. SQLite databases can be created with FlowHeater during the Import/Export process.

Thank you for info.

While I was trying to merge CSV and Excel files to SQLite I was not able to use READ Excel adapter since I have no MSExcel installed on the server where my FlowHeater is. Is there any way to overcome this issue without installing Excel on the server and without changing my Excel input file to text?

When loading data from multiple inputs to SQLite there is an option to select a Primary Key, but it seems like I can assign Primary Key to only one column. With my data I cannot do it but would like to assign Primary Key to 2 or more (all) columns. Is it possible?

AW

Hi Andrew,

Export of MS Excel files without an installation of Microsoft Office / Excel is with some restrictions possible. You’ll find a short description here: Read MS Excel files without MS Office installation

Primary Key: Of course you can select more than one Primary Key fields. You have just to select one field and enable the Primary Key option. After this you have to select another field and so on. There’s no maximum limitation!

Afterwards in the Designer all select Primary Key fields are displayed with a key symbol.

Thank you for your help, Robert. I have problem with adding data from the 2nd FD definition to SQLite table.

As a reminder, I want to merge multiple data sources into one SQLite DB.
I did:
Step1: Text-to-SQLite; (no problems)
Step2: FH-Definition: ODBC adapter to SQLite Adapter and I want to make sure relations are:
READ WRITE
ProductNumber======ForcPart
DepotNumber======= ForcSU

I have tried link data in Step2 in a different ways but when I executed Step2 data was not written to the SQLite table. Please see attached file for screenshot when I have tried to export SQLite table to txt file.
No data written from the 2nd Step (just from 1st)

File is attached.
Thank you,
AW

You’re welcome.

For more analytic I need your used FlowHeater Definition files. Could you please post your used files, Thanks.

Hi Robert,

Attached are 3 fhd files and 1 sqlite.
I think the main problem I have is with the 'DTNA_FORECAST to CONTAINER2.fhd' where I would like to append from ODBCAdapter (READ) to my ForcDaimler.sqlite. While appending I need to make sure appended data is joined by
READ...............WRITE
ProductNameShort...ForcPart and
DepotNumber........ForcSU.

Thank you for your assistance.
AW

Hi Andrew,

You just have to define the right Primary Keys fields on the WRITE side for the SQLite Database and of course you have to set the Update Data option in the SQLite Configurator as well, That´s all!
The Primary Key fields are needed to join the right records from ODBC to SQLite .

NOTE: Please be sure that you have linked the Primary Key fields from your READ (ODBC) side to the WRITE (SQLite) side.

I’ve modified your second Definition, please have a look to the attachment.

Thank you, Great Help.
AW