Create Multiple Excel Output Files

[grigby]

Hi,

I'm looking for some guidance on the best way to create multiple MS Excel xls or xlsx formatted files, if this is even possible.

The job begins with a tOracleInput, which creates three headers and places the data as needed into an Excel file. However, I need to have multiple xlsx files created, so if there are 5500 rows then six (6) xlsx files are created with no more than 1000 rows per file. This is necessary so the file will meet upload requirements specified by an OEM site, which also does not accept csv files.

[TRF]

Hi,
Use a tFileOutputDelimited with the "Split output in several files" option. Then iterate over the generated files to generate the corresponding Excel files.
You may also refer to this post for a different approach https://community.talend.com/t5/Design-and-Development/tFileOutputDelimited-split-output-in-multiple...

[grigby]

Hi,

Thanks for the input, but I'm not sure I understand the iterate over the
generated files to generate the corresponding Excel files?

This is what the job looks like, and the files are created as expected
(e,g, XXX1, XXX2, XXX3, etc.), but I do not see an iterate option off the
tfileoutput.

[TRF]

So you have created a collection of csv files. Now start à new subjob connected to the previous one with a onSubjobOk trigger. Use a tFileList to iterate the csv files then tFileInputDelimited to read the content of each file and tFileOutputExcel to generate the corresponding Excel files. Use variables from tFileList to get the file names.

[grigby]

Hi,

I'm new to all of this so please be patient. I'm unsure what variable you
are referencing in the tFileList... Do you mean "*.csv" in the filemask?

I used a variable ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))
for the tFileInputDelimited to read all the files in the folder. I'm unable
to use the Iterate from the delimited file to the tFileOutputExcel as shown
in the view below.


If I read each file using the path " C:/.../.../Documents/Output/UDM1.csv"
then UDM2 etc. then each file can be written using a row# (Main) as shown
below.

[TRF · Accepted solution]

That's exactly what you need to do:
tFileList--(iterate)-->tFileInputDelimited--(main)-->tFileOutputExcel
In tFileOutputExcel you just have to replace "csv" by "xlsx" in the tFileList_1_CURRENT_FILEPATH variable to generate the filename :
((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")).replace("csv", "xlsx")

[grigby]

Hi,

I placed the ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH")
).replace("csv","xlsx") in the tFileInputDelimited, but when I run the job
I get:
C:\Users\grigby\Documents\Output\udm0.xlsx (The system cannot find the file
specified)
C:\Users\grigby\Documents\Output\udm1.xlsx (The system cannot find the file
specified)
C:\Users\grigby\Documents\Output\udm2.xlsx (The system cannot find the file
specified)
C:\Users\grigby\Documents\Output\udm3.xlsx (The system cannot find the file
specified)
C:\Users\grigby\Documents\Output\udm4.xlsx (The system cannot find the file
specified)
C:\Users\grigby\Documents\Output\udm5.xlsx (The system cannot find the file
specified)

[TRF]

You don't have to change the filepath in the tFileInputDelimited but in the tFileOutputExcel.

[grigby]

Hi,

Thanks so much for the help and solution!!

[lav5248]

Hi, 

Im basic learner for this talend tool. So in my project i need to convert 10 different excel files which is in one folder into  "csv File(s)" . so can you please help me with the same process step by step . Because i couldn't understand how to configure the three components (tfilelist, tfileinputdelimited, tfileoutputexcel)  you explained