Load Data from Sharepoint Site to Delta table in Databricks

[Aidonis]

Hi New to the community so sorry if my post lacks detail.

I am trying to create a connection between databricks and a sharepoint site to read excel files into a delta table

I can see there is a FiveTran partner connection that we can use to get sharepoint data into databricks but I wanted to ask the community if they know of any other ways of connecting sharepoint to databricks.

Thanks,

[Ajay-Pandey · Accepted solution]

Hi @Aidan Heffernan​ you can use Sharepoint Rest API to connect with databricks

Please refer below code-

 from office365.sharepoint.client_context import ClientContext
 from office365.runtime.auth.client_credential import ClientCredential
    
 sharepoint_base_url = "https://site.sharepoint.com/sites/group/"
 READ_DIR = "/sites/Group/Shared Documents/Folder/Subfolder
    
 # setup sharepoint access
 client_credentials = ClientCredential(client_id, client_secret)
 ctx = ClientContext(sharepoint_base_url).with_credentials(client_credentials)"
    
 def download_sharepoint_file(file_url):
     temp_dir = tempfile.mkdtemp()
     download_path = os.path.join(temp_dir, os.path.basename(file_url))
     with open(download_path, "wb") as local_file:
         file = ctx.web.get_file_by_server_relative_path(file_url).download(local_file).execute_query()
     return download_path
    

[Aidonis]

Thanks for this @Ajay Pandey​  this worked a charm 😁