topic Re: Autoloader Excel Files in Data Engineering

Autoloader Excel Files

erigaud — Wed, 19 Jul 2023 09:39:42 GMT

Hello, I looked at the documentation but could not find what I wanted. Is there a way to load Excel files using an autoloader and if yes, what options should be given to specify format, sheet name etc ?

Thank you friends !

Re: Autoloader Excel Files

Vinay_M_R — Wed, 19 Jul 2023 11:12:34 GMT

Hi @erigaud

You can use spark.read.format("com.crealytics.spark.excel") while reading excel files using autoloader and to specify format you need to provide com.crealytics.spark.excel and to specify sheet name you can provide it under options.

Please find the below example code to read load Excel files using an autoloader:

spark.read.format("com.crealytics.spark.excel") \

.option("header", "true") \

.option("dataAddress", "'Data - Current'!A1") \

.option("treatEmptyValuesAsNulls", "true") \

.option("inferSchema", "true") \

.load(location)

And you can also use pandas to read excel files:

import pandas as pd

pd.read_excel(file ,sheet_name = 0, index_col = 0)

Re: Autoloader Excel Files

erigaud — Wed, 19 Jul 2023 11:23:12 GMT

Thank you very much, great solution !

Re: Autoloader Excel Files

Hemant — Wed, 19 Jul 2023 11:43:40 GMT

Unfortunately, Databricks autoloader doesn't support Excel file types to incrementally load new files.

Link:https://docs.databricks.com/ingestion/auto-loader/options.html

If your excel file contains a single sheet then there is a workaround.

Re: Autoloader Excel Files

Hemant — Wed, 19 Jul 2023 15:31:52 GMT

Where autoloader is being used in the above snippet 🤔 ?

Re: Autoloader Excel Files

erigaud — Thu, 20 Jul 2023 09:19:44 GMT

What is the workaround ? Is it to transform the excel to a csv file ?

Re: Autoloader Excel Files

Hemant — Fri, 21 Jul 2023 03:42:54 GMT

But using autoloader for the conversion into csv.

Re: Autoloader Excel Files

erigaud — Fri, 21 Jul 2023 06:24:27 GMT

How would that work exactly ? I specify to the autoloader that the format is csv and it will be able to pick up the excel files, and load the sheet normally, even if the format is .xlsx ?