Combine Multiple Excel Files into One Excel File

[Anonymous]

Hello,

I have 5 different input excel files with the same structure( same columns) but different number of rows.. Now I have to combine all of these different excel files into one excel output.

E.g., 1st file has 1 record, 2nd has 2 record, 3rd has 1 record, 4th has 5 records and 5th has 5 records, now I want a single output file with 14 records .

Thanks

[Anonymous]

@skrc0213  use tunite component.

Kindly find the below screenshot for your reference:

Thanks,

Prabuj

[Anonymous]

Thank you so much. It worked. 

[Anonymous]

Hi Prabuj,

One more doubt. I actually have a job that generates these excel files. And in some cases due to filter criteria I might get only 2 or 3 excel files. In that case how can I merge only the excel files generated.

The above solution is working fine as long as there are all 5 excels available. I would need an alternate solution where the output file is generated even if there are 2-3 excels.

thanks

Sumithra Kulkarni

[Anonymous]

@skrc0213  Do the same with 2 or 3 excel, it will work fine.

Thanks,

Prabuj.

/*Don't forget to give kudos*/

[Anonymous]

No this will not work as it is looking for Excel 1, 4 and 5. Because they are not generated by previous job, the current job which unites is failing with a message "Exception in component tFileInputExcel_1 (combine_multiple_excels)
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file input stream from file: 'C:\out\ERP\TCP\MFG\Labor Cost\Automation\Output\test_with_empname\WorkOrderOutput_1_071619.xlsx'
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEnt"

Thanks

Sumithra Kulkarni

[Anonymous]

Hi Sumithra,

I don't understand your question..Can you elaborate ? So i can help you out 

Do you mean to say you want to do the same with 2 to 3 excel files to one output ? or something else.

Thanks,

Prabuj

[Anonymous]

So I have a different job that usually generates excel output files which I am looking to combine.

Sometimes that job might generated only 2 excel files, sometimes 3 excels and sometimes 8 excel files. So the number of files that would be generated is different each time. Still I need to combine what ever output files have generated into 1.   

If I set it to fixed 8 input files and do a tunite as you suggested it is failing when there are only 2 or 3 files available instead of 8. I have uploaded a screenshot. The previous job did not generate the 1st file. So it has failed.

tunite.JPG

[Anonymous]

@skrc0213 check the tfileinputexcel 1 , whether the directory is properly given. 

The error says : Can't open the specified file input stream from file 

'C:\out\ERP\TCP\MFG\Labor Cost\Automation\Output\test_with_empname\WorkOrderOutput_1_071619.xlsx'

Thanks,

Prabuj

[Anonymous]

Yes it is throwing that error because this file is not generated by my previous job. So back to my question. If the number of input excel files is unknown (structure and columns is same) and I want to merge all of them into a single file. What is the approach we can take?