One Star

Issue in import file Excel

Hi all,
I'm trying to import a lot of excel files using Talend etl jobs, but, when i create the metadata the following message is displayed:
Warning:  Usage of a local non-builtin name
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.metadata.managment.ui.utils.ShadowProcessHelper.getCsvArray(ShadowProcessHelper.java:383)
org.talend.repository.ui.wizards.metadata.connection.files.excel.ExcelFileStep2Form$PreviewProcessor.nonUIProcessInThread(ExcelFileStep2Form.java:1042)
org.talend.commons.ui.swt.thread.SWTUIThreadProcessor$1.run(SWTUIThreadProcessor.java:74)

Files I'm trying to import have 97-2003 excel format (xls) and I'm using UTF-8 encoding.
If I convert these files using excel the error doesn't occur more.
Because I need import these files automatically no convert operation shoud be performed.
I use TOS DI 6.0.0
Does anybody can help me, please?
20 REPLIES
One Star

Re: Issue in import file Excel

Encountering the same issue on Talend 6.0 on 32bit machine.
One Star

Re: Issue in import file Excel

Encountering the same issue on Talend 6.0 on 32bit machine.

I use 64 bit machine

Re: Issue in import file Excel

talendnub, have you special characters in your sheets?
Moderator

Re: Issue in import file Excel

Hi,
What does your whole ETL job look like?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Re: Issue in import file Excel

Hi,
I encouter the issue before I develop an ETL job. I'd like create metadata for excel files but the error message is displayed.
thanks!
One Star

Re: Issue in import file Excel

Hello,
There are no special characters on the worksheet.
I created a job called "testexceljob".
Right clicked on File Excel under Metadata.
Clicked on Create file Excel.
Used the text "textexcel" for the Name, Purpose, Description.
clicked on Next.
Clicked Browse and selected my excel file. 
File: C:/Talend/Workspace/TestExcel.xlsx
On Set sheets parameters window, clicked on Sheet1
Clicked Next
Showed the following on Output tab:
Error: Could not find or load main class local_project.shadowfileinputtodelimitedoutput_0_1.ShadowFileInputToDelimitedOutput
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.metadata.managment.ui.utils.ShadowProcessHelper.getCsvArray(ShadowProcessHelper.java:383)
org.talend.repository.ui.wizards.metadata.connection.files.excel.ExcelFileStep2Form$PreviewProcessor.nonUIProcessInThread(ExcelFileStep2Form.java:1042)
org.talend.commons.ui.swt.thread.SWTUIThreadProcessor$1.run(SWTUIThreadProcessor.java:74)
Talend installation Path: C:\Talend\
Workspace: C:\Talend\Workspace

Re: Issue in import file Excel

Update:
I tried to create metadata of excel files using TOS DI 5.1 (installaed in a differente computer) and it works well!!!!!
Then, I performed the same actions in another computer with TOS DI 6.0 and the issue occurs as the first computer (so, I performed tests using 2 computer with TOS DI 6.0 and one computer with TOS DI 5.1)
So, it's a TOS DI 6.0 bug, I think....Smiley Sad
In the meantime, i'll try to solve looking for an XLS->CSV batch converter but it's a work around and it's a bad solution when i have multiple sheetsSmiley Sad
I uploaded a simple excel file (only with headers) able to demonstrate the issue.
I hope in Talend developing team for future improvement...
Regards
Cartel4.zip.zip

Re: Issue in import file Excel

Can anybody (TOS DI 6.0 users) try to create metadata using the excel file I attached in my previous post ?
Thanks in advance!
One Star

Re: Issue in import file Excel

looks some issue with your excel file.. i tried with 5.6.1 EE and 6.0 the preview throws the same error as what you are getting. I tried creating a new excel sheet and tried to import in both 5.6.1 and 6.0 and it works fine
One Star

Re: Issue in import file Excel

Looks like your issue is with the excel sheet itself. You can do this, open the excel sheet navigate to formulas-> click on name manager-> in that you can see the refers to field, which you can remove the values in that field and save it or else you can delete that formula if you dont use it. After that Talend works fine, can preview data and as well as able to load/read the excel by creating metadata. hope this helps

Re: Issue in import file Excel

Many thanks! your solution works well...but...it's a workaround.
If you need to schedule the process, how can you remove the references? 
You before might edit the excel file and then execute the Talend job...right?
I don't understand because the same file works well using TOS DI 5.1 Smiley Sad
One Star

Re: Issue in import file Excel

ya, thats the only option you have for now. 

Re: Issue in import file Excel

or..I come back to TOS DI 5.1 Smiley Very Happy
Four Stars

Re: Issue in import file Excel

Does anybody of Talend team suggest any other workarounds or solutions? I can't go back TOS DI 5.1 as wroted above. Smiley Sad
 
Four Stars

Re: Issue in import file Excel

up....
Moderator

Re: Issue in import file Excel

Hi Rosanero4Ever,
We have made a testing on studio 6.0 with your Cartel4.xls file and got the same error.
Could you please create a jira issue of DI project on Talend Bug Tracker so that our developer will check it to see if it is a bug?
In addition, can you import another exce file in metadata successfully on studio 6.0?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Issue in import file Excel

Hi Sabrina,
I import successfully other excel files.
As described above, the issue is due to references which are in "name manager" in Excel formulas menu. If there aren't references the excel file is imported successfully.
I created the jira issue at https://jira.talendforge.org/browse/TDI-33863
I hope Talend team will solve this issue as soon as possibile (i have to import  many excel files with references !!!)
In the meantime I'll also install  TOS DI 5.1 to import excel file with references
Many thanks for your precious support.
Moderator

Re: Issue in import file Excel

Hi Rosanero4Ever,
Thanks for your feedback. We will keep you posted on this issue.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Issue in import file Excel

I'm having the same issue.  My file was a very simple two column 10 row file.  I copied the data and moved it to a new sheet, deleted the original sheet and it finally worked.  But that shouldn't happen.  Please advise of bug fix.
Four Stars

Re: Issue in import file Excel

Hi all,
I can confirm that the described bug was solved in TOS DI 6.1.
Congratulations to the development team for its work! Smiley Very Happy