topic tFileInputExcel: pos exceeds record length in Talend Studio

tFileInputExcel: pos exceeds record length

Anonymous — Mon, 28 Jan 2019 20:29:22 GMT

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

Re: tFileInputExcel: pos exceeds record length

vapukov — Mon, 28 Jan 2019 21:11:00 GMT

Hi,

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

is any reason to use excel instead of plain csv?

Re: tFileInputExcel: pos exceeds record length

Anonymous — Mon, 28 Jan 2019 21:15:19 GMT

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)

Re: tFileInputExcel: pos exceeds record length

Anonymous — Mon, 28 Jan 2019 21:17:55 GMT

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

Re: tFileInputExcel: pos exceeds record length

vapukov — Mon, 28 Jan 2019 21:25:32 GMT

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

Re: tFileInputExcel: pos exceeds record length

Anonymous — Mon, 28 Jan 2019 21:53:52 GMT

I will try that and see if it works

Re: tFileInputExcel: pos exceeds record length

Anonymous — Thu, 31 Jan 2019 17:40:25 GMT

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