CODEWITHSUNDEEP
pythonexcelpandaspyodbcsqlalchemy-access
John Mark Johnson
John Mark Johnson

Reputation: 21

How to import a directory of excel files into MS ACCESS with python?

I have been trying to import multiple files into MS Access with python but keep getting the following error:

DBAPIError: (pyodbc.Error) ('HYC00', '[HY00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106) (SQLBindParameter)')

Here is the code I used:

import pandas as pd
import urllib
import sqlalchemy as sa
import openpyxl
from sqlalchemy import *

xls_path = r'C:\Users\JMJ\Desktop\Excel_docs
df = pd.read_excel(xls_path, engine = openpyxl)

connection_string = (r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
                     r'DBQ=C:\Users\JMJ\Desktop\testdb_python.accdb;'
                     r'ExtendedAnsiSQL=1;'
)
connection_url = sa.engine.URL.create(
     "access+pyodbc",
     query={"odbc_connect": connection_string}
)
engine = sa.create_engine(connection_url)
df.to_sql('New_table', engine)

Does anyone know how to get this code running?

Upvotes: 0

Views: 75

Answers (0)

Related Questions