need to write to ACCESS DB with xlsx file I tried this method: Exporting or writing data from python pandas data frame to MS Access table however, it occurred error.
# Setup access variable
connstr = (r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
r"DBQ=C:\Users\tester\master_db.accdb")
# Setup pyodbc variable
cnxn = pyodbc.connect(connstr)
sql = 'SELECT * FROM tbl_testing'
# Read access data
dfins = pd.read_sql(sql, cnxn)
for index, row in dfins.iterrows():
with cnxn.cursor() as crsr:
crsr.execute("INSER INTO tbl_testing (Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22,Col23,Col24,Col25,Col26,Col27,Col28,Col29,Col30,Col31,Col32,Col33,Col34,Col35,Col36,Col37,Col38,Col39,Col40,Col41,Col42,Col43) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", row["col1"],row["col2"],row["col3"],row["col4"],row["col5"],row["col6"],row["col7"],row["col8"],row["col9"],row["col10"],row["col11"],row["col12"],row["col13"],row["col14"],row["col15"],row["col16"],row["col17"],row["col18"],row["col19"],row["col20"],row["col21"],row["col22"],row["col23"],row["col24"],row["col25"],row["col26"],row["col27"],row["col28"],row["col29"],row["col30"],row["col31"],row["col32"],row["col33"],row["col34"],row["col35"],row["col36"],row["col37"],row["col38"],row["col39"],row["col40"],row["col41"],row["col42"],row["col43"])
error message: ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (-3500) (SQLPrepare)")
Can anyone teach me how to fix this? I'm also willing to learn other solution.