Dynamic data import / export

datenbank

simply more productive
Up to 100,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.
  • Page:
  • 1

TOPIC: How to select linked records from multiple sources

How to select linked records from multiple sources 2 years 5 months ago #2349

  • Andrew Wojciak
  • Andrew Wojciak's Avatar Topic Author
  • Offline
  • Junior Member
  • 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

Please Log in or Create an account to join the conversation.

Last Edit: by Andrew Wojciak. Reason: attaching a file

How to select linked records from multiple sources 2 years 5 months ago #2350

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

Please Log in or Create an account to join the conversation.

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: by FlowHeater-Team.

How to select linked records from multiple sources 2 years 5 months ago #2351

  • Andrew Wojciak
  • Andrew Wojciak's Avatar Topic Author
  • Offline
  • Junior Member
  • 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

Please Log in or Create an account to join the conversation.

How to select linked records from multiple sources 2 years 5 months ago #2352

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

Attachments:

Please Log in or Create an account to join the conversation.

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.

How to select linked records from multiple sources 2 years 5 months ago #2353

  • Andrew Wojciak
  • Andrew Wojciak's Avatar Topic Author
  • Offline
  • Junior Member
  • 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

Please Log in or Create an account to join the conversation.

Last Edit: by Andrew Wojciak. Reason: attaching a file

How to select linked records from multiple sources 2 years 5 months ago #2354

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

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

Please Log in or Create an account to join the conversation.

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.

How to select linked records from multiple sources 2 years 5 months ago #2355

  • Andrew Wojciak
  • Andrew Wojciak's Avatar Topic Author
  • Offline
  • Junior Member
  • 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_FORECASTtoCONTAINER1.fhd
File Size: 13 KB


File Attachment:

File Name: DTNA_FORECASTtoCONTAINER2.fhd
File Size: 13 KB


File Attachment:

File Name: DTNA_FORECASTtoCONTAINER3.fhd
File Size: 17 KB
Attachments:

Please Log in or Create an account to join the conversation.

Last Edit: by Andrew Wojciak. Reason: adding sqlite file

How to select linked records from multiple sources 2 years 5 months ago #2356

  • FlowHeater-Team
  • FlowHeater-Team's Avatar
  • Offline
  • Administrator
  • Administrator
  • Posts: 268
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_FORECASTtoCONTAINER2.fhd
File Size: 13 KB
Attachments:

Please Log in or Create an account to join the conversation.

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.

How to select linked records from multiple sources 2 years 5 months ago #2357

  • Andrew Wojciak
  • Andrew Wojciak's Avatar Topic Author
  • Offline
  • Junior Member
  • Junior Member
  • Posts: 21
Thank you, Great Help.
AW

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
Moderators: FlowHeater-Team
Time to create page: 0.250 seconds