CODEWITHSUNDEEP
pythonpandasazurepysparkdatabricks
walzer91
walzer91

Reputation: 630

Export a Spark Dataframe (pyspark.pandas.Dataframe) to Excel file from Azure DataBricks

I'm struggling with the export of a pyspark.pandas.Dataframe to an Excel file.

I'm working on an Azure Databricks Notebook with Pyspark. My goal is to read a csv file from Azure Data Lake Storage container and store it as a Excel file on another ADLS container.

I'm finding so many difficulties related to performances and methods. pyspark.pandas.Dataframe has a built-in to_excel method but with files larger than 50MB the commands ends with time-out error after 1hr (seems to be a well known problem).

Following you can find an example of code. It ends by saving the file on the DBFS (there are still problems integrating the to_excel method with Azure) and then I move the file to the ADLS.

import pyspark.pandas as ps
spark.conf.set(f"fs.azure.account.key.{storage_account_name}.dfs.core.windows.net", storage_account_key)

reference_path = f'abfss://{source_container_nae}@{storage_account_name}.dfs.core.windows.net/{file_name}'

df = ps.read_csv(reference_path, index=None)

df.to_excel(file_name, sheet_name='sheet')

pyspark.pandas.Dataframe is the suggested method by Databricks in order to work with Dataframes (it replaces koalas) but I can't find any solution to my problem, except converting the dataframe to a normal pandas one.

Can please someone help me?

Thanks in advance!

UPDATE

Some more information of the whole pipeline.

I have a DataFactory pipeline that reads data from Azure Synapse, elaborate them and store them as csv files in ADLS. I need DataBricks because DataFactory does not have a native sink Excel connector! I know that I can use instead Azure Functions or Kubernetes, but I started using DataBricks hoping that it was possible...

Upvotes: 2

Views: 13167

Answers (3)

walzer91
walzer91

Reputation: 630

I've found a solution to the problem with the pyexcelerate package:

from pyexcelerate import Workbook

df = # read your dataframe

values = df.columns.to_list() + list(df.values)
sheet_name = 'Sheet'

wb = Workbook()
wb.new_sheet(sheet_name, data=values)
wb.save(file_name)

In this way Databricks succeed in elaborating a 160MB dataset and exporting to Excel in 3 minutes.

Let me know if you find a better solution!

Upvotes: 0

Bjørn Jørgensen
Bjørn Jørgensen

Reputation: 106

Hm.. it looks like you are reading the same file and saving to the same file.

can you change

df.to_excel(file_name, sheet_name='sheet')

to

df.to_excel("anotherfilename.xlsx", sheet_name='sheet')

Upvotes: 0

vladc
vladc

Reputation: 180

You should not convert a big spark dataframe to pandas because you probably will not be able to allocate so much memory. You can write it as a csv and it will be available to open in excel:

df.to_csv(path=file_name, num_files=1)

Upvotes: -2

Related Questions