Импортируйте все файлы xlsx из папки в базу данных с помощью python.

---

Как обработать несколько файлов excel из папки и загрузить в базу данных с помощью python.

Что я уже пробовал:

  all_data = pd.DataFrame()

  for f in glob.glob("*.xlsx"):
     df = pd.read_excel(f)
     all_data = all_data.append(df,ignore_index=True)

# now save the data frame
  writer = pd.ExcelWriter('data.xlsx')
  all_data.to_excel(writer,'Sheet1',index = False)
  writer.save()

  data = pd.read_excel('c:\\users\sam\desktop\data.xlsx')

 # rename columns
 data = data.rename(columns={'posteddate': 'posteddate',
                          'totalamt': 'totalamt',
                          'qty': 'qty',
                          'itemstatus': 'itemstatus',
                          'groupitem': 'groupitem',
                          'supdlno': 'supdlno',
                          'barcode': 'barcode',
                          'unitprice': 'unitprice',
                          'subtotal': 'subtotal',
                          'itemnumber': 'itemnumber'})

 # open the workbook and define the worksheet
 book = xlrd.open_workbook("c:\\users\sam\desktop\data.xlsx")
 sheet = book.sheet_by_name("sheet1")

 query1 = """
 create table [leaf] (

 posteddate varchar(255),
 totalamt varchar(255),
 qty varchar(255),
 itemstatus varchar(255),
 groupitem varchar(255),
 supdlno varchar(255),
 barcode varchar(255),
 unitprice varchar(255),
 subtotal varchar(255),
 itemnumber varchar(255)
 )"""

 query = """
 insert into [leaf] (
   posteddate,
   totalamt,
   qty,
   itemstatus,
   groupitem,
   supdlno,
   barcode,
   unitprice,
   subtotal,
   itemnumber
  ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""

 # execute create table
   try:
      cursor.execute(query1)
      conn.commit()
   except pypyodbc.programmingerror:
      pass

 # grab existing row count in the database for validation later
 cursor.execute("select count(*) from leaf")
 before_import = cursor.fetchone()

 for r in range(1, sheet.nrows):
  posteddate = sheet.cell(r,1).value
  totalamt= sheet.cell(r,4).value
  qty = sheet.cell(r,8).value
  itemstatus= sheet.cell(r,12).value
  groupitem= sheet.cell(r,14).value
  supdlno= sheet.cell(r,16).value
  barcode= sheet.cell(r,17).value
  unitprice= sheet.cell(r,18).value
  subtotal= sheet.cell(r,19).value
  itemnumber= sheet.cell(r,20).value
  # assign values from each row

  values = (posteddate, totalamt, qty, itemstatus,
 groupitem, supdlno, barcode, unitprice, subtotal, itemnumber)

     # execute sql query

  cursor.execute(query, values)

     # commit the transaction
      conn.commit()

 # if you want to check if all rows are imported
 cursor.execute("select count(*) from leaf")
 result = cursor.fetchone()

 print((result[0] - before_import[0]) == len(data.index))  # should be true

 # close the database connection
 conn.close()

---