How to Extract Sheet Names from Excel File Using "Com. Crealytics. Spark. Excel" in Databricks (Pyspark)
I Have an Excel File in the Azure Datalake, I Have Read the Excel File Like the Following Ddff=Spark. Read. Format("Com. Crealytics. Spark. Excel") \...
I have an Excel file in the azure datalake ,I have read the excel file like the following
ddff=spark.read.format("com.crealytics.spark.excel") \
.option("header", "true")\
.option("sheetName","__all__")\
.option("inferSchema","true")\
.load("abfss://[email protected]/file.xlsx")
Now Iam confused how to get just the sheetnames from that Excel file,is there any direct function to do that ?
2 Answers
As per the github link for spark-excel.. the following code should work - please try it...Code directly taken from the github page.
import com.crealytics.spark.excel.WorkbookReader
val sheetNames = WorkbookReader( Map("path" -> "Worktime.xlsx")
, spark.sparkContext.hadoopConfiguration
).sheetNames
val df = spark.read.excel(
header = true,
dataAddress = sheetNames(0)
)
I reproduce the same in my environment with sample data. I got this output.
Please follow the below steps. To resolve this issue :
Step1: Mount your storage account with Azure data bricks:
dbutils.fs.mount(
source = "wasbs://<container_name>@<stoarge_account_name>.",
mount_point = "/mnt/<mount_path>",
extra_configs = {"fs.azure.account.key.<stoarge_account_name>.blob.core.windows.net":"<Access_key>"})
Step 2: Use dbutils.fs.ls to access the whole mount path:
dbutils.fs.ls("/mnt/<mount_path>")
Note: Use "/dbfs", instead of "dbfs:": The function expects a local file path
For example, This is my actual file path: dbfs:/mnt/dem/read-employees-csv.xlsx. I modified the file path like this /dbfs/mnt/dem/read-employees-csv.xlsx. And also make sure to install openpyxl and fsspec . Use this code to install packages : pip install openpyxland pip install fsspec
Now, execute the below code. You will get the list of sheets:
Code:
import pandas
x1 = pandas.ExcelFile('/dbfs/mnt/dem/read-employees-csv.xlsx')
print(x1.sheet_names)
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
