Four Stars

[resolved] split large excel file to 2 or more excel files

Hi there,
 i need to split a large excel file >5mb into smaller files .
Please help!
Thanks in adv,
Hachi
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

Re: [resolved] split large excel file to 2 or more excel files

Hi,
There is no "split" option in tfileoutputexcel component.
You can tranfer your output delimited files into excel files.
tfileinputexcel-->tfileoututdelimited(split)-->tfilelist(loop through your splitted files)-->further processing(excel output, DB)
                  

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.
14 REPLIES
Moderator

Re: [resolved] split large excel file to 2 or more excel files

Hi,
There is an option 'Split output in several files' in the advanced setting tab of tFileOutputDelimid. You can generate N rows in each 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.
Four Stars

Re: [resolved] split large excel file to 2 or more excel files

Hi,
There is an option 'Split output in several files' in the advanced setting tab of tFileOutputDelimid. You can generate N rows in each file.
Best regards
Sabrina

Hi Sabrina,
can the splitted output file be excel ..
large excel file --- 2 excel files.
Moderator

Re: [resolved] split large excel file to 2 or more excel files

Hi,
There is no "split" option in tfileoutputexcel component.
You can tranfer your output delimited files into excel files.
tfileinputexcel-->tfileoututdelimited(split)-->tfilelist(loop through your splitted files)-->further processing(excel output, DB)
                  

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.
Four Stars

Re: [resolved] split large excel file to 2 or more excel files

Hi,
There is no "split" option in tfileoutputexcel component.
You can transfer your output delimited files into excel files.
tfileinputexcel-->tfileoututdelimited(split)-->tfilelist(loop through your splitted files)-->further processing(excel output, DB)
                  

Best regards
Sabrina

Thanks sabrina.. Smiley Happy this hepls!
Moderator

Re: [resolved] split large excel file to 2 or more excel files

Hi Hachi,
Great it is working. Let us know if there is any further help we can give.
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: [resolved] split large excel file to 2 or more excel files

Hi,
I am new on Talend. I want to split a large excel file.
I did not understand how to use: tfileinputexcel-->tfileoututdelimited(split)-->tfilelist(loop through your splitted files)-->further processing(excel output, DB)
Please, can you explain exactly what to?
Thanks.
Regards,
El Hassan
Moderator

Re: [resolved] split large excel file to 2 or more excel files

Hi,
 Do you want to split your big excle file into several delimited files or excel files?
 Due to "split" option will be available in tfileoutputdelimited component. If you want to get several delimited files, you can just use tfileoututdelimited to achieve it.
The work flow should be: tfileinputexcle-->tfileoutputdelimited(In advanced settings, here is an "split output in several files" option).
If you want to get several excel files, you have to transfer your output delimited files into excel files firstly.
The solution was provided as above. 
There are two subjobs:
1: Split your big excel file into several delimited files
2: Transfer your output delimited files into excel files
tfileinputexcel-->tfileoututdelimited(split)-->onsubjobok-->tfilelist(loop through your splitted delimited files)-->further processing(excel output, DB)
Let us know if it is Ok with you.
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: [resolved] split large excel file to 2 or more excel files

Hello Sabrina,
Thank you for your replay.
I want to split an excel file into several excel files.
I already split the big excel file "DFC_Chiffrage_Supplier_Export.xls" into several delimited files(DFC_Chiffrage_Supplier_Export0.xls, DFC_Chiffrage_Supplier_Export1.xls, DFC_Chiffrage_Supplier_Export2.xls and DFC_Chiffrage_Supplier_Export3.xls).
I can not transfer the output delimited files into excel files.
Please, how can I do that, step by step? I m new on Talend Smiley Sad
Thanks.
Regards,
El Hassan
Moderator

Re: [resolved] split large excel file to 2 or more excel files

Hi,
You can use tfilelist to retrieve your several delimited files from a folder and output them through tfileoutputexcel.
The workflow should be:tfilelist(using file mask as *.csv)-->tfileinputdelimited->tfileoutputexcel.
Here is a component reference about:TalendHelpCenter:tFileList
To make it clear, I have designed a simple demo job for you. Please take a look at my screenshots for details.
Feel free to let us know if it is OK with you.

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: [resolved] split large excel file to 2 or more excel files

Hi,
It's working Smiley Happy
Thanks Sabrina.
Regards,
El Hassan
Moderator

Re: [resolved] split large excel file to 2 or more excel files

Hi El Hassan,
Great it works. Feel free to let us know if there is anu further help we can give.
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: [resolved] split large excel file to 2 or more excel files

I am getting the following error when converting from csv to xls. connecting to socket on port 4042
connected
Exception in component tFileOutputExcel_2
java.io.FileNotFoundException: D:\Public\talend output test\Testing\D:\Public\talend output test\Testing\B0.csv.xls (The filename, directory name, or volume label syntax is incorrect)
at java.io.FileOutputStream.open(Native Method)
at java.io.FileOutputStream.<init>(FileOutputStream.java:221)
at java.io.FileOutputStream.<init>(FileOutputStream.java:110)
at project.doublecheck_0_1.doublecheck.tFileList_1Process(doublecheck.java:555)
at project.doublecheck_0_1.doublecheck.runJobInTOS(doublecheck.java:1024)
at project.doublecheck_0_1.doublecheck.main(doublecheck.java:881)
disconnected
Job doublecheck ended at 17:49 09/01/2017.
Four Stars

Re: [resolved] split large excel file to 2 or more excel files

Hi Xdshi,
I've similar job that gets data from DB and writes to tfileoutputdelimit, as the volume is 1M records, wants write to small files with 1000 records. when the job  runs it creates 100 files in 2 mins but it wont stop there, it keeps overwriting multiple times almost like 50+ times before job completes successfully.
Why is it overwriting so many times, is tehre anything wrong in my configuration or settings. please help. 
thanks and regards  
Moderator

Re: [resolved] split large excel file to 2 or more excel files

Hi preddy79,
Is there any error message printed on console?  On which build version you got this issue?
Screenshots of your job setting will be preferred.
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.