import io import requests import openpyxl import pandas as pd from sqlalchemy import create_engine import urllib #Target Server Connection String params = urllib.parse.quote_plus(r'Driver={ODBC Driver 17 for SQL Server};Server=servernamehere,1433;Uid=adminuser;Pwd=testpswd;database=Stage;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;') #Blob snapshot URL pulled from azure blob input_excel = requests.get('https://testdata.blob.core.uscloudapi.net/testing6data/testing.xlsx, stream=True) conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params) engn = create_engine(conn_str,echo=True) # Loads the excel file into the eb variable wb = openpyxl.load_workbook(filename=io.BytesIO(input_excel.content), data_only='True') tables_dict = {} # Go through each worksheet in the workbook for ws_name in wb.sheetnames: #print(wb.sheetnames) print("") print(f"worksheet name: {ws_name}") ws = wb[ws_name] print(f"tables in worksheet: {len(ws._tables)}") # Get each table in the worksheet for tbl in ws._tables: # First, add some info about the table to the dictionary tables_dict[tbl.name] = { 'table_name': tbl.name, 'worksheet': ws_name, 'num_cols': len(tbl.tableColumns), 'table_range': tbl.ref} # Grab the 'data' from the table data = ws[tbl.ref] # Now convert the table 'data' to a Pandas DataFrame # First get a list of all rows, including the first header row rows_list = [] for row in data: # Get a list of all columns in each row cols = [] for col in row: cols.append(col.value) rows_list.append(cols) # Create a pandas dataframe from the rows_list. # The first row is the column names df = pd.DataFrame(data=rows_list[1:], index=None, columns=rows_list[0]) # Add the dataframe to the dictionary of tables # df = df.dropna(how='any') df.to_sql(tbl.name, engn, if_exists='append',schema='testing') print(df) tables_dict[tbl.name]['dataframe'] = df print('-----------------------------done------------------------')