i can list out the file using dbutils but can not able to read files in databricks

[MohammadWasi]

I can list out the file using dbutils but can not able to read files in databricks. PFB in screenshot. I can able to see the  file using dbutils.fs.ls but when i try to read this file using read_excel then it is showing me an error like "FileNotFoundError: [Errno 2] No such file or directory: '/FileStore/xxx/abcd.xls'". I used to read excel file as

"df_mem = pd.read_excel('/FileStore/xxx/abcd.xls', engine='openpyxl', sheet_name = 'Sheet 1')"

[Kaniz]

Hi @MohammadWasi, It seems like you’re encountering a common issue related to file paths when working with pd.read_excel in Python.

Let’s troubleshoot this step by step:

1. Check the File Path:

   • First, ensure that the Excel file (abcd.xls) is indeed located in the specified directory (/FileStore/xxx/).
   • Double-check the spelling of the file name and the path to make sure there are no typos.
   • If the file is in a different directory, adjust the path accordingly.

2. Use Absolute Paths:

   • Instead of using a relative path, consider using an absolute path to the file. This ensures that you’re accessing the correct directory.
   • You can construct an absolute path using the os module in Python. Here’s an example:

     import os
     pre = os.path.dirname(os.path.realpath(__file__))
     fname = 'abcd.xls'
     path = os.path.join(pre, fname)
     df_mem = pd.read_excel(path, engine='openpyxl', sheet_name='Sheet 1')
     

3. Convert to CSV (Optional):

   • If you continue to face issues, consider converting your Excel file (abcd.xls) to a CSV file.
   • Then, read the CSV file using pd.read_csv:

     df_mem = pd.read_csv('path/to/abcd.csv')
     

Remember to replace 'path/to/abcd.csv' with the actual path to your CSV file if you choose this option. Hopefully, one of these steps will resolve the issue! 😊

If you need further assistance, feel free to ask^1234.

[MohammadWasi]

Thanks @Kaniz for your reply, Unfortunately I used path as variable in my code but again showing same error. PFB in screenshot.

[Kaniz]

Hi @MohammadWasi, 

• Ensure that the file extension in your code matches the actual file extension. Double-check if it’s ‘.xls’ or ‘.xlsx’.
• I see that you are using an incorrect file extension. Please change it and re-run your code.

[MohammadWasi]

I am using '.xls' format only. 

[Kaniz]

Hi @MohammadWasi, Please use ".xlsx".

[MohammadWasi]

Hi @Kaniz I changed file in .xlsx format but again got the same error as above.