Here’s an example of how to write data from an Excel file to a SQL database using Python and the pandas library
import pandas as pd import pyodbc # Connect to SQL Server server_name = "server_name" database_name = "database_name" username = "username" password = "password" conn_str = ( f"DRIVER={{ODBC Driver 17 for SQL Server}};" f"SERVER={server_name};" f"DATABASE={database_name};" f"UID={username};" f"PWD={password}" ) cnxn = pyodbc.connect(conn_str) # Read Excel file into a pandas dataframe excel_file = "data.xlsx" df = pd.read_excel(excel_file) # Write dataframe to SQL Server table table_name = "table_name" df.to_sql(table_name, cnxn, if_exists="append", index=False) # Close SQL Server connection cnxn.close()
In this example, we first connect to the SQL Server using pyodbc and create a connection string. We then read an Excel file into a pandas dataframe using the read_excel function. Finally, we write the contents of the dataframe to a SQL Server table using the to_sql function.
Note that in this example, the if_exists parameter is set to “append” to add the data to an existing table. If the table does not exist, you can set this parameter to “replace” to create a new table with the same name and schema as the dataframe.