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.

     

burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

Hi Felpan ,

Pulling 1,200 tables from 800-900 Excel files and consolidating them into a single table is a huge and complex workload. Also, the situation you are experiencing with sharing files within Lakehouse is understandable. Below I share my suggestions for both issues:

📥1. How to Efficiently Pull Excel Files from SharePoint

🔹Current Status:

  • You have been successful with Dataflow Gen2, but it is not sustainable as it requires a large number of queries.

🔹Proposed Approach:

Azure Data Factory (ADF) veya Azure Logic Apps + Azure İşlevi

  • List all Excel files with SharePoint API.
  • Loop through each file, read the tables in it and write them to Azure Data Lake or SQL DB.
  • You can automate this process with Azure Function and schedule it with ADF.

🔹Alternative:

  • You can pull files from SharePoint with Office365-REST-Python-Client or msal using Python and process and consolidate them with pandas.

📤2. Sharing Excel File Under Lakehouse “Files”

🔹The current situation:

  • You were able to work with Excel Online, but this feature is still in preview.
  • That's why your organization is cautious.

🔹Recommended Approach:

  • To share a file via OneLake, grant the user the Viewer or Contributor role in the workspace where the file is located.
  • Alternatively, you can copy the file to OneDrive or SharePoint and share it from there.
  • Creating a report that reads the file via Power BI and sharing this report with the App may also be a solution.

✅Summary Recommendations:

 

Goal Recommended Approach
Extracting bulk data from SharePoint ;Azure Data Factory + Azure Function or Python script
Consolidating Excel files ; Merge using pandas or load into a SQL staging table
Sharing Excel files from Lakehouse;Grant workspace access + share via OneLake link or use SharePoint as an alternative