[resolved] How to iterate over the sheets of an Excel file

One Star pfd
One Star

[resolved] How to iterate over the sheets of an Excel file

I have many excel files, each containing a varying number of sheets ; all sheets have the same schema. I want to iterate over the sheets of the various files. I can iterate over the files thanks to tFileList.
I tried to iterate over the sheets with a tJavaFlex component, but apparently the tExcelInput component reads all the data during the begin phase. So I cannot compute the sheet name during the main section of the tJavaFlex...
Is there some way to do that ?
Thanks in advance

Accepted Solutions
Employee

Re: [resolved] How to iterate over the sheets of an Excel file

I'm going to give a solution for Perl project users as this topic interest me :-)
In tPerlFlex, the begin part is:
use Spreadsheet::ParseExcel;
my $book = Spreadsheet::ParseExcel::Workbook->Parse(
$_globals{tFileList_1}{CURRENT_FILEPATH}
);
my @worksheets = map { $_->{Name} } @{$book->{Worksheet}};
foreach (@worksheets) {
$_globals{CURRENT_WORKSHEET} = $_;

no main, and the end is:
}

My output is:
Starting job topic747 at 18:06 23/05/2007.
file: file1.xls sheet: sheet1 firstname: Pierrick lastname: Le Gall
file: file1.xls sheet: sheet1 firstname: Philippe lastname: Lobet
file: file1.xls sheet: sheet2 firstname: Richard lastname: Billerey
file: file1.xls sheet: sheet2 firstname: Olivier lastname: Carbone
file: file1.xls sheet: sheet3 firstname: Axel lastname: Maumont
file: file1.xls sheet: sheet3 firstname: Stephane lastname: Mallet
file: file2.xls sheet: sheet1 firstname: Michael lastname: Hirt
file: file2.xls sheet: sheet1 firstname: Nicolas lastname: Rousseau
Job topic747 ended at 18:06 23/05/2007.

All Replies
Employee

Re: [resolved] How to iterate over the sheets of an Excel file

I'm going to give a solution for Perl project users as this topic interest me :-)
In tPerlFlex, the begin part is:
use Spreadsheet::ParseExcel;
my $book = Spreadsheet::ParseExcel::Workbook->Parse(
$_globals{tFileList_1}{CURRENT_FILEPATH}
);
my @worksheets = map { $_->{Name} } @{$book->{Worksheet}};
foreach (@worksheets) {
$_globals{CURRENT_WORKSHEET} = $_;

no main, and the end is:
}

My output is:
Starting job topic747 at 18:06 23/05/2007.
file: file1.xls sheet: sheet1 firstname: Pierrick lastname: Le Gall
file: file1.xls sheet: sheet1 firstname: Philippe lastname: Lobet
file: file1.xls sheet: sheet2 firstname: Richard lastname: Billerey
file: file1.xls sheet: sheet2 firstname: Olivier lastname: Carbone
file: file1.xls sheet: sheet3 firstname: Axel lastname: Maumont
file: file1.xls sheet: sheet3 firstname: Stephane lastname: Mallet
file: file2.xls sheet: sheet1 firstname: Michael lastname: Hirt
file: file2.xls sheet: sheet1 firstname: Nicolas lastname: Rousseau
Job topic747 ended at 18:06 23/05/2007.
One Star pfd
One Star

Re: [resolved] How to iterate over the sheets of an Excel file

Thanks a lot; it worked fine.
In Java, the Start section is
	
final jxl.WorkbookSettings workbookSettings_tSheetList_1 = new jxl.WorkbookSettings();
workbookSettings_tSheetList_1.setEncoding("ISO-8859-15");
final jxl.Workbook workbook_tSheetList_1 = jxl.Workbook.getWorkbook(
new java.io.BufferedInputStream(new java.io.FileInputStream(
((String) globalMap.get("tFileList_1_CURRENT_FILEPATH")))),
workbookSettings_tSheetList_1);
java.lang.String[] sheetNames = workbook_tSheetList_1.getSheetNames();
for (int ixx=0; ixx<sheetNames.length; ixx++) {
String theSheet = sheetNames;
// I don't want these sheets
if ("Resultats_2006".equals(theSheet)) continue;

globalMap.put("CURRENT_SHEET", theSheet);

There is no Main section. The End section contains only "}".
One Star

Re: [resolved] How to iterate over the sheets of an Excel file

This Java code works very well, thank you very much!
One Star

Re: [resolved] How to iterate over the sheets of an Excel file

Just what I was after ta.
I had to tLibraryLoad jxl.jar. (I think this is because I'm not using a tFileInputExcel yet and therefore jxl was not loaded).
"Exception in thread "main" java.lang.NoClassDefFoundError: jxl/WorkbookSettings at *******.summary_0_1.Summary.tFileList_1Process(Summary.java:671)"
I also added "WBS_1.setSuppressWarnings(true);" to the code as I was getting, loads of warnings;
"Warning: Usage of a local non-builtin name
Warning: Property storage name for 5 is empty - setting to Root Entry"
Cheers
Four Stars

Re: [resolved] How to iterate over the sheets of an Excel file

Hi moinerus,
On componentOk link would not help many times, if the further tasks are dependent on the status of completion of previous task. If the library is responsible for further task execution, then convert link to OnSubjobOk and then check once again..
Thanks
Vaibhav
One Star

Re: [resolved] How to iterate over the sheets of an Excel file

Vaibhav as the jxl Lib is build in and loaded with other components, I'm happy its going to load.
Good point though, I do usually use OnSubjobOK must have miss clicked.
Cheers Andy
Seventeen Stars

Re: [resolved] How to iterate over the sheets of an Excel file

hi all,
difference between onComponentOk and OnSubjobOk is due to how Talend process the 3 different part of component (start,main,end).
for the start part Talend begin to check each component starting by the last one (usually a write - output component).
Then launch the main part (the flow)
And finish by closing each end part of component until the end.
a component could be finished before whole subjob.
you can easily view those 3 part in tJavaFlex component that is a pattern (squeletor Smiley Happy component ( without ui).
for a subjob with a single component both (component or subjob ok) are the same Smiley Wink
regards
laurent
Four Stars

Re: [resolved] How to iterate over the sheets of an Excel file

Hi Laurent,
For single components like tFTPConnection, tFTPget, tFTPut, tDBconnection,tLibraryLoad components, whether both (onSubJobOk and onComponentOk) are same?
If it is the same, then it defies the sole purpose of component usage and their link creation... We must raise a change request to Talend...
tPreJob and tPostJob components does not have onSubJobOk, If and Main links, it only has onComponentOk...
Can you pl explain to me about this?
Vaibhav
Seventeen Stars

Re: [resolved] How to iterate over the sheets of an Excel file

yes it's the same if a subjob has only one component.
see prejob & postJOb has a graphical starting&ending point telling you : do something before anything else (init part) for the tPreJob and close properly job for tPostJob (close connection, clear buffer,etc).

regards
laurent
Four Stars

Re: [resolved] How to iterate over the sheets of an Excel file

Hi Laurent,
Thank you for clarification. I have updated my understanding regarding this. To make it more clear, I used tSleep for 5 second and used three links... After 5 seconds all three links executed one after another. First OnComponentOk then RunIf and then OnSubjobOk
Thanks
Vaibhav
Seventeen Stars

Re: [resolved] How to iterate over the sheets of an Excel file

you're welcome Smiley Wink