Unable to Read Multiple Excel Files Using Wildcard (*) inUnable to Read Mult Lakehouse using PySpark

[ArwaAldoud]

I’m trying to read multiple Excel (.xlsx) files from a folder in Lakehouse using Notebook PySpark. However, when I use a wildcard (*), I get a FileNotFound error.

Code that fails (attempting to read multiple files using wildcards):

df_sales = pd.read_excel("abfss://{lakehouse}@onelake.dfs.fabric.microsoft.com/Files/Sales*.xlsx", sheet_name="Sales")

Error Message: Using a wildcard (*) results in FileNotFound.

and the Code that works (reading a single file):

df_sales = pd.read_excel("abfss://{lakehouse}@onelake.dfs.fabric.microsoft.com/Files/Current/Sales_2023.xlsx", sheet_name="Sales")

When specifying the exact file name, it works fine.

Any guidance or best practices would be greatly appreciated

[Anonymous]

Hi @ArwaAldoud,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[ArwaAldoud · Accepted solution]

Thank you for your detailed response.

I followed your steps, but unfortunately, it didn’t work for me. Instead, I specified the exact file name in my scenario, and it worked fine.

I appreciate your support and guidance

[Anonymous]

Hi @ArwaAldoud,

We trust that your issue has been resolved. Kindly mark my solution as "Accept as Solution." Additionally, give it a 'Kudos' to help others find it easily.

If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[ArwaAldoud]

I truly appreciate your help.
Thanks again, and I’ll continue engaging in the Microsoft Community Forum.

[Anonymous]

Hi @ArwaAldoud,

Thank you for reaching out in Microsoft Community Forum.

The wildcard (*) in pd.read_excel() is not supported because Pandas expects an exact file path.

please follow below steps to acheive the error;

1. Use dbutils.fs.ls() to get all Excel files in the folder and read them one by one into Pandas.

2. For improved performance and scalability, it is recommended to use PySpark and leverage Spark’s Excel reader (com.crealytics.spark.excel) to efficiently read multiple files.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[ArwaAldoud]

The file name is correct, the path is correct it's only an issue when using wildcards (*)