Modifying the xlsx file using openpyxl in databricks directly without pandas/dataframe

user_6613295 0
Viewed 150
import openpyxl
input_workbook1 = openpyxl.load_workbook('/dbfs/FileStore/Test/my_excel.xlsx')
sheet_1 = input_workbook1.active
sheet_1['A2'] = 'A2'
input_workbook1.save('/dbfs/FileStore/Test/Output.xlsx')  
OSError: [Errno 95] Operation not supported

I tried reading the excel file directly using openpyxl in databricks , I can able to read and modify directly without pandas/dataframes, but when I am trying to save i.e last line in above code facing the issue.I tried exactly the same way but facing the above error , can anyone help me please

openpyxl azure-databricks databricks python
1 Answers

I tried doing the same procedure and it gave me the same error OSError: [Errno 95] Operation not supported. The reason for this is that there is a limitation that random writes do not work on the local file system and here is the official Microsoft documentation (Local File API limitations) which refers to this issue.

So, try instead of trying to write to the local file system, write the file to /databricks/driver/ path and then copy/move the file to required directory.

Modify your code as following:

import openpyxl
input_workbook1 = openpyxl.load_workbook('/dbfs/FileStore/Test/my_excel.xlsx')
sheet_1 = input_workbook1.active
sheet_1['A2'] = 'A2'
input_workbook1.save('Output.xlsx')
#will be saved to '/databricks/driver/'.
#Use dbutils.fs.ls('/databricks/driver/') to view.
from shutil import move
move('/databricks/driver/Output.xlsx','/dbfs/FileStore/Test/')

Ref1

wb1 = openpyxl.load_workbook('/dbfs/FileStore/Output.xlsx')
ws1 = wb1.active
for row in ws1.iter_rows():
    print([col.value for col in row])

Ref2

The above code will successfully move your file to the required path without any errors.

user_182299800
Related