I am using XlsxWriter on Databricks to write an Excel file on an Azure blob but I have the following issue : FileCreateError: [Errno 95] Operation not supported.
I don't know anything about Databricks but does it allow you create files? For example is you used
to_csv()
instead of
to_excel()
would it work?
If it does work then it may be that Databricks doesn't allow you to create temp files (which XlsxWriter uses for efficiency). Do you have a longer error trace which might show that?
OSError Traceback (most recent call last)
/databricks/python/lib/python3.7/site-packages/xlsxwriter/workbook.py in close(self)
315 try:
--> 316 self._store_workbook()
317 except IOError as e:
/databricks/python/lib/python3.7/site-packages/xlsxwriter/workbook.py in _store_workbook(self)
709 try:
--> 710 xlsx_file.write(os_filename, xml_filename)
711 os.remove(os_filename)
/usr/lib/python3.7/zipfile.py in write(self, filename, arcname, compress_type, compresslevel)
1743 with open(filename, "rb") as src, self.open(zinfo, 'w') as dest:
-> 1744 shutil.copyfileobj(src, dest, 1024*8)
/usr/lib/python3.7/zipfile.py in close(self)
1137 self._fileobj.write(self._zinfo.FileHeader(self._zip64))
-> 1138 self._fileobj.seek(self._zipfile.start_dir)
OSError: [Errno 95] Operation not supported
During handling of the above exception, another exception occurred:
FileCreateError Traceback (most recent call last)
<command-4005735283330799> in <module>
9 df.to_excel(writer, sheet_name='output_1')
---> 11 writer.save()
/databricks/python/lib/python3.7/site-packages/pandas/io/excel.py in save(self)
1950 """
-> 1952 return self.book.close()
1954 def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0,
/databricks/python/lib/python3.7/site-packages/xlsxwriter/workbook.py in close(self)
316 self._store_workbook()
317 except IOError as e:
--> 318 raise FileCreateError(e)
319 except LargeZipFile as e:
320 raise FileSizeError("Filesize would require ZIP64 extensions. "
FileCreateError: [Errno 95] Operation not supported
Could you try with the following option and see if it changes anything:
writer = pd.ExcelWriter('output.xlsx',
engine='xlsxwriter',
options={'in_memory': True})It doesn't work ...
I think the issue come from Databricks, I will try to contact them.
Thanks a lot !
hey, have you found solution? I got the same issue
Since several people are having this issue let's try debug it. It don't have access to (or have every used) Databricks so you will have to run the tests.
Let's remove Pandas from the equation.
Step1: Can you run this program and see if it works:
import xlsxwriter
workbook = xlsxwriter.Workbook('test1.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()If not can you post any trace/warnings.
Step 2: Then try this and see if there is any difference:
import xlsxwriter
workbook = xlsxwriter.Workbook('test2.xlsx', {'constant_memory': True})
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()Step 3: Also, then try this and see if there is any difference:
import xlsxwriter
workbook = xlsxwriter.Workbook('test3.xlsx', {'tmpdir': '.'})
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
workbook.close()I managed to solve it by saving the file locally in Databricks, then I copy it to the desired mount.
import datetime
import shutil
now_= datetime.datetime.now()
timestamp_ = datetime.datetime.timestamp(now_)
tmp_path = f'temp_{timestamp_}.xlsx' # I save it with the timestamp to avoid errors
writer = ExcelWriter(tmp_path)
real_path = f'{YOURPATH}_filename.xlsx'
df.to_excel(writer,'sheet_name')
writer.save()
shutil.copy(tmp_path, real_path) # Copy the temp file to mount Resolved
Similar to the suggestion from @pfernandez-sanofi above the Databricks documentation now recommends performing the operation on a local disk and then copying the output XlsxWriter file to dbfs.
From the docs https://docs.databricks.com/data/databricks-file-system.html#local-file-api-limitations:
Local file API limitations
FUSE V2 (default for Databricks Runtime 6.x and 7.x)
Does not support random writes. For workloads that require random writes, perform the I/O on local disk first and then copy the result to /dbfs. For example:
import xlsxwriter
from shutil import copyfile
workbook = xlsxwriter.Workbook('/local_disk0/tmp/excel.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(0, 0, "Key")
worksheet.write(0, 1, "Value")
workbook.close()
copyfile('/local_disk0/tmp/excel.xlsx', '/dbfs/tmp/excel.xlsx')
