Query in tDBInput to select multiple files

Highlighted
Six Stars

Query in tDBInput to select multiple files

Hi.

I hope you can help me. I'm new working with TOS and with databases Smiley Embarassed

 

The source of my data are some files (.DBF format) in a folder located in my PC (C:\), it's names can change so I need that my query take every file in that folder. Now, as I know the name files, I have this query:

"SELECT * FROM 20180222AL
union all
SELECT * FROM 20180223AL
union all"

Where "20180222AL" and "20180223AL" are the names of my files.

But, as I said before, in the real life, the names are going to change and I need that my query take all the files in the folder.

¿What can I do?

Thanks!!Woman Happy


Accepted Solutions
Forteen Stars

Re: Query in tDBInput to select multiple files

you could split job into 2 parts - prepare sql, and run sql

 

part 1:

 

tFileList - and iterational prepare statements (need fix final code):

Screen Shot 2018-11-30 at 6.04.40 PM.png

 

in tJavaFlex code:

if (context.sql_text.equals("")){
	context.sql_text="SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE")); 
	}
else {
	context.sql_text=context.sql_text+" \nUNION ALL \n"+"SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE"));
	};

 

 

after first part context.sql_text will contain sql code:

SELECT * FROM cp1251.dbf 
UNION ALL 
SELECT * FROM dbase_30.dbf 
UNION ALL 
SELECT * FROM dbase_8b.dbf 
UNION ALL 
SELECT * FROM dbase_f5.dbf

for all files in folder

 

in a second part just put instead sql code

 

context.sql_code 

without double quotes around

 

 

UPD:

instead of ((String)globalMap.get("tFileList_1_CURRENT_FILE")), you can use ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")) for include full path for files

-----------
Forteen Stars

Re: Query in tDBInput to select multiple files

hi,

 

sql_text cannot be resolved or is not a field

it means - sql_text context variable (and any other), must be declared before used

 

you must define sql_text in context tab:

Screen Shot 2018-12-05 at 7.48.02 AM.png

-----------

All Replies
Forteen Stars

Re: Query in tDBInput to select multiple files

you could split job into 2 parts - prepare sql, and run sql

 

part 1:

 

tFileList - and iterational prepare statements (need fix final code):

Screen Shot 2018-11-30 at 6.04.40 PM.png

 

in tJavaFlex code:

if (context.sql_text.equals("")){
	context.sql_text="SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE")); 
	}
else {
	context.sql_text=context.sql_text+" \nUNION ALL \n"+"SELECT * FROM "+((String)globalMap.get("tFileList_1_CURRENT_FILE"));
	};

 

 

after first part context.sql_text will contain sql code:

SELECT * FROM cp1251.dbf 
UNION ALL 
SELECT * FROM dbase_30.dbf 
UNION ALL 
SELECT * FROM dbase_8b.dbf 
UNION ALL 
SELECT * FROM dbase_f5.dbf

for all files in folder

 

in a second part just put instead sql code

 

context.sql_code 

without double quotes around

 

 

UPD:

instead of ((String)globalMap.get("tFileList_1_CURRENT_FILE")), you can use ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")) for include full path for files

-----------
Six Stars

Re: Query in tDBInput to select multiple files

Hi vapukov,

Thank you for your answer. I have been trying to solve my problem with your suggestion since you answered me but I just couldn’t, so here I am again.
I put in my job the tFileList component with these basic settings:

 Settings tFileList.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Then, the tJavaFlex with these basic settings:Settings tJavaFlex.JPG

 

 

 

 

When I run the job, I get this error (sql_text cannot be resolved or is not a field):Error.JPG

 

 

 

What I’m doing wrong?

 

In addition, I don't know how to set the component tFixedFlowInput. I did it like this, but I don’t know if I did it right:Settings tFixedFlowInput.JPG

 

 

 

I'm really sorry to be so confused, but as I was saying, I'm just beginning to learn from TOS and java programming. Thanks for your help!

 

Forteen Stars

Re: Query in tDBInput to select multiple files

hi,

 

sql_text cannot be resolved or is not a field

it means - sql_text context variable (and any other), must be declared before used

 

you must define sql_text in context tab:

Screen Shot 2018-12-05 at 7.48.02 AM.png

-----------
Six Stars

Re: Query in tDBInput to select multiple files

Thank you! You were very helpful.

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