Extra underscore behind ".xlsm" and ".xlsx" after exporting excel files from Databricks

[MinMin]

Hi all, 

I tried to export several excel files from Databricks. But there will always be one extra underscore behind ".xlsm" and ".xlsx", if I export them and try to open the files on local system. I have to manually remove the underscore from the filename and then I am able to open the excel files. 

For the solutions, I downloaded different browsers to implement it. It doesn't work either. 

I also tried to change the workspace settings. Does anyone know how I can reconfigure the settings to solve the problem? 

I will be super grateful if you can provide some hints!

Thanks 🙂

[MinMin]

[Kaniz]

Hi @MinMin , Certainly! Dealing with unexpected underscores in exported Excel files can be quite frustrating. 

Let’s explore some potential solutions to address this issue:

File Naming Issue:

• The extra underscore in the exported filenames might be due to a specific naming convention or an unintended behavior during the export process.
• To avoid this, consider renaming the files directly within Databricks before exporting them. Ensure that the filenames do not contain any special characters or underscores.

Exporting Options:

• When exporting data from Databricks, you have a few options:
  • Download from SQL Editor:
    • Within the Databricks SQL interface (SQL editor), you can download the full query results as an Excel file.
    • Uncheck the “LIMIT 1000” option and click the download button under “…” in the bottom left corner.
  • Save Locally and Create Hyperlink:
    • Save the results locally on the disk (e.g., in the dbfs:/FileStore directory).
    • Create a hyperlink for downloading the CSV or Excel file by constructing the appropriate URL.
    • Example URL format: HTTPS://<yourworkspace>.cloud.databricks.com/files/your_file_name.csv or .xlsx.

Python Pandas Workaround:

• If the direct export from Databricks doesn’t resolve the issue, consider converting your Spark DataFrame to a Pandas DataFrame and then exporting it to Excel.
• Here’s a two-step approach:
  1. Convert your Spark DataFrame to Pandas:
  2. Write the Pandas DataFrame to an Excel file:
• This approach bypasses any potential issues related to the Databricks export process.

Check Workspace Settings:

• Review your Databricks workspace settings related to exports and file formats.
• Ensure that there are no custom settings or restrictions causing the unexpected behavior.

Remember to test each solution step by step to identify the root cause and find the most effective resolution. I hope these hints help you resolve the issue! 🌟

[MinMin]

Hello @Kaniz,

Thank you very much for the response 🙂

My team tested the solutions as you mentioned above. But it didn't work 😞

Note: No code is used to download the excel file. We are trying to directly export the file from Databricks workspace (see screenshot).

The problem appears for all users and not just for this one excel file.

As I mentioned it's not just for excel, it's for all types of files I export in this way: .txt or .py etc.

I'm looking forward to your feedbacks!

[DH_Fable]

Hi, did you find a solution this? I have the same/similar problem where when I save a dataframe from a Databricks notebook using to_excel() it saves the file with extension ".xlsx_" rather then "xlsx", meaning to open I have to manually download and change the file type