pd.ExcelWriter, writer.save(), download file locally

user_4668284 0
Viewed 648

I am using Python 3.7.5 in a databricks environment.

I have a pretty simple function written that concatenates multiple dataframes into one excel spreadsheet. The main issue is that I can't access the file to download to my local machine.

# function

def dfs_tabs(df_list, sheet_list, file_name):

    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')  

    for dataframe, sheet in zip(df_list, sheet_list):

        dataframe.to_excel(writer, sheet_name=sheet, startrow=0 , startcol=0)  

    writer.save()

 

# list of dataframes and sheet names

dfs = [df, df1, df2]

sheets = ['df','df1','df2']   

 

# run function

dfs_tabs(dfs, sheets, 'filename.xlsx')

Here is a sample of how I export dataframes to csv successfully to a location where I can download the files. Hoping that I can merge the logic from below to the function above to be able to download the file to my local machine.

#bounce to csv

outname = 'file.csv'

outdir = '/dbfs/FileStore/'

df.to_csv(outdir+outname, index=False, encoding="utf-8")
pandas dataframe excel python
1 Answers

per https://docs.databricks.com/data/databricks-file-system.html#local-file-api-limitations - I had to append the entire local address to the filename variable - complete working script below - thanks @jmcnamara

# function

def dfs_tabs(df_list, sheet_list, file_name):

    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')  

    for dataframe, sheet in zip(df_list, sheet_list):

        dataframe.to_excel(writer, sheet_name=sheet, startrow=0 , startcol=0)  

    writer.save()

 

# list of dataframes and sheet names

dfs = [df, df1, df2]

sheets = ['df','df1','df2']   

 

# run function

dfs_tabs(dfs, sheets, '/local_disk0/tmp/filename.xlsx')

 

#copy file to dbfs

 

copyfile('/local_disk0/tmp/filename.xlsx', '/dbfs/FileStore/filename.xlsx')
user_46682840
Related