Hello.  I am trying to export two tables from an Access database to excel.  I have been able to connect to the database using the code provided.  But I have not been able to export the table to excel.  I also want to rename the field names that will display in excel (P_UpNumber = Upstream Manhole, P_DownNumber = Downstream Manhole).

import pyodbc
# MS Access DB connection
pyodbc.lowercase = False
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' +
    r'DBQ=C:\Desktop\PACP.MDB;')
# Open cursor and execute SQL
cursor = conn.cursor()
cursor.execute('select P_UpNumber, P_DownNumber FROM T_PIPES');
for row in cursor.fetchall():
    print (row)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Prior to my machine being upgraded to Windows 10, I had  code that exported the rows to a csv without field names.  But now it does not export it and I do not receive any error messages.  My guess is that it has to do with the Access now being 64 Bit.  Any help will be much appreciated.  Thank you.

# MS Access DB connection pyodbc . lowercase = False conn = pyodbc . connect ( r 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' + r 'DBQ=DBQ=C:\Desktop\PACP.MDB;' ) # Open cursor and execute SQL cursor = conn . cursor ( ) cursor . execute ( 'select P_UpNumber, P_DownNumber FROM T_PIPES' ) ; wb = xlwt . Workbook ( ) ws = wb . add_sheet ( "T_PIPES" ) # use table name for worksheet name cols = [ 'Upstream Manhole' , 'Downstream Manhole' ] # renamed colum headings wbRow = 0 # counter for workbook row ws . write ( wbRow , 0 , cols [ 0 ] ) # write column heading to first row ws . write ( wbRow , 1 , cols [ 1 ] ) for row in cursor . fetchall ( ) : wbRow += 1 # increment workbook row counter ws . write ( wbRow , 0 , row [ 0 ] ) ws . write ( wbRow , 1 , row [ 1 ] ) wb . save ( r "C:\Path\to\PACP.xls" ) ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍

Was your script connecting to the database and printing results?

Puzzling.  What if you try writing to a file instead of printing?  This would create a tab delimited file with an xls extension Excel may give a "file format" error, but it should open it.

import pyodbc
# MS Access DB connection
pyodbc.lowercase = False
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' +
    r'DBQ=DBQ=C:\Desktop\PACP.MDB;')
# Open cursor and execute SQL
cursor = conn.cursor()
cursor.execute('select P_UpNumber, P_DownNumber FROM T_PIPES');
# open a file for writing
fw = open(r"C:\Desktop\PACP.xls","w") # edit path as needed
fw.write("{}\t{}\n".format('Upstream Manhole', 'Downstream Manhole')) # write a header row
for row in cursor.fetchall(): # loop through Access
    fw.write("{}\t{}\n".format(row[0],row[1]))
fw.close() # close file‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You may wish to verify Access as being 64 or 32 bit.  You should also be using a matching 64/32 bit version of Python.

This version is like Randy's above but uses csv so you wont get that file open error.  Change the pathtocsv variable to the path of your csv.

Edited for python 3 syntax

import pyodbc
import csv
csvpath = r'path to your csv'
# create a csv file (python 2)
# writer = csv.writer(open(csvpath, "wb"), delimiter=",")
# create a csv file (python 3)
writer = csv.writer(open(csvpath, "w"), delimiter=",")
# write a header row
writer.writerow(['Upstream Manhole', 'Downstream Manhole'])
# MS Access DB connection
pyodbc.lowercase = False
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' +
    r'DBQ=C:\Desktop\PACP.MDB;')
# Open cursor and execute SQL
cursor = conn.cursor()
cursor.execute('select P_UpNumber, P_DownNumber FROM T_PIPES');
# output cursor results to csv
for row in cursor.fetchall():
   writer.writerow([row[0], row[1]])
del writer‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Hello all.  I hope you are staying safe in this crazy 2020.  I came back to this issue I was having and discovered that my results were being exported to a csv.  What I found out is that it is being exported the path where I have the .py file saved (B:\Scripts\Python\CCTV).  I would like to save it to a different path (B:\Planning\GIS\CCTV) but after searching I have not been able to find the right code.  Below is the updated code

# Exporting relevant fields from CCTV Access (Master PACP 1.mdb) table to csv.
import pyodbc
import csv
import pandas as pd
# MS Access DB connection
print("Connecting to database.")
pyodbc.lowercase = False
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' +
    r'DBQ=B:\Planning\GIS\CCTV\MASTER PACP 1.MDB;')
# Open cursor and execute SQL
print("Executing SQL.")
cursor = conn.cursor()
cursor.execute('select P_PipeID, P_ChangeDate, P_CreateDate, P_UpNumber, P_DownNumber, P_LocationDetails,\
P_Material, P_PlaceName, P_RoadName, P_ScheduleLength, P_Size, P_YearLaid FROM T_PIPES');
# Open csv and iterate through results.
print("Converting to csv.")
with open('PIPES.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    for row in cursor.fetchall():
        writer.writerow(row)
        #writer.writerow([row[0], row[2]])
cursor.close()
conn.close()
# Adding headers to csv
print("Adding header to PIPES.csv.")
with open('PIPES.csv', newline='') as f:
    r = csv.reader(f)
    data = [line for line in r]
with open('PIPES.csv', 'w', newline='') as f:
    w = csv.writer(f)
    w.writerow(['PipeID', 'ChangeDate', 'CreateDate', 'UpManhole', 'DownManhole', 'LocationDetail',\
    'Material', 'PlaceName', 'RoadName', 'Length', 'Size', 'YearInstall'])
    w.writerows(data)
df = pd.read_csv('PIPES.csv')
df['SLID'] = df['UpManhole'] + df['DownManhole']
print("Done")