One Star

Read file per file inside folder

Hi,
I just wonder how do Talend read file inside specified folder and then do extraction as usual. After finished data extraction, move that file into another folder.
The scenario is like this, I want to extract data from excel file and load into mssql. However, there are several files of excel and I put those files inside "Folder_In" folder. Also, there may be another excel files will be added into "Folder_Input" and need to be extracted.
After that excel files extracted, that file will be moved and saved into another folder "Folder_Out". Is it possible for Talend to do this?
Plz help me Smiley Sad

Regards,
Kal
24 REPLIES
Moderator

Re: Read file per file inside folder

Hi,
The scenario is like this, I want to extract data from excel file and load into mssql. However, there are several files of excel and I put those files inside "Folder_In" folder. Also, there may be another excel files will be added into "Folder_Input" and need to be extracted.

We need confirm one thing. Are there two folder "Folder_In" and "Folder_Input" as the resource file or just one?
Waiting for your confirmation.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Moderator

Re: Read file per file inside folder

Hi, ae_kel29
I have designed a demo job for your requirement.
tFilelist-->tFileInputDelimited-->tLogrow-->tMySqlOutput
|
tFileCopy
See my pics for details
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi,
For any delays in response your inquiry. There are two folder with name "Folder_In" and "Folder_Out". "Folder_In" is used to stored the excel files which the data of each files will be extracted and once a file has competed extracted, that file will be moved-out and will be stored in "Folder_Out".
Is it possible i create etl job to do this situation? Plz help me Smiley Sad

Regards,
Kal
Moderator

Re: Read file per file inside folder

Hi,
Don't worried about that, it is possible you can create such a ETL job to meet your needs.
There are two folder with name "Folder_In" and "Folder_Out". "Folder_In" is used to stored the excel files which the data of each files will be extracted and once a file has competed extracted, that file will be moved-out and will be stored in "Folder_Out".

For your requirement, i have designed a demo job to help you.
See the related screenshots for details.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi,
I don't understand the function of tFileInputDelimited since inside my TEST_FOLDER_IN, there are several excel file that i wants to extract. See image. How do i wants to set the setting for tFileInputDelimited?
Regards,
Kal
One Star

Re: Read file per file inside folder

Hi,
I tried to set up my job followed yours, but there's an error hit saying that no schema found. Is there any schema required to be defined on tFileInputDelimited?
Regards,
Kal
Moderator

Re: Read file per file inside folder

Hi,
Sorry for the delay.
The function of component tFileInputDelimited is receiving these files in the folder. You must define schema in tFileInputDelimited so that it can get the content of file.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi,
I think i found why, it is because my source files were excel files. I've changed to used tFileInput Excel and put the directory path ((String)global... just like you and it successfully extract the files Smiley Happy
But just wonder about little issue that currently i'm trying to figure it out. I'll come back to you later Smiley Happy
Regards,
Kal
Moderator

Re: Read file per file inside folder

Hi,
OK, feel free to post your issue on forum. You are always welcome!
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi,
I just wonder, i have 2 files with same format inside a folder which refer to year 2006 and 2009 and i wants to extract these files automatically by using tFileList. I was successfully extracted the data each year with respective values but the year was replicate to 2006 even though the values are for year 2009.
Regards,
Kal
Moderator

Re: Read file per file inside folder

Hi,
I just wonder, i have 2 files with same format inside a folder which refer to year 2006 and 2009 and i wants to extract these files automatically by using tFileList. I was successfully extracted the data each year with respective values but the year was replicate to 2006 even though the values are for year 2009.

I don't understand your requirement very well. Could you set a example for your source file format? and your expect file format? Thanks a lot.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

I'm sorry for any difficulties. Hope you can understand my requirement by the following example. I have 2 excel files that contain table id, year, and values.
1st Excel
--------------
Table10|2009|11
Table10|2009|12
Table10|2009|13
2nd Excel
--------------
Table10|2009|22
Table10|2009|21
Table10|2009|20

Expected Result
--------------
Table10|2009|11
Table10|2009|12
Table10|2009|13
Table11|2006|22
Table11|2006|21
Table11|2006|20
But, after execute the job, i got the output as:
Current Result
--------------
Table11|2006|11
Table11|2006|12
Table11|2006|13
Table11|2006|22
Table11|2006|21
Table11|2006|20
You can see the table id and year was repeated. The files are the same format and structure and the only different is the value of each years. Hope you can understand the requirement.
Regards,
Kal
Moderator

Re: Read file per file inside folder

Hi,
I have got your requirement clearly and need take a testing for your case, and then give you the response asap.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi,
Thanks Sabrina for your concern. Btw, there's a little correction. Here is the correct info and I'm sorry for any mistype.
1st Excel
--------------
Table10|2009|11
Table10|2009|12
Table10|2009|13
2nd Excel
--------------
Table11|2006|22
Table11|2006|21
Table11|2006|20

Expected Result
--------------
Table10|2009|11
Table10|2009|12
Table10|2009|13
Table11|2006|22
Table11|2006|21
Table11|2006|20

Current Result
--------------
Table11|2006|11
Table11|2006|12
Table11|2006|13
Table11|2006|22
Table11|2006|21
Table11|2006|20

Regards,
Kal
Moderator

Re: Read file per file inside folder

Hi ae_kel29,
I have designed a demo job for your issue. The work flow is tFileList-->tFileInputEXCEL-->tLogRow and the job works well.
See my following screenshots

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi,
Thanks Sabrina for your findings. Let me check my ETL job and will come back to you soon.

Regards,
Kal
Moderator

Re: Read file per file inside folder

Hi ae_kel29,
Any new update for your issue?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi Sabrina,
Thanks for your concern. I still try to amend my ETL job. Perhaps the way i built the job make the data mismatched. I'll come back to you later for any update.
Rgds,
Kal
One Star

Re: Read file per file inside folder

Hi,
I have 2 files and i used tFileList to extract both files and some components to get the expected output but i got little issue since there's missing value after execute the job. See below example:
File 1
--------
AAAA 2006
aaaa 2006
File 2
--------
BBBB 2009
bbbb 2009

Expected Output
-------------------
AAAA 2006 | aaaa 2006 | 2006
BBBB 2009 | bbbb 2009 | 2009

Current Output
-----------------
AAAA 2006 | null | 2006
BBBB 2009 | bbbb 2009 | 2009

Plz help me Smiley Sad
Rgds,
Kal
Moderator

Re: Read file per file inside folder

Hi ae_kel29,
I doubt that there are some extra spaces in your source file.
Do you use tfileinputdelimited component? checkout the option "Trim all columns" and "check each row structure against schema" in Advanced settings to see if this issue still repro.
See pic
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi,
I used tFileInputExcel and already checked the "Trim all column". See my job
Moderator

Re: Read file per file inside folder

Hi,
I think the issue don't exist in tfilelist-->tfileinputExcel work flow.Because I have a demo job as yours(see pic). Suppose that something wrong with the component Extract row and tMap. How do you set these component? Could you share it on forum.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Read file per file inside folder

Hi,
Thanks for your response. But maybe the explanation below may help you:
File 1
--------
AAAA 2006
aaaa 2006
File 2
--------
BBBB 2009
bbbb 2009

Expected Output
-------------------
AAAA 2006 | aaaa 2006 | 2006
BBBB 2009 | bbbb 2009 | 2009

Current Output
-----------------
AAAA 2006 | null | 2006
BBBB 2009 | bbbb 2009 | 2009

You can see my expected result and current result that i want. What you've done is direct load meanwhile if you carefully take a look at my expected result, there's something i split out.
Rgds,
Kal
One Star

Re: Read file per file inside folder

Hi,
I have six different excel files in a folder and each file contains different data.Each files data must be loaded into different tables in MSSQL. The folder maybe loaded with new files with the same name frequently.I want a job in Talend such that the files are loaded automatically into MSSQL whenever triggered.Is this possible in Talend. I am new to Talend and thus need help in it.
Please help me out with this. :/
Regards,
Ritesh.