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.

[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

[grigby]

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]

[grigby]

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]

[lav5248]

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

[lav5248]

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

[grigby]

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.

[grigby]

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.

[lav5248]

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

[lav5248]

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

[lav5248]

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