Join two input text files to one output

One Star

Join two input text files to one output

Hello, this is my first post.

I am new with Talend and I am exploring its possibilities. I need to buil a process for my IT Department to get a file transformation as follows:

I receive two files from my customers, one is for Order Headers and the second one is for Order Lines. Our ERP Software needs an unique positional text file with the following format (summarize):

HEADER DATA
LINE DATA
LINE DATA
...
HEADER DATA
LINE DATA
LINE DATA
...

and so.

I am trying to use tAdvancedFileOUtputXML to get an XML file that mix all the data correctly and after that try to build the final text file but I am not very sure about how it works. Is there an easy way to get those two files mixed following the HEADER-LINE sequence? Is XML the best solution?

Thank you in advance.
Employee

Re: Join two input text files to one output

Hi,

Could you please give some details about the 2 input files format ? How do you match detail lines with header ?
One Star

Re: Join two input text files to one output

The two files are quite simple.

They just join through Customer_Number field and Order_Number field. So you can join order lines with their order header through these two fields.

The file format is as follows:

HEADER FILE:

Customer_No
Order_No
Date
Customer Data
...

LINES FILE:

Customer_No
Order_No
Line_No
Item
Quantity
...

Thanks for your time!!
Employee

Re: Join two input text files to one output

Welcome to the Talend community,

You may use 2 tFileInputDelimited and a tMap component to merge your header and lines file.

Use "\n\n" as row delimiter and a single "\n" as field delimiter in the tFileInputDelimited component properties for header and lines file.

Then, join header and lines records in tMap. The lines file is the main flow ( connected first to the tMap ) and the header file is the lookup ( connected last ).

Do not forget to specify the keys in the lookup schema.

the header file :
1
1
2007-08-28
MySql

2
2
2007-08-29
PostgreSQL

the lines file

1
1
1
Item 1
10

1
1
2
Item 2
11

1
1
3
Item 3
12

2
2
1
Item 21
20

2
2
2
Item 22
21

2
2
3
Item 23
22

The result :

Starting job f1263 at 17:14 29/08/2007.
1|1|1|Item 1|10|2007-08-28|MySql
1|1|2|Item 2|11|2007-08-28|MySql
1|1|3|Item 3|12|2007-08-28|MySql
2|2|1|Item 21|20|2007-08-29|PostgreSQL
2|2|2|Item 22|21|2007-08-29|PostgreSQL
2|2|3|Item 23|22|2007-08-29|PostgreSQL
Job f1263 ended at 17:14 29/08/2007. [exit code=0]

Hope it Helps
One Star

Re: Join two input text files to one output

Thank you so much for your help.

I think I didn't explain the problem so clearly, but using your own data it will be easier.

The difficult side of my scenario is that header data and detail data remain separate in the final file.

The resulting layout should be this (I have included strings "HEAD" and "LINE" because the file layout has this kind of strings indeed):
(remember the files are all positional but I will use commas for a clearer view)

"HEAD",date,order_no,customer_no,customer_data (one header record per order)
"LINE",line_no,item,quantity (as many line records as lines in the order)

So for your example, the result should be this:

"HEAD",2007-08-28,1,1,MySql
"LINE",1,Item 1,10
"LINE",2,Item 2,11
"LINE",3,Item 3,12
"HEAD",2007-08-29,2,2,PostgreSQL
"LINE",1,Item 21,20
"LINE",2,Item 22,21
"LINE",3,Item 23,22

I don't know how to define this final file because it has no fixed format for every record because it has HEAD records and LINE records. (Also it has some transmission header and trailing information but this is another story)

That is why I thought about building an XML tree with loops for headers and lines but I am not able to reach that target using tAdvancedFileOutputXML component.

Thank you in advance for your help.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now