Reading Excel(xlsx) with Pyspark does not work above a certain medium size

user_3844270 0
Viewed 118

Having the following configuration of a cluster in databricks: 64GB, 8 cores

The tests have been carried out as the only notebook in the cluster, at that time there were no other notebooks running. I find that reading a simple 30 MB Excel file in spark keeps loading and does not work. Using the following code for this purpose:

sdf = spark.read.format("com.crealytics.spark.excel")\
                .option("header", True)\
                .option("inferSchema", "true")\
                .load(my_path)
display(sdf)

I have tried reducing the excel file and it works fine up to 15MB. As a workaround I am going to export the excel to csv and read it from there, but I find it shocking that spark can't even read 30MB of excel.

or am I doing something wrong in the configuration?

pyspark azure-databricks databricks apache-spark-sql
2 Answers

You need to install these 2 libraries on your databricks cluster to read excel files. Follow these paths to install:

Clusters -> select your cluster -> Libraries -> Install New -> Maven -> in Coordinates: com.crealytics:spark-excel_2.12:0.13.5

Clusters -> select your cluster -> Libraries -> Install New -> PyPI-> in Package: xlrd

Now, you will be able to read your excel as follows:

sdf = spark.read.format("com.crealytics.spark.excel") \
    .option("header", "true") \
    .option("inferSchema", "true") \
    .option("dataAddress", "'NameOfYourExcelSheet'!A1") \
    .load(filePath)
user_159690410

Can you please try the below option as shown in this spark-excel - github ?

Based on your input you can modify the number of rows. The value 20 is an sample value.

.option("maxRowsInMemory", 20) // Optional, default None. If set, uses a streaming reader which can help with big files (will fail if used with xls format files)

As mentioned above, the option does not work for .xls files.

In case the files are really big, consider the options as show in the link #590

Please validate before using any of the options specified.

Cheers...

user_132808380
Related