Five Stars

Create Multiple Excel Output Files

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Thirteen Stars TRF
Thirteen Stars

Re: Create Multiple Excel Output Files

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")

TRF
Five Stars

Re: Create Multiple Excel Output Files

Hi,

 

Thanks so much for the help and solution!!

8 REPLIES
Thirteen Stars TRF
Thirteen Stars

Re: Create Multiple Excel Output Files

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

TRF
Five Stars

Re: Create Multiple Excel Output Files

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.


Thirteen Stars TRF
Thirteen Stars

Re: Create Multiple Excel Output Files

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.

TRF
Five Stars

Re: Create Multiple Excel Output Files

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.



Thirteen Stars TRF
Thirteen Stars

Re: Create Multiple Excel Output Files

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")

TRF
Five Stars

Re: Create Multiple Excel Output Files

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)



Thirteen Stars TRF
Thirteen Stars

Re: Create Multiple Excel Output Files

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

TRF
Five Stars

Re: Create Multiple Excel Output Files

Hi,

 

Thanks so much for the help and solution!!