Guangning Yu's Blog

Load Excel file into SQL Server

2019-03-07 07:57:56  |  Python
  1. import pandas as pd
  2. import pyodbc
  3. import sqlalchemy
  4. import urllib
  5. def get_sqlalchemy_engine(driver, server, uid, pwd, database):
  6. conn_str = 'DRIVER={};SERVER={};UID={};PWD={};DATABASE={}'.format(driver, server, uid, pwd, database)
  7. quoted = urllib.parse.quote_plus(conn_str)
  8. engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
  9. return engine
  10. if __name__ == '__main__':
  11. # create engine
  12. driver = 'ODBC Driver 17 for SQL Server'
  13. server = 'xxx'
  14. uid = 'xxx'
  15. pwd = 'xxx'
  16. database = 'xxx'
  17. engine = get_sqlalchemy_engine(driver, server, uid, pwd, database)
  18. # read excel
  19. file_path = 'xxx'
  20. df = pd.read_excel(file_path)
  21. # load into SQL Server
  22. schema_name = 'xxx'
  23. table_name = 'xxx'
  24. df.to_sql(table_name, schema=schema_name, con=engine, index=False, if_exists='replace')