One Star

Metadata File Excel which contains index formula and data validation

Hi,
I would like to create metadata for my excel file, my excel file contains many different sheets such as "geography", "program". "Input" sheet contains data validation which data validation list use hidden column in this "input" sheet, after column with data validation is selected then it will generate result with this formula =INDEX(pro_code_list,MATCH(L12,pro_desc_list,FALSE),1) in the next column.
My problem is can I create metadata excel file by selecting only the "Input" sheet because when I do like that, there is an error:
Exception in component tFileInputExcel
java.lang.RuntimeException: Special sheets not exist!
at budgetfs.shadowfileinputtodelimitedoutput_0_1.ShadowFileInputToDelimitedOutput.tFileInputExcelProcess(ShadowFileInputToDelimitedOutput.java:666)
at budgetfs.shadowfileinputtodelimitedoutput_0_1.ShadowFileInputToDelimitedOutput.runJobInTOS(ShadowFileInputToDelimitedOutput.java:1055)
at budgetfs.shadowfileinputtodelimitedoutput_0_1.ShadowFileInputToDelimitedOutput.main(ShadowFileInputToDelimitedOutput.java:947)
Preview error. Some settings must be changed.
Note: Preview errors are generally due to a wrong encoding setting.
org.talend.designer.runprocess.shadow.ShadowFilePreview.preview(ShadowFilePreview.java:90)
org.talend.repository.ui.utils.ShadowProcessHelper.getCsvArray(ShadowProcessHelper.java:385)
org.talend.repository.ui.wizards.metadata.connection.files.excel.ExcelFileStep2Form$PreviewProcessor.nonUIProcessInThread(ExcelFileStep2Form.java:969)
org.talend.commons.ui.swt.thread.SWTUIThreadProcessor$1.run(SWTUIThreadProcessor.java:74)
But when I choose all sheets, in preview I can see only the first two sheets. So how can I define schema for only the last excel sheet?
Best ragard,
Sophanna
3 REPLIES
Seventeen Stars

Re: Metadata File Excel which contains index formula and data validation

the wizard cannot extract type data from a formula field because the Apache POI API return formula fields always as String. I would not use the Metadata wizard for the excel file, instead I would define the schema manually.
Unfortunately the tFileInputExcel comp must read all fields from the first until the last related column.
In Talend Exchange exists a complete different excel component suite which does not need reading unnecessary fields and also can read sheet by sheet without reading the whole file new.
Check them out by searching for excel in Talend Exchange.
The Metadata for Excel file can only handle on sheet because the input component can only handle one schema.
One Star

Re: Metadata File Excel which contains index formula and data validation

Hi,
Thanks for your reply. So you mean that with TOS existing components, there is no way that I can't read fields which is the result of index formula. Is it right?
Best regard,
Sophana
Seventeen Stars

Re: Metadata File Excel which contains index formula and data validation

I would not say always but I have seen many cases. One problem is that the underlaying Apache POI API supports only in the latest releases an number of functions.