topic Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that. in Data Engineering

How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

databicky — Mon, 02 Jan 2023 09:08:45 GMT

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

daniel_sahal — Mon, 02 Jan 2023 10:35:08 GMT

Can you please share the code snippet?

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

ramravi — Mon, 02 Jan 2023 13:56:52 GMT

Currently, as per my understanding, there is no support available in databricks to write into excel file using python. Suggested solution would be to convert pandas Dataframe to spark Dataframe and then use Spark Excel connector to write into excel files. This link explains the details clearly for the same requirement.

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

sher — Mon, 02 Jan 2023 15:00:14 GMT

hey

you need to authenticate the abfss

Configure authentication
service_credential = dbutils.secrets.get(scope="<scope>",key="<service-credential-key>")
 
spark.conf.set("fs.azure.account.auth.type.<storage-account>.dfs.core.windows.net", "OAuth")
spark.conf.set("fs.azure.account.oauth.provider.type.<storage-account>.dfs.core.windows.net", "org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider")
spark.conf.set("fs.azure.account.oauth2.client.id.<storage-account>.dfs.core.windows.net", "<application-id>")
spark.conf.set("fs.azure.account.oauth2.client.secret.<storage-account>.dfs.core.windows.net", service_credential)
spark.conf.set("fs.azure.account.oauth2.client.endpoint.<storage-account>.dfs.core.windows.net", "https://login.microsoftonline.com/<directory-id>/oauth2/token")

you can check out below two links:

https://docs.databricks.com/external-data/azure-storage.html

https://docs.databricks.com/_static/notebooks/adls-gen2-service-principal.html

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

Hubert-Dudek — Mon, 02 Jan 2023 15:33:30 GMT

Please mount ADLS storage as described here:

https://community.databricks.com/s/feed/0D53f00001eQGOHCA4

And then write pandas to excel to that directory.

df.to_excel("output.xlsx")

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

FerArribas — Mon, 02 Jan 2023 21:25:43 GMT

Hi @Hubert Dudek,

Pandas API doesn't support abfss protocol.

You have three options:

If you need to use pandas, you can write the excel to the local file system (dbfs) and then move it to ABFSS (for example with dbutils)
Write as csv directly in abfss with the Spark API (without using pandas)
Write the dataframe as excel with the Spark API directly in abfss with a library like https://github.com/crealytics/spark-excel. (without using pandas)

Thanks.

Fernando Arribas

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

Hubert-Dudek — Tue, 03 Jan 2023 08:57:16 GMT

But once you mount it, you can write as it is visible as a dbfs directory.

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

FerArribas — Tue, 03 Jan 2023 13:16:05 GMT

Have you tried writing to the local file system (for example, in the path /databricks/...)

Anyway, i recommend you to tyr writing with Spark (without dataframes). Pandas without additional libraries, doesn't distribute and with high volumes you will have memory problems, performance problems...

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

databicky — Tue, 03 Jan 2023 13:16:07 GMT

so for that we don't have a option to add background color and not able to autofit the rows and columns

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

Hubert-Dudek — Tue, 03 Jan 2023 13:17:52 GMT

It is enough to use pandas on a spark, so it is distributed. Additionally, pandas have the to_excel method, but spark data-frames do not.

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

databicky — Tue, 03 Jan 2023 13:22:09 GMT

i done that, but in spark df not able to add bg color and cell alignment based on values in excel sheet

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

FerArribas — Tue, 03 Jan 2023 13:51:22 GMT

I'm not sure about that. When you call the function to_excel all the data is loaded into the driver (as if you were doing a collect). So, the writing is not distributed and you can have memory and performance problems as I mentioned.

Reference: https://spark.apache.org/docs/latest/api/python/reference/pyspark.pandas/api/pyspark.pandas.DataFrame.to_excel.html

Try writing with this library:

https://github.com/crealytics/spark-excel

Example (https://github.com/crealytics/spark-excel/issues/134#issuecomment-517696354):

df.write

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

.save("test.xlsx")

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is showing error as protocol not known 'abfss' like that.

Jfoxyyc — Wed, 04 Jan 2023 21:55:58 GMT

Under the general scenario this shouldn't be an issue since an Excel file can only handle a little over a million rows anyway. Saying that, your statement that this should be written to dbfs and use dbutils to move the file to abfs should be the accepted answer.

Re: How can we write a pandas dataframe into azure adls as excel file, when trying to write it is sh

AngelBalcazar13 — Fri, 06 Sep 2024 00:54:41 GMT

how can I know the path in dbfs where the file is written?

from the Workspace I have tried with

path

URLpath