SQL Query appending from Python to Access

Question

I am trying to read an Excel file into a Python pandas data frame, then append the data frame to a pre-existing Access Database.

I have been receiving the following error:

Error('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106) (SQLBindParameter)')

I have read that this error is typically a result of pyodbc 4.0.25. I have downgraded my version to pyodbc 4.0.24 and I am still receiving the error.

import glob
import os
import pandas as pd
import time
import pyodbc

# specify the folder that the files are sitting in 
list_of_files = glob.glob(r'C:\Users\CraigG\Test\*xlsx') 

# define the newest file in the folder
filename = min(list_of_files, key=os.path.getmtime)

# put excel file in data frame
df = pd.read_excel(filename)

print(pyodbc.version)

conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
                      r'DBQ=C:\\Users\\CraigG\\Test\\Db\\Master.accdb;')
cursor = conn.cursor()

for index, row in df.iterrows():

   AccountNum = row["Account #"]
   CustomerNum = row["Customer #"]
   CustomerName = row["Customer Name"]
   AccountName = row["Account Name"]
   SalesRegisterNum = row["Sales Register #"]
   InvoiceDate = row["Invoice Date"]
   BillingMonthDate = row["BillingMonthDate"]
   WrittenBy = row["Written By"]
   Mfr = row["Mfr"]
   CatalogNo = row["CatalogNo"]
   LineType = row["LineType"]
   UPC = row["UPC"]
   QTYShip = row["QTY Ship"] 
   Price = row["Price"]
   PriceUOM = row["Price UOM"]
   ExtenedPrice = row["Extened Price"]
   Cost = row["Cost"]
   CostUOM = row["Cost UOM"]
   ExtendedCost = row["Extended Cost"] 
   SPACost = row["SPA Cost"]
   SPACostUOM = row["SPA Cost UOM"]
   ExtendedSPACost = row["Extended SPA Cost"]
   PCNum = row["PC #"]

   sql = "INSERT INTO Master ([Account #], [Customer #], [Customer Name], [Account Name], [Sales Register #], [Invoice Date], [BillingMonthDate], [Written By], [Mfr], [CatalogNo], [LineType], [UPC], [QTY Ship], [Price], [Price UOM], [Extened Price], [Cost], [Cost UOM], [Extended Cost], [SPA Cost], [SPA Cost UOM], [Extended SPA Cost], [PC #]) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

   params = (AccountNum, CustomerNum, CustomerName, AccountName, 
             SalesRegisterNum, InvoiceDate, BillingMonthDate, WrittenBy, Mfr, 
             CatalogNo, LineType, UPC, QTYShip, Price, PriceUOM, ExtenedPrice, Cost, 
             CostUOM, ExtendedCost, SPACost, SPACostUOM, ExtendedSPACost, PCNum)

   cursor.execute(sql, params)
   cursor.commit()

Does your DataFrame contain values that might have originally been strings (e.g. "Account #", "Customer #") but wound up in your DataFrame as integers whose value is greater than 2147483647? If so, then pyodbc will try to pass them to the Access ODBC driver as BIGINT (64-bit signed) but the driver won't accept them. — Gord Thompson
– Gord Thompson, Commented Feb 7, 2019 at 21:43

Parfait · Accepted Answer · 2019-02-08 02:12:12Z

No need for Pandas to serve as medium. Leave the library for data science! MS Access works great with its sibling, MS Excel. Simply query the workbook for a pure SQL call without any looping.

Below assumes Excel data maintains the same column names as Access table starting in cell A1 in a worksheet named Sheet1. Adjust FROM clause as needed.

import glob
import os
import pyodbc

# specify the folder that the files are sitting in 
list_of_files = glob.glob(r'C:\Users\CraigG\Test\*xlsx') 

# define the newest file in the folder
filename = min(list_of_files, key=os.path.getmtime)

sql = r"""INSERT INTO Master ([Account #], [Customer #], [Customer Name], [Account Name], [Sales Register #], 
                              [Invoice Date], [BillingMonthDate], [Written By], [Mfr], [CatalogNo], [LineType], 
                              [UPC], [QTY Ship], [Price], [Price UOM], [Extened Price], [Cost], [Cost UOM], 
                              [Extended Cost], [SPA Cost], [SPA Cost UOM], [Extended SPA Cost], [PC #]) 
          SELECT e.[Account #], e.[Customer #], e.[Customer Name], e.[Account Name], e.[Sales Register #], 
                 e.[Invoice Date], e.[BillingMonthDate], e.[Written By], e.[Mfr], e.[CatalogNo], e.[LineType], 
                 e.[UPC], e.[QTY Ship], e.[Price], e.[Price UOM], e.[Extened Price], e.[Cost], e.[Cost UOM], 
                 e.[Extended Cost], e.[SPA Cost], e.[SPA Cost UOM], e.[Extended SPA Cost], e.[PC #]

          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=C:\Users\CraigG\Test\Db\Master.accdb;')    
cursor = conn.cursor()

cursor.execute(sql)
cursor.commit()

cursor.close()
conn.close()

Collectives™ on Stack Overflow

SQL Query appending from Python to Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related