Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
_AnonymousUser
Specialist III
Specialist III

excel with sheets problem

Hi,
I have an excel file with multiple sheets, I don't know each time how many sheets there will be in the file.
How can I iterate the same talend elaboration on each sheet.
I would like to have an output file for each sheet of the excel file.
Thanks in advance for your precious help.
Labels (2)
23 Replies
Anonymous
Not applicable

Hi
Try this
tFileInputExcel(with all sheets checked)---tMap(add an extracolumn(SheetName) having the sheet name
((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET"))---tFileOutput give the filename as
row1.SheetName.psv with append checked.
This will write into sperate files based on sheet name.
YVES1
Contributor III
Contributor III

Hi lijolawrance ,
I don't understand how your solution can be applied to all the sheets of the Excel file.
Could you please detail ?
Thanks
Yves.
Anonymous
Not applicable

Hi im getting the error message while connecting from excel file to database.... field name is not null.. can anyone reply for me.Its is displaying 0 for the remaining column in the excel field.
Anonymous
Not applicable

Hi Yves
I am expecting that all you sheets have same schema.
If yes, If all sheets is checked in tFileInputExcel; talend read all the sheets from the excel in one go no iterate is required.
in the next tMap, add an extra column (SheetName) and value for the column is ((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET")).
This value will change based on sheets name so basically if there are are 5 sheets, the column SheetName will have 5 unique value.
tFileInputExcel--------tMap-------tFileOutput
row1 row2
For writing into output file, you can specify the filename as <Location>\row2.SheetName+<extension> with append option clicked
here a seperate output will be created for each unique value of SheetName column.
Hope it help you
@ravishankar.m
Can you explain what is the error you are getting?
Anonymous
Not applicable

Column 'TIMESLOT' cannot be null
Column 'TIMESLOT' cannot be null
These are the errors i am getting while running the job..and also in my database its taking 0 value for the extra excel column
janhess
Creator II
Creator II

Column 'TIMESLOT' cannot be null
Column 'TIMESLOT' cannot be null
These are the errors i am getting while running the job..and also in my database its taking 0 value for the extra excel column

Don't hijack posts. Try putting this in a new post.
Anonymous
Not applicable

Hi
I am new to talend. actually my requirement is
1) i need to extract some particular field from excelfile to another excelfile. Is it possible. please teach me.
2)what is the usage of data profiler and MDM whether i need to install this both are dont want.
please reply soon
Anonymous
Not applicable

ravishankar.m wrote:
Column 'TIMESLOT' cannot be null
Column 'TIMESLOT' cannot be null
These are the errors i am getting while running the job..and also in my database its taking 0 value for the extra excel column
Anonymous
Not applicable

Hi Yves
I am expecting that all you sheets have same schema.
If yes, If all sheets is checked in tFileInputExcel; talend read all the sheets from the excel in one go no iterate is required.
in the next tMap, add an extra column (SheetName) and value for the column is ((String)globalMap.get("tFileInputExcel_1_CURRENT_SHEET")).
This value will change based on sheets name so basically if there are are 5 sheets, the column SheetName will have 5 unique value.
tFileInputExcel--------tMap-------tFileOutput
row1 row2
For writing into output file, you can specify the filename as <Location>\row2.SheetName+<extension> with append option clicked
here a seperate output will be created for each unique value of SheetName column.
Hope it help you
@ravishankar.m
Can you explain what is the error you are getting?

Hi im ravishankar
Column 'TIMESLOT' cannot be null
Column 'TIMESLOT' cannot be null
These are the errors i am getting while running the job..and also in my database its taking 0 value for the extra excel column
Anonymous
Not applicable

HI
how to do timestamp in talend . can anyone reply for me