Guangning Yu's Blog
Home
Code
Data
Setup
Industry
MachineLearning
Archive
Load Excel file into SQL Server
2019-03-07 15:57:56
|
Python
``` import pandas as pd import pyodbc import sqlalchemy import urllib def get_sqlalchemy_engine(driver, server, uid, pwd, database): conn_str = 'DRIVER={};SERVER={};UID={};PWD={};DATABASE={}'.format(driver, server, uid, pwd, database) quoted = urllib.parse.quote_plus(conn_str) engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted)) return engine if __name__ == '__main__': # create engine driver = 'ODBC Driver 17 for SQL Server' server = 'xxx' uid = 'xxx' pwd = 'xxx' database = 'xxx' engine = get_sqlalchemy_engine(driver, server, uid, pwd, database) # read excel file_path = 'xxx' df = pd.read_excel(file_path) # load into SQL Server schema_name = 'xxx' table_name = 'xxx' df.to_sql(table_name, schema=schema_name, con=engine, index=False, if_exists='replace') ```
Previous:
Windows cmd
Next:
Setup SQL Server and pyodbc
