Six Stars

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Eight Stars

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

@sushilbuxar - 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.

Six Stars

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

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

4 REPLIES
Eight Stars

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

@sushilbuxar - 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 - 

tFileInputExcel-1.JPG

Six Stars

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

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

Eight Stars

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

@sushilbuxar - 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.

Six Stars

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

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