databricks

[KSB]

Hi Team,

Having excel file in sharepoint folder, and has to insert excel data into SQL table from databricks notebook . can i have clear steps on it. Dont have access to Azure Active Directory.  can anyone gives solution without using AZURE Active Directory.

inserting excel data from sharepoint to SQL table through power automate, working fine , but when having 10000 records, for inserting data taking 4-5 hrs of time.

[Stefan-Koch]

Hi KSB

You could read direkt with Databricks from Excel Sharepoint with Graph API. Here is one possible way: https://community.databricks.com/t5/data-engineering/load-data-from-sharepoint-site-to-delta-table-i...

However, you need the proper rights/grants to use the Graph API. 

Another approach: Copy the Excel from Sharepoint to your Datalake with Power Automate. Then, read the data with Databricks into a Delta Table. If you have your Excel file in the data lake, the code could look like this: 

pip install openpyxl

import pandas as pd

schema_name = f"my_catalog.my_schema"
file_location_xls   = "/Volumes/demo_shared/samples/files/FinancialsSampleData.xlsx"

# create pandas dataframe
pdf = pd.read_excel(file_location_xls, sheet_name="Financials1")

# convert pandas dataframe to pyspark dataframe
df = spark.createDataFrame(pdf)

df.write.saveAsTable(f"{schema_name}.financials_sheet1")