topic Create Multiple Excel Output Files in Talend Studio

Create Multiple Excel Output Files

grigby — Sat, 16 Nov 2024 08:30:47 GMT

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.

Re: Create Multiple Excel Output Files

TRF — Fri, 23 Mar 2018 06:31:35 GMT

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-files-and-zip-at/td-p/40013

Re: Create Multiple Excel Output Files

grigby — Fri, 23 Mar 2018 19:12:33 GMT

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.

Re: Create Multiple Excel Output Files

TRF — Sat, 24 Mar 2018 14:37:41 GMT

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.

Re: Create Multiple Excel Output Files

grigby — Sun, 25 Mar 2018 03:42:33 GMT

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.

Re: Create Multiple Excel Output Files

TRF — Sun, 25 Mar 2018 06:52:44 GMT

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

Re: Create Multiple Excel Output Files

grigby — Mon, 26 Mar 2018 16:31:33 GMT

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)

Re: Create Multiple Excel Output Files

TRF — Mon, 26 Mar 2018 17:37:59 GMT

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

Re: Create Multiple Excel Output Files

grigby — Tue, 27 Mar 2018 13:22:42 GMT

Hi,

Thanks so much for the help and solution!!

Re: Create Multiple Excel Output Files

lav5248 — Thu, 02 Jul 2020 11:35:41 GMT

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

Re: Create Multiple Excel Output Files

lav5248 — Thu, 02 Jul 2020 11:35:41 GMT

Hi,

Re: Create Multiple Excel Output Files

grigby — Thu, 02 Jul 2020 15:00:37 GMT

Hi,

tFileList--(iterate)--> tFileInputExcel--(main)--> tFileOutputExcel

[image: image.png]

In the *tFileList *(Component tab) enter the path where the files are
located in the directory field. Since there are multiple files, use the
*Filemask* "*.csv" value. If there are multiple files, but only one set is
needed then a more restrictive entry such as "FileXXX*.csv" could be used.

[image: image.png]

In the *tFileInputExcel *(Component tab) type, or copy and paste*
((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))* into the file
name/Stream field.

[image: image.png]

In the tFileOutputExcel(Component tab) copy and paste (
*String)globalMap.get("**tFileList_1_CURRENT_FILEPATH")**).replace("xls",
"csv") *into the File Name field.

[image: image.png]

Re: Create Multiple Excel Output Files

grigby — Thu, 02 Jul 2020 15:00:37 GMT

Re: Create Multiple Excel Output Files

lav5248 — Thu, 02 Jul 2020 15:42:26 GMT

Hi Girigby,

Thanks for the detailed explanation. Since i'm new to this tool i need few clarifications too.Kindly bare with me

I did same as what you said, my doubt is, do i need to add schema for the tfileinputexcel component because if i run the job its asking "tfileinputexcel - parameter (sheetlist) must have atleast one value"

Moreover i doubt whether i'm doing it correctly

My input folder has (xlsx) files which has different column and different values for each file and i need that to be converted into "csv" files respective to their file names

tFileList--(iterate)--> tFileInputExcel--(main)--> tFileOutputExcel

Changes made in components file name/stream are:

1. browsed the file location need in the directory field

2. tFileList -> filemask -> "*.csv"

3. tFileInputExcel -> ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))

4. tFileOutputExcel-> String)globalMap.get("tFileList_1_CURRENT_FILEPATH")).replace("xlsx", "csv")

kindly let me know the above one is correct . i need to get it to be converted

Thanks in advance

Re: Create Multiple Excel Output Files

lav5248 — Thu, 02 Jul 2020 15:42:26 GMT

Hi Girigby,

Thanks for the detailed explanation. Since i'm new to this tool i need few clarifications too.Kindly bare with me

Moreover i doubt whether i'm doing it correctly

My input folder has (xlsx) files which has different column and different values for each file and i need that to be converted into "csv" files respective to their file names

tFileList--(iterate)--> tFileInputExcel--(main)--> tFileOutputExcel

Changes made in components file name/stream are:

1. browsed the file location need in the directory field

2. tFileList -> filemask -> "*.csv"

3. tFileInputExcel -> ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))

4. tFileOutputExcel-> String)globalMap.get("tFileList_1_CURRENT_FILEPATH")).replace("xlsx", "csv")

kindly let me know the above one is correct . i need to get it to be converted

Thanks in advance

Re: Create Multiple Excel Output Files

grigby — Thu, 02 Jul 2020 17:29:37 GMT

Hi,

The tFileList Filemask would be "*.xlsx".

The tFileInputExcel
-> ((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"). Correct. You
will need to ensure the "Read excel2007... checkbox is selected. You can
use the "All Sheets" checkbox. The sheet name will match whatever is in the
original file.

In re to the schema on the input file, yes, this would need to be completed.
Correct. tFileOutputExcel->
String)globalMap.get("tFileList_1_CURRENT_FILEPATH")).replace("xlsx", "csv".
Make sure you change the Field Separator to "," for the column entries
otherwise if left as ";" a single column would appear as
Value0;Value1;Value2;Value3.

The original goal of this solution was to rename certain files where the
schema of the received files are all the same and to rename the files to
the xlsx for further processing.

As noted, the files contain different column headers and values, so you may
want to consider using a tFileInputExcel >tMap>tFileOutputDelimited method.
This will allow control over the inputs and outputs using the csv format.

Re: Create Multiple Excel Output Files

grigby — Thu, 02 Jul 2020 17:29:37 GMT

Re: Create Multiple Excel Output Files

lav5248 — Fri, 03 Jul 2020 07:02:32 GMT

Hi ,

Thank you so much i got result as expected.

so as you said i'll try doing the TMAP component for different column headers and values

Thanks much once again

Re: Create Multiple Excel Output Files

lav5248 — Fri, 03 Jul 2020 07:02:32 GMT

Hi ,

Thank you so much i got result as expected.

so as you said i'll try doing the TMAP component for different column headers and values

Thanks much once again

Re: Create Multiple Excel Output Files

lav5248 — Fri, 03 Jul 2020 07:40:30 GMT

got the results for different column headers and values using tmap component as expected