Excel sheet has 3 sheet with same schema .. i want to make it all on one sheet of excel

[sbxr]

Hi All,

I have Excel file which has 3 sheet with same schema now i want to append or load all data in one sheet of excel and then send it mysql db.

Note:schema name is same but schema size is different in all sheets, all of the sheets has more than 60000 rows..please help me with this.

sheet1                     sheet2

id|name|year          id|name|year

1,sus,2016             3,hello,2017

2,heh,2016             4,tello,2017

Output should be:

all_data-sheet

id|name|year

1,sus,2016 

2,heh,2016

 3,hello,2017

4,tello,2017

[iamabhishek]

@sbxr - there is a option in tFileInputExcel "All Sheets" you have to use that. So, first check the "all sheets" box in the excel input, then define a schema that has the maximum number of columns you can expect in all of your sheets - in your case it's same number so no issues. Once done just connect to tFileOutputExcel and define your schema accordingly. If you need any data transformation you could tMap or other components.

Job Layout - 

[sbxr]

its not happening .. job is starting but transferring row . it is working for small file which i tried. but not with the large file..

[iamabhishek · Accepted solution]

@sbxr - The problem is probably not the number of lines in the input file, but the size of the memory to deal with the content. Do you have any error when the job runs? If yes, what as you just said "its not happening".

Try this though - on the Advanced Settings tab of tFileInputExcel component, there's a Generation Mode setting with two choices: User mode and Event mode.  Event mode consumes less memory and may allow you to read directly from your Excel file.

[sbxr]

Thank you Abhishek...its worked.. error was  because of memory issue.. i used event mode and it worked

Capture11.PNG