Dynamically Create Files - Iterate over List from Query

Seven Stars

Dynamically Create Files - Iterate over List from Query

I have two tables Cars and CarParts (see sample code and data below).

 

What I would like to do within Talend is to iterate over the list of Cars and write out all the CarParts for each car into a separate file.

 

For example:

I have following Car in my car table:

'Corolla','A1111'

 

For Car Code 'A1111' I would like to retrieve all the parts and write the records to a csv file

and do then move on the next car in my Cars table till I have processed all the cars.

 

Just wondering how can I do this in Talend.

 

Here is the sample code for both tables:

 

create table cars(
	car varchar(10),
	carcode varchar(5)
	)

insert into cars values('Corolla','A1111')
insert into cars values('Honda','B1112')
insert into cars values('C300','C1113')
insert into cars values('Camry','D1114')



create table carparts(
	carcode varchar(5),
	partnum int,
	partname varchar(10)
	)

insert into carparts values ('A1111',1,'Tires')
insert into carparts values ('A1111',2,'Wheels')
insert into carparts values ('A1111',3,'Seats')
insert into carparts values ('A1111',4,'Headlight')
insert into carparts values ('A1111',5,'Mirror')

insert into carparts values ('B1112',1,'Tires')
insert into carparts values ('B1112',2,'Wheels')
insert into carparts values ('B1112',3,'Seats')
insert into carparts values ('B1112',4,'Headlight')
insert into carparts values ('B1112',5,'Mirror')

insert into carparts values ('C1113',1,'Tires')
insert into carparts values ('C1113',2,'Wheels')
insert into carparts values ('C1113',3,'Seats')
insert into carparts values ('C1113',4,'Headlight')
insert into carparts values ('C1113',5,'Mirror')

insert into carparts values ('D1114',1,'Tires')
insert into carparts values ('D1114',2,'Wheels')
insert into carparts values ('D1114',3,'Seats')
insert into carparts values ('D1114',4,'Headlight')
insert into carparts values ('D1114',5,'Mirror')

thanks

Tags (1)
Eight Stars

Re: Dynamically Create Files - Iterate over List from Query

Hi @talendstar ,

 

I have created couple jobs to do what you want. The design of job 1 is Screen Shot 2019-08-28 at 4.47.02 PM.png

and job2 with a context variable as shown belowScreen Shot 2019-08-28 at 4.48.48 PM.png

 

what is done here is .. the first jobs does a select * from Cars tables, passes it to tFlowToIterate and for each row in cars table job2 is executed once passing the carcode.Screen Shot 2019-08-28 at 4.51.02 PM.png

above picture shows passing carcode to job2 in tRunJob.

And the select query in tDBInput1 in job2 is

 

Screen Shot 2019-08-28 at 4.52.32 PM.png

and I write the output from this component to a csv file where I use the carcode in the context variable as the name of the file being createdScreen Shot 2019-08-28 at 4.53.33 PM.png

And the output is 4 different files for each carcode with its corresponding parts.Screen Shot 2019-08-28 at 4.54.43 PM.png

 

Hope this helps!!

Let me know if there is any problem

 

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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog