topic Re: Read file per file inside folder in Talend Studio

Read file per file inside folder

Anonymous — Thu, 17 Jan 2013 04:32:58 GMT

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

Regards,
Kal

Re: Read file per file inside folder

Anonymous — Thu, 17 Jan 2013 08:54:13 GMT

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

Re: Read file per file inside folder

Anonymous — Fri, 18 Jan 2013 07:40:30 GMT

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

Re: Read file per file inside folder

Anonymous — Wed, 30 Jan 2013 02:21:29 GMT

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

Regards,
Kal

Re: Read file per file inside folder

Anonymous — Wed, 30 Jan 2013 03:19:56 GMT

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

Re: Read file per file inside folder

Anonymous — Tue, 19 Feb 2013 03:22:57 GMT

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

Re: Read file per file inside folder

Anonymous — Tue, 19 Feb 2013 03:54:51 GMT

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

Re: Read file per file inside folder

Anonymous — Tue, 19 Feb 2013 04:10:22 GMT

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

Re: Read file per file inside folder

Anonymous — Tue, 19 Feb 2013 04:52:09 GMT

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
But just wonder about little issue that currently i'm trying to figure it out. I'll come back to you later
Regards,
Kal

Re: Read file per file inside folder

Anonymous — Tue, 19 Feb 2013 06:06:11 GMT

Hi,
OK, feel free to post your issue on forum. You are always welcome!
Best regards
Sabrina

Re: Read file per file inside folder

Anonymous — Mon, 25 Feb 2013 02:58:29 GMT

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

Re: Read file per file inside folder

Anonymous — Mon, 25 Feb 2013 03:37:41 GMT

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

Re: Read file per file inside folder

Anonymous — Mon, 25 Feb 2013 04:20:41 GMT

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

Re: Read file per file inside folder

Anonymous — Mon, 25 Feb 2013 06:44:24 GMT

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

Re: Read file per file inside folder

Anonymous — Mon, 25 Feb 2013 07:09:11 GMT

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

Re: Read file per file inside folder

Anonymous — Tue, 26 Feb 2013 07:32:18 GMT

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

Re: Read file per file inside folder

Anonymous — Tue, 26 Feb 2013 09:20:48 GMT

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

Regards,
Kal

Re: Read file per file inside folder

Anonymous — Wed, 27 Feb 2013 02:37:16 GMT

Hi ae_kel29,
Any new update for your issue?
Best regards
Sabrina

Re: Read file per file inside folder

Anonymous — Wed, 27 Feb 2013 06:35:08 GMT

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

Re: Read file per file inside folder

Anonymous — Thu, 28 Feb 2013 08:05:40 GMT

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
Rgds,
Kal