[resolved] Creating multiple output tables using the contents of a Database table

One Star

[resolved] Creating multiple output tables using the contents of a Database table

Hi all,

Just wondering if you can help me with the following problem.

I have a table containing 2 columns
1. f_name
2. f_code

There is multiple rows in this table (94 rows).

I am trying to create 1 file per row. Each file should be named using f_name and the contents of this file should contain f_code. Can anyone tell me if this is possible and how I might go about it. I have tried everything, however all I can manage is to create 94 rows with the correct file name - they're all empty though.

I can't seem to upload images however the process for achiving 94 empty files is as follows.

tPostgresqlInput - read in table with 2 columns
tOutputExcel - output the contents of this table to excel file because tFlowToIterate does not seem to work with a DB table.
tFlowToIterate
tFileInputDelimited - generate a file using the f_name as it's name (String)globalMap.get("row2.f_name")+".csv"
tFileOutputDelimited - output file.

How can I get the f_code into these files as its' created or does it have to be done after?
Thanks in advance,


Face

Background to problem. I am trying to create a job to automate the generation of a file per function that I have listed on my Postgresql DB. I have assembled the function using the pg_catalog table and associated fields in Postgresql and stored the output into a table with two columns. The idea of this process and the multiple files is that it will allow me to easily update my SVN repository at the most granular level.
One Star

Re: [resolved] Creating multiple output tables using the contents of a Database table

Hi

Try to use this way
tPostgresqlInput----main-->tJavaRow(Save the columns in the context)-------main----tFlowToIterate----Iterate--->tFileTouch(File Name as context.f_name+".csv"+-->tJavaRow--->tFileOutputDelimited

This will give your required output.
One Star

Re: [resolved] Creating multiple output tables using the contents of a Database table

thanks for getting back to me Lijolawrance.

Can you give me a bit more information on how to save the columns to context. I've never done this before?

thanks in advance,


Face
One Star

Re: [resolved] Creating multiple output tables using the contents of a Database table

Hi

in tJavaRow1 first press generate code, after that add

context.f_name=input_row.f_name;

this will save the your f_name in context.
One Star

Re: [resolved] Creating multiple output tables using the contents of a Database table

Thanks for getting back so quick lijolawrance. Still can't seem to get it all to work though.

WORKING.
I've entered in "/home/face/Desktop/test/"+context.f_name+".txt" into the tFileTouch_1 component and this seems to create 94 empty files with the f_name as the file name. This is fine however it only works when I have the last two components turned off i.e. tJavaRow --> tFileOutputDelimited

NOT WORKING
If I activate the last 2 components then the tFileTouch_1 component brings up the red "x" to indicate and error. The error states "If this component has output, there must be an input link to propagate the data".
Finally, how will I get the "f_code" to be the contents of these files?

I have a context group set up containing 2 context variables "f_name" and "f_code". The first tJava_row component contains the following code.
//Code generated according to input schema and output schema
output_row.f_name = input_row.f_name;
output_row.function_code = input_row.function_code;
context.f_name = input_row.f_name;
context.f_code = input_row.function_code;

Thanks in advance.


Face
One Star

Re: [resolved] Creating multiple output tables using the contents of a Database table

Hi Face

I have attached a sample screenshot for your help. Instead of paloinput i have used tFileInputDeleimited.

Have a look at both the tJavaRow. I will help you.
One Star

Re: [resolved] Creating multiple output tables using the contents of a Database table

Nice one LijoLawrance, got it working now. Cheers for your help and the detail.

Regards,


Face

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

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch