使用 pyodbc 将数据插入 MS Access 时无法在 Excel 文件中找到“Sheet1”

我正在尝试使用 pyodbc 将 Excel 文件中的数据插入到 MS Access 数据库中，但我不断遇到错误，指出在 Excel 文件中找不到“Sheet1”。这是我的代码：

import pyodbc

filename = 'ALC_2022068_06282024-testing copy.xlsx'

sql = r"""INSERT INTO tblALC_test ([dateofnotice], [provnbr])
          SELECT e.[dateofnotice], e.[provnbr]
          FROM [Excel 12.0 Xml;HDR=Yes;Database={xl}].[Sheet1$] AS e;
       """.format(xl=filename)

conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
                      r'DBQ=F:\Kevin\CPD_Night\ALC_Testing.accdb;')    
cursor = conn.cursor()

cursor.execute(sql)
cursor.commit()

cursor.close()
conn.close()

该代码连接到 MS Access 数据库和 Excel 文件，但无法找到“Sheet1”选项卡。我已验证工作表名称和路径，并确保在运行脚本时关闭文件。

我尝试使用 pyodbc 将 Excel 文件中的数据插入到 MS Access 数据库中。我期望脚本在 Excel 文件中找到“Sheet1”并执行 SQL 查询来传输数据。但是，我遇到错误，指示找不到“Sheet1”选项卡。我验证了工作表名称，检查了文件路径，并确保运行脚本时文件已关闭，但问题仍然存在。

你遇到的问题是，你的连接字符串假设你使用的是“Excel 12.0 Xml”驱动程序，但该驱动程序只能读取 .xlsx 文件中 第一行 的数据，用于确定数据类型。它 不能 读取其他行，也不能用于将数据插入数据库。

为了解决这个问题，你需要使用不同的驱动程序来读取 Excel 文件。一种选择是使用 “Microsoft Excel Driver ( .xls, .xlsx, .xlsm, .xlsb)” 驱动程序。这个驱动程序可以读取 .xls 和 .xlsx 文件，并且可以读取所有行的数据。

下面是使用 "Microsoft Excel Driver ( .xls, .xlsx, .xlsm, .xlsb)" 驱动程序修改后的代码：

import pyodbc

filename = r'C:\path\to\your\ALC_2022068_06282024-testing copy.xlsx'  # 请替换成你的 Excel 文件路径

conn_str = (
    r'DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};'
    r'DBQ=' + filename + ';'
)

conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

sql = r"""INSERT INTO tblALC_test ([dateofnotice], [provnbr])
          SELECT [dateofnotice], [provnbr]
          FROM [Sheet1$]
       """

cursor.execute(sql)
cursor.commit()

cursor.close()
conn.close()

# 然后连接到 Access 数据库并将数据插入
conn_str_access = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' 
    r'DBQ=F:\Kevin\CPD_Night\ALC_Testing.accdb;'
)

conn_access = pyodbc.connect(conn_str_access)
cursor_access = conn_access.cursor()

# ... 执行插入数据的操作 ...

cursor_access.close()
conn_access.close()

注意:

• 确保你已经安装了 "Microsoft Excel Driver ( .xls, .xlsx, .xlsm, .xlsb)" 驱动程序。你可以在微软官网上下载。
• 将 filename 变量替换成你的 Excel 文件的完整路径。
• 确保你的 Excel 文件中确实有一个名为 "Sheet1" 的工作表。

如果你的 Excel 文件很大，你可能需要调整 cursor.fast_executemany = True 来提高性能。