使用python将文件夹中的所有xlsx文件导入数据库。 [英] Import all xlsx file from a folder into database using python.

问题描述

如何从文件夹处理多个excel文件并使用python加载到数据库中。



我尝试过：

How to process multiple excel file from a folder and load into database using python.

What I have tried:

  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()

推荐答案

如果是sql server数据库，请创建一个SSIS包到它。这是绝对最简单的方法。

If it's a sql server database, create a SSIS package to to it. That's the absolute easiest way to do it.

这篇关于使用python将文件夹中的所有xlsx文件导入数据库。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！