tFileInputExcel: pos exceeds record length

[Anonymous]

I am new to talend.

I created a job to write to a xls file and then to load into a SQL database.

The job creates the xls but when I try to open it it opens in safe mode only and then I get this error and the job does not finish.

Any help would be great

[statistics] connecting to socket on port 3992
[statistics] connected
Exception in component tFileInputExcel_1 (TEST)
jxl.common.AssertionFailed: pos exceeds record length
at jxl.common.Assert.verify(Assert.java:51)
at jxl.read.biff.SSTRecord.readStrings(SSTRecord.java:208)
at jxl.read.biff.SSTRecord.<init>(SSTRecord.java:123)
at jxl.read.biff.WorkbookParser.parse(WorkbookParser.java:585)
at jxl.Workbook.getWorkbook(Workbook.java:271)
at local_project.test_0_1.TEST.tFileInputExcel_1Process(TEST.java:14455)
at local_project.test_0_1.TEST.tDBInput_1Process(TEST.java:8923)
at local_project.test_0_1.TEST.runJobInTOS(TEST.java:18218)
at local_project.test_0_1.TEST.main(TEST.java:18069)
[statistics] disconnected

[vapukov]

Hi,

did you test the same with checbox "read 2007 format"?

is any reason to use excel instead of plain csv? 

[Anonymous]

There is a field that has lots of commas and special characters in it so when I tried to create the csv it doesn't come out correct. Also it creates 12900 rows and when I click on the check bow to read 2007 it still gives me a different error 

Exception in component tFileInputExcel_1 (TEST)
org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)

[Anonymous]

Sorry also when I try to write to the xlsx format it says that a cell can not contain text greater than 32,767 characters

[vapukov · Accepted solution]

do you enable in advanced settings for csv - escape and text enclosure? (for example "\"" for both)

usually, it well handle all commas and special characters inside text

[Anonymous]

I will try that and see if it works

[Anonymous]

Thanks I appreciate the help. I did what you said and it worked. I did not know that option was there.