0

i am trying to import a excel data in to ms access file using python.

below my code;

import pandas as pd
import urllib.parse
import sqlalchemy
from sqlalchemy import create_engine
import os

os.getcwd()
Chd= os.chdir('D:/NX_BACKWORK/Feeder Setup_PROCESS')
Chd = os.getcwd()

#xls = pd.ExcelFile('Upload-Data.xlsx',engine='openpyxl')
df1 = pd.read_excel('123.xlsx', sheet_name='T_PBAR')

print(df1.head(10))
print('open mdb')

cnnstr = (
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=D:\NX_BACKWORK\Feeder Setup_PROCESS\MODEL.mdb"
)

cnnurl = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(cnnstr)}"
acc_engine = create_engine(cnnurl)
print('writing to access')
df1.to_sql("T_PBAR",acc_engine, if_exists='append')

print('write complete')

this is error i am facing in below line

df1.to_sql("T_PBAR",acc_engine, if_exists='append')

Exception has occurred: InterfaceError (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/20/rvf5) pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

The above exception was the direct cause of the following exception:

File "C:\Users\zen\OneDrive\Documents\Python\PandasAPP\TESTNGpy.py", line 67, in df1.to_sql("T_PBAR",acc_engine, if_exists='append') sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/20/rvf5)

need a support

0

1 Answer 1

0

i used to check the below cmd.

print(pyodbc.drivers())

in console output: ['SQL Server']

go to control panel system security > Administrative Tools> ODBC datasource (32/64 bit) > Driver > check what is installed in it if its not install the Microsoft Access Database Engine.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.