Python Script to read from multiple excel sheets and load to Azure SQL DB Tables

Just for example, I created an excel file with two sheets, as the figure below.

There are two solutions for your needs to transfer data from excel sheets to the tables of SQL Azure.

1. To read the data of echo sheet via xlrd (its document) to generate the insert sql, then to execute these insert sqls via pyodbc, pymssql or others. Here is my sample code for reading data from excel sheets in Python.

   import xlrd
   
   book = xlrd.open_workbook("samples.xlsx")
   sheet_names = book.sheet_names()
   for sheet_name in sheet_names:
       sheet = book.sheet_by_name(sheet_name)
       recs = ",".join(["('{0[0]}', {0[1]})".format(sheet.row_values(i)) for i in range(sheet.nrows)])
       insert_sql = f"insert into {sheet_name} (name, age) values {recs};"
       print(insert_sql)
       # Connect to execute the insert data sql via pyodbc, pymssql or others
   

   The result of the code above is.

   insert into Table_A values ('Peason1', 18.0),('Peason2', 19.0),('Peason3', 20.0),('Peason4', 21.0),('Peason5', 22.0),('Peason6', 23.0),('Peason7', 24.0),('Peason8', 25.0),('Peason9', 26.0),('Peason10', 27.0);
   insert into Table_B values ('Peason11', 27.0),('Peason12', 26.0),('Peason13', 25.0),('Peason14', 24.0),('Peason15', 23.0),('Peason16', 22.0),('Peason17', 21.0),('Peason18', 20.0),('Peason19', 19.0),('Peason20', 18.0);
   

   Meanwhile, please refer to Azure offical documents Python SQL Driver and Quickstart: Use Python to query an Azure SQL database to know how to use pyodbc or pymssql to connect and query Azure SQL Database.

2. You can read the sheet data of excel file via pandas, and then to write data to SQL Azure via the function pandas.DataFrame.to_sql through SQLAlchemy connection. Here is my sample code to read the sheet data of excel file to get pandas dataframe.

   import xlrd
   import pandas as pd
   
   file_name = "samples.xlsx"
   col_names = ["name", "age"]
   book = xlrd.open_workbook(file_name)
   sheet_names = book.sheet_names()
   for sheet_name in sheet_names:
       df = pd.read_excel(file_name, sheet_name=sheet_name, index_col=None, header=None, names=col_names)
       print(df)
       # Use df.to_sql with SQLAlchemy connection, that you can do it by yourself via refer to the pandas and SQLAlchemy documents.
   

   The result of the code above is.

          name  age
   0   Peason1   18
   1   Peason2   19
   2   Peason3   20
   3   Peason4   21
   4   Peason5   22
   5   Peason6   23
   6   Peason7   24
   7   Peason8   25
   8   Peason9   26
   9  Peason10   27
          name  age
   0  Peason11   27
   1  Peason12   26
   2  Peason13   25
   3  Peason14   24
   4  Peason15   23
   5  Peason16   22
   6  Peason17   21
   7  Peason18   20
   8  Peason19   19
   9  Peason20   18
   

   For connecting SQL Azure by SQLAlchemy, please refer to SQLAlchemy document Microsoft SQL Server and the other SO thread Connecting to an Azure database using SQLAlchemy in Python.

Hope it helps.