I have uploaded small Excel files on my DBFS. I then use function read_xlsx() from the "readxl" package in R to import the file into the R memory. I use a standard cluster (12.1, non ML). The function works but it takes ages. E.g. a simple Excel table with 40000+ records and 5 columns takes 9 minutes. On my R installation on Windows, the load is instantaneous. "readxl" is considered to be the best package to deal with Excel files. It is part of the already made available libraries on the cluster. Any idea what might cause this?
Hi @Marcel Baumgartner, It is unusual for the read_xlsx() function from the readxl package to take so long to read a relatively small Excel file. There could be several reasons for this performance issue, including cluster configuration, available resources, or underlying file storage.
Here are some suggestions to diagnose and potentially resolve the performance issue:
Use the following command to copy the file from DBFS to the local file system:
%fs cp dbfs:/path/to/your/excel_file /tmp/local_excel_file
Then, in your R code, read the local file:
library(readxl) df <- read_xlsx("/tmp/local_excel_file")
package or a more general issue. Keep in mind that you might need to install these libraries if they are not already available on your cluster.
Please note that R in Databricks runs inside a container, and its performance might be different from your local R installation. Also, keep in mind that the performance of the R environment in Databricks might not be as optimized as the PySpark environment, which is native to Databricks. If the performance issue persists, consider using PySpark to read and process the data.
Dear Kaniz, thanks. Your suggestion to copy the Excel file from DBFS to a temp folder "e.g. /tmp/" worked. Once I run read_xlsx() the upload is instantenous.
Indeed, we had the same problem the other way around. I was not able to save an xlsx or pdf file into a folder on "/dbfs/FileStore/*". However, I can save it to "/tmp/*" and then copy it to where I want the file to be. Good to know!
Hi @Marcel Baumgartner
Thank you for posting your question in our community! We are happy to assist you.
To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?
This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance!
Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections.
Click here to register and join today!