topic Re: How to work with multiple sheets of single Excel file using tMap in Talend Studio

How to work with multiple sheets of single Excel file using tMap

Anonymous — Sat, 16 Nov 2024 14:02:02 GMT

Hi, I am new to Talend and will greatly appreciate help from this forum.
I'm trying to use tMap in Open Studio to do data mapping from some Excel sheets to a number of Excel sheets with different schema.
1. I need to have all input Excel sheets in one single input Excel file. But I can't read data from Excel sheet if the Excel file contains multiple sheets. When I create a new FileExcel metadata, after linking the file to my Excel file and "Set sheets parameters" with only one sheet in Step 2 of 4, then in the next wizard page Step 3 of 4, I get the following error. For now I just put each input sheet into a separate Excel file, and it works for me (I can create metadata, pass all steps in New Excel File wizard, etc.)
Last column or First column parameter error
2. I need to write all output Excel sheets into one single output Excel file. I can do it as long as I write to one sheet per subjob. Actually within one subjob, I am able to map to two tFileOutputExcel components. However if I associate the two components to different sheets of the same Excel file, then I get the following errors. (I have set "Append the exist file" option properly -- BTW, it should be spelled as "Append the existing file".) So for now, in order to write to the two sheets of the same Excel file, I have to create another subjob; in one subjob map to one tFileOutputExcel , then in the second subjob map to the other tFileOutputExcel. BTW, if I associate the two components to different Excel files, it also works.
Exception in component tFileOutputExcel_8
jxl.read.biff.BiffException: The input file was not found
at jxl.read.biff.File.<init>(File.java:124)
at jxl.Workbook.getWorkbook(Workbook.java:221)
at jxl.Workbook.getWorkbook(Workbook.java:198)
So right now, I have my temporary workaround, but I expect more. While I'll continue with my learning and exploring, I'll be grateful to any quicky help from the forum.
Thanks.
-Song

Re: How to work with multiple sheets of single Excel file using tMap

Anonymous — Thu, 19 Mar 2009 01:36:31 GMT

Ive parsed multiple sheets from excel-- first check the "all sheets" box in the excel input, then define a schema that has the maximum number of columns you can expect in all of your sheets. If you're using java set the type to String.
Once you can read the entire excel file in this way you can attach it to a tMap. you can retrieve the current sheet name from the excel input by calling sheet_tFileInputExcel_1.getName().toString() Note that the input you are using might have a different internal name. you can find the name by looking at the component tab for the input.
using the sheet name as an output filter for the initial tMap you can define the correct schema's for each sheet.
you can use a variable in the "Sheet Name" field of the tExcelOutput and set up your job to change the sheet name variable at the correct time.

Re: How to work with multiple sheets of single Excel file using tMap

Anonymous — Tue, 04 Jan 2011 13:29:27 GMT

Please John can u expain it in detail i am not getting it

Re: How to work with multiple sheets of single Excel file using tMap

_AnonymousUser — Mon, 20 Mar 2017 10:02:37 GMT

Hi Team,
I need to write data of 10 million into Excel but it does not allow me to do it gives following error
java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowable range (0..1048575)
can i write data in multiple sheets so how job design will look like i have
tsybaseinput ----> tfileexceloutput
simple job

Re: How to work with multiple sheets of single Excel file using tMap

Anonymous — Fri, 27 Apr 2018 10:20:32 GMT

Hi John,

It would helpful for us to understand if you could explain the steps in details.