Dynamic data import / export

datenbank

simply more productive
Up to 10,000 records can be processed with the freeware version!
Welcome, Guest
Username: Password: Remember me
Welcome to the FlowHeater support forum.

Questions about using FlowHeater are answered here.

TOPIC: How to select linked records from multiple sources

How to select linked records from multiple sources 1 year 10 months ago #2349

  • Andrew Wojciak
  • Andrew Wojciak's Avatar
  • Offline
  • Junior Member
  • Posts: 21
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 flowheater.net/en/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
Last Edit: 1 year 10 months ago by Andrew Wojciak. Reason: attaching a file
The administrator has disabled public write access.

How to select linked records from multiple sources 1 year 9 months ago #2350

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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.
Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Last Edit: 1 year 9 months ago by FlowHeater-Team.
The administrator has disabled public write access.

How to select linked records from multiple sources 1 year 9 months ago #2351

  • Andrew Wojciak
  • Andrew Wojciak's Avatar
  • Offline
  • Junior Member
  • Posts: 21
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
The administrator has disabled public write access.

How to select linked records from multiple sources 1 year 9 months ago #2352

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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.

Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
The administrator has disabled public write access.

How to select linked records from multiple sources 1 year 9 months ago #2353

  • Andrew Wojciak
  • Andrew Wojciak's Avatar
  • Offline
  • Junior Member
  • Posts: 21
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
Last Edit: 1 year 9 months ago by Andrew Wojciak. Reason: attaching a file
The administrator has disabled public write access.

How to select linked records from multiple sources 1 year 9 months ago #2354

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
You’re welcome.

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

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
The administrator has disabled public write access.

How to select linked records from multiple sources 1 year 9 months ago #2355

  • Andrew Wojciak
  • Andrew Wojciak's Avatar
  • Offline
  • Junior Member
  • Posts: 21
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

File Attachment:

File Name: DTNA_FOREC...NER1.fhd
File Size:13 KB


File Attachment:

File Name: DTNA_FOREC...NER2.fhd
File Size:13 KB


File Attachment:

File Name: DTNA_FOREC...NER3.fhd
File Size:17 KB
Last Edit: 1 year 9 months ago by Andrew Wojciak. Reason: adding sqlite file
The administrator has disabled public write access.

How to select linked records from multiple sources 1 year 9 months ago #2356

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Posts: 240
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.

File Attachment:

File Name: DTNA_FOREC...NER2.fhd
File Size:13 KB
Best wishes
Robert Stark

Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
The administrator has disabled public write access.

How to select linked records from multiple sources 1 year 9 months ago #2357

  • Andrew Wojciak
  • Andrew Wojciak's Avatar
  • Offline
  • Junior Member
  • Posts: 21
Thank you, Great Help.
AW
The administrator has disabled public write access.
Moderators: FlowHeater-Team
Time to create page: 0.093 seconds