Python 3.0将Excel文件导入Access文件

Question

我使用Python 3创建了一个Excel(.xlsx(文件。现在我想将此Excel文件转换为Access(.accdb(文件。我知道Access可以导入Excel文件，但我正在尝试使用Python来实现自动化。

Excel文件中有3张图纸。我已经在Excel和Access之间建立了连接，但不知道如何在Access文件中插入图纸/值。非常感谢你的帮助！非常感谢！

writer=pd.ExcelWriter('ETABS.xlsx',engine='xlsxwriter')
pointcord.to_excel(writer, sheet_name='Sheet1')
jointreaction.to_excel(writer, sheet_name='Sheet2')
writer.save()
import pyodbc
pyodbc.drivers()
DBFile = r'C:UsersnyeungDocumentswsp.codingworkshop.pythonClassNotebooksETABS.accdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBFile)
exFile = r'C:UsersnyeungDocumentswsp.codingworkshop.pythonClassNotebooksETABS.xlsx'
conn1 = pyodbc.connect('DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ='+exFile,autocommit=True)
curs1 = conn1.cursor()
# the following returns list of tuples
excel_results = curs1.execute().fetchall()
curs.executemany("INSERT INTO ETABS values (?,?)", excel_results)
conn.commit()
for row in curs1.tables():
print (row.table_name)

Answer 1

考虑纯SQL，因为JET/ACE引擎允许直接查询外部工作簿和数据库。由于使用了相同的底层引擎，所以您可以从MS Access连接或Excel连接中执行此操作。无需逐行追加光标。

以下假设所有Excel工作表列与所有Access表列按相同顺序一一匹配。如果在Access表中使用自动编号字段，请考虑在追加查询的INSERT INTO和SELECT子句中显式声明列。

访问连接 (本地DB表附加(

DBFile = r'C:UsersnyeungDocumentswsp.codingworkshop.pythonClassNotebooksETABS.accdb'
exFile = r'C:UsersnyeungDocumentswsp.codingworkshop.pythonClassNotebooksETABS.xlsx'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBFile)
# APPEND TO LOCAL TABLE
sql = """INSERT INTO ETABS
SELECT * FROM [Excel 12.0 Xml; HDR = Yes;Database={myfile}].[Sheet1$];
"""
curs = conn1.cursor()
curs.execute(sql.format(myfile = exFile))
conn.commit()

顺便说一下，您甚至可以运行生成表查询，而不是附加到现有表：

SELECT * INTO myNewTable FROM [Excel 12.0 Xml; HDR=Yes; Database={myfile}].[Sheet1$]

---

Excel连接(外部DB表附加(

DBFile = r'C:UsersnyeungDocumentswsp.codingworkshop.pythonClassNotebooksETABS.accdb'
exFile = r'C:UsersnyeungDocumentswsp.codingworkshop.pythonClassNotebooksETABS.xlsx'
conn = pyodbc.connect('DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ='+exFile)
curs1 = conn1.cursor()
# APPEND TO EXTERNAL TABLE
sql = """INSERT INTO [{myfile}].[ETABS]
SELECT * FROM [Sheet1$];
"""
curs = conn.cursor()
curs.execute(sql.format(myfile = DBFile))
conn.commit()