Forum Discussion

Felpan's avatar
Felpan
Frequent Visitor
1 year ago
Solved

Excel Files Extraction from SharePoint & Lakehouse File Sharing Issues

  Hello everyone, I'm facing a challenge with extracting data from approximately 1,200 tables spread across 800-900 Excel files stored in a SharePoint location. I've successfully used Dataflow Gen ...
one lake
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi Felpan ,

    Thanks for the update.

    The warning “Unverified HTTPS request is being made to...” typically appears when SSL certificate verification is disabled using verify=False in a requests call. While this can help bypass certificate issues during testing, it is not a secure long-term solution.

     

    If you're using this approach:

    response = requests.get(url, headers=headers, verify=False)

    Remove verify=False and ensure your system trusts the SSL certificate used by SharePoint or Graph API. You can also use the certifi package to include trusted CA bundles.

     

    To temporarily suppress the warning during development:

    import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

    or:

    import requests
requests.packages.urllib3.disable_warnings()


    Note: Suppressing HTTPS verification is not recommended for production environments. It's safer to ensure your system trusts the certificate or use a valid CA certificate bundle.

     

    Since you're working with SharePoint files via Graph API in Fabric notebooks, suppressing the warning may help temporarily, but securing the connection should be considered as a next step if you plan to move this into production.

     

    Hope this helps. Please reach out for further assistance.
    Please consider marking the helpful reply as Accepted Solution to assist others with similar issues. A kudos would also be greatly appreciated.


    Thank you.

     

v-veshwara-msft's avatar
v-veshwara-msft
Community Support
1 year ago

Hi Felpan , 

Thanks for using Microsoft Fabric Community.

Thanks for providing the details. Since you're working with over 800 Excel files and 1,200+ tables, handling them through multiple queries in Dataflow Gen2 becomes difficult to scale. Now that you have your App ID, Tenant ID, and Secret, a better option would be to use Fabric Notebooks with the Microsoft Graph API.

Here’s an overviewof the approach:

  1. Authenticate using the client credentials flow to acquire an access token.

  2. Use the Microsoft Graph API to list and download files from SharePoint.

  3. Read each Excel file using libraries.

  4. Append the extracted data to a consolidated Spark DataFrame.

  5. Write the combined result to a Delta table in your Lakehouse.

A similar implementation has been discussed in this thread, where Fabric notebooks and the Graph API are used for this purpose. You can adapt that pattern to loop through all files and consolidate tables efficiently. 

 

Regarding Excel File Sharing in Lakehouse

Files stored in the Lakehouse "Files" section cannot be opened directly in Excel unless OneLake File Explorer is installed. Although it is in preview, many customers are successfully using it as it maps OneLake storage as a local drive, allowing seamless Excel integration.

If your organization prefers not to use a preview tool, here are some alternatives:

  • Store Excel files in SharePoint or OneDrive for editing.

  • Use notebooks or pipelines to move files from SharePoint to Lakehouse for reporting.

  • If users only need to view the data, connect Power BI or Excel directly to the Lakehouse for live datasets.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.