Is XLSB supported? #669

yaravind · 2022-10-22T18:02:01Z

yaravind
Oct 22, 2022

I am getting the following exception when reading XLSB file.

Code

val srvcLevlDf = spark.read.excel(
    header = true,  // Required
    inferSchema = true
    ).load("Database.xlsb")

Exception

com.github.pjfanning.xlsx.exceptions.OpenException: Failed to open file
  at com.github.pjfanning.xlsx.impl.StreamingWorkbookReader.init(StreamingWorkbookReader.java:127)

  at com.github.pjfanning.xlsx.impl.StreamingWorkbookReader.init(StreamingWorkbookReader.java:97)

  at com.github.pjfanning.xlsx.StreamingReader$Builder.open(StreamingReader.java:532)

  at com.crealytics.spark.excel.StreamingWorkbookReader.openWorkbook(WorkbookReader.scala:128)

  at com.crealytics.spark.excel.WorkbookReader.withWorkbook(WorkbookReader.scala:33)

  at com.crealytics.spark.excel.WorkbookReader.withWorkbook$(WorkbookReader.scala:32)

  at com.crealytics.spark.excel.StreamingWorkbookReader.withWorkbook(WorkbookReader.scala:108)

  at com.crealytics.spark.excel.ExcelRelation.excerpt$lzycompute(ExcelRelation.scala:48)

  at com.crealytics.spark.excel.ExcelRelation.excerpt(ExcelRelation.scala:48)

  at com.crealytics.spark.excel.ExcelRelation.headerColumns$lzycompute(ExcelRelation.scala:121)

  at com.crealytics.spark.excel.ExcelRelation.headerColumns(ExcelRelation.scala:120)

  at com.crealytics.spark.excel.ExcelRelation.$anonfun$inferSchema$1(ExcelRelation.scala:189)

  at scala.Option.getOrElse(Option.scala:189)

  at com.crealytics.spark.excel.ExcelRelation.inferSchema(ExcelRelation.scala:188)

  at com.crealytics.spark.excel.ExcelRelation.<init>(ExcelRelation.scala:52)

  at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:52)

  at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:29)

  at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:24)

  at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:350)

  at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:281)

  at org.apache.spark.sql.DataFrameReader.$anonfun$load$3(DataFrameReader.scala:253)

  at scala.Option.getOrElse(Option.scala:189)

  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:253)

  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:190)

  ... 83 elided

Caused by: com.sun.org.apache.xerces.internal.impl.io.MalformedByteSequenceException: Invalid byte 1 of 1-byte UTF-8 sequence.

  at com.sun.org.apache.xerces.internal.impl.io.UTF8Reader.invalidByte(UTF8Reader.java:702)

  at com.sun.org.apache.xerces.internal.impl.io.UTF8Reader.read(UTF8Reader.java:568)

  at com.sun.org.apache.xerces.internal.impl.XMLEntityScanner.load(XMLEntityScanner.java:1690)

  at com.sun.org.apache.xerces.internal.impl.XMLEntityScanner.arrangeCapacity(XMLEntityScanner.java:1556)

  at com.sun.org.apache.xerces.internal.impl.XMLEntityScanner.skipString(XMLEntityScanner.java:1594)

  at com.sun.org.apache.xerces.internal.impl.XMLVersionDetector.determineDocVersion(XMLVersionDetector.java:156)

  at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:805)

  at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:770)

  at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141)

  at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1213)

Answered by nightscape

Oct 25, 2022

To summarize the answer regarding the original question: XLSB is not supported.

View full answer

nightscape · 2022-10-24T10:29:20Z

nightscape
Oct 24, 2022
Maintainer

@yaravind are you sure this is the complete list of options you provided? Actually, the StreamingWorkbookReader should only be used if you specify maxRowsInMemory.

2 replies

yaravind Oct 24, 2022
Author

@nightscape Thank you for looking into this. Following the exact code causing the issue. I had to specify the maxRowsInMemory (or streaming) option as these files are more than 50 MB and it was failing with another exception as shown below

val srvcLevlDf = spark.read.excel(
    header = true,  // Required
    dataAddress = "'DB'!A2", // Optional, default: "A1"
    inferSchema = true,
    maxRowsInMemory=100000
)

When using XLSB input I get the following exception

org.apache.poi.xssf.XLSBUnsupportedException: .XLSB Binary Workbooks are not supported
  at org.apache.poi.xssf.usermodel.XSSFWorkbook.beforeDocumentRead(XSSFWorkbook.java:360)

  at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:257)

  at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:118)

  at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.create(XSSFWorkbookFactory.java:98)

  at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.create(XSSFWorkbookFactory.java:36)

  at org.apache.poi.ss.usermodel.WorkbookFactory.lambda$create$2(WorkbookFactory.java:224)

  at org.apache.poi.ss.usermodel.WorkbookFactory.wp(WorkbookFactory.java:329)

  at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:224)

  at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:185)

  at com.crealytics.spark.excel.DefaultWorkbookReader.$anonfun$openWorkbook$3(WorkbookReader.scala:102)

  at scala.Option.fold(Option.scala:251)

  at com.crealytics.spark.excel.DefaultWorkbookReader.openWorkbook(WorkbookReader.scala:102)

  at com.crealytics.spark.excel.WorkbookReader.withWorkbook(WorkbookReader.scala:33)

  at com.crealytics.spark.excel.WorkbookReader.withWorkbook$(WorkbookReader.scala:32)

  at com.crealytics.spark.excel.DefaultWorkbookReader.withWorkbook(WorkbookReader.scala:87)

  at com.crealytics.spark.excel.ExcelRelation.excerpt$lzycompute(ExcelRelation.scala:48)

  at com.crealytics.spark.excel.ExcelRelation.excerpt(ExcelRelation.scala:48)

  at com.crealytics.spark.excel.ExcelRelation.headerColumns$lzycompute(ExcelRelation.scala:121)

  at com.crealytics.spark.excel.ExcelRelation.headerColumns(ExcelRelation.scala:120)

  at com.crealytics.spark.excel.ExcelRelation.$anonfun$inferSchema$1(ExcelRelation.scala:189)

  at scala.Option.getOrElse(Option.scala:189)

  at com.crealytics.spark.excel.ExcelRelation.inferSchema(ExcelRelation.scala:188)

  at com.crealytics.spark.excel.ExcelRelation.<init>(ExcelRelation.scala:52)

  at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:52)

  at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:29)

  at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:24)

  at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:350)

  at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:281)

So I saved the excel file as XLSX and tried to load it. I tried to load it in Non-streaming mode. I get the following exception and I am not sure how to fix this so started using streaming mode by specifying maxRowsInMemory as shown in the first code at the start of this comment. I am not sure what value I need to set for array length. Any heuristics that we can use?

org.apache.poi.util.RecordFormatException: Tried to allocate an array of length 106,068,771, but the maximum length for this record type is 100,000,000.
If the file is not corrupt and not large, please open an issue on bugzilla to request

increasing the maximum allowable size for this record type.

You can set a higher override value with IOUtils.setByteArrayMaxOverride()
  at org.apache.poi.util.IOUtils.throwRFE(IOUtils.java:599)

nightscape Oct 25, 2022
Maintainer

See https://github.com/crealytics/spark-excel/pull/613/files#diff-b335630551682c19a781afebcf4d07bf978fb1f8ac04c6bf87428ed5106870f5R94

nightscape · 2022-10-25T09:52:51Z

nightscape
Oct 25, 2022
Maintainer

To summarize the answer regarding the original question: XLSB is not supported.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Is XLSB supported? #669

{{title}}

Replies: 2 comments 2 replies

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

Select a reply

Is XLSB supported? #669

yaravind Oct 22, 2022

Replies: 2 comments · 2 replies

nightscape Oct 24, 2022 Maintainer

yaravind Oct 24, 2022 Author

nightscape Oct 25, 2022 Maintainer

nightscape Oct 25, 2022 Maintainer

yaravind
Oct 22, 2022

Replies: 2 comments 2 replies

nightscape
Oct 24, 2022
Maintainer

yaravind Oct 24, 2022
Author

nightscape Oct 25, 2022
Maintainer

nightscape
Oct 25, 2022
Maintainer