topic Re: MS Access tables to Excel in Python Questions

MS Access tables to Excel

GIS_geek — Sat, 11 Dec 2021 15:44:04 GMT

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.

Re: MS Access tables to Excel

RandyBurton — Sat, 11 Dec 2021 15:44:06 GMT

One option would be to use the xlwt module, something like:

import pyodbc
import xlwt

# 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?

Re: MS Access tables to Excel

GIS_geek — Thu, 12 Mar 2020 22:32:01 GMT

Thanks Randy.

The code you provided did create the xls with column names. Though it did not bring the data from the Access table. To answer your question, my script does connect the the database and prints the results. See attachment. Not sure why the data is not exporting to the xls.

Re: MS Access tables to Excel

RandyBurton — Sat, 11 Dec 2021 15:44:09 GMT

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.

Re: MS Access tables to Excel

Anonymous User — Sat, 11 Dec 2021 15:44:12 GMT

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: MS Access tables to Excel

GIS_geek — Fri, 13 Mar 2020 20:40:24 GMT

Randy, your updated code worked. But it concatenates the fields.

And I confirmed, the Access I have is 64 bit and I am using the conda package that comes with ArcGIS Pro.

Re: MS Access tables to Excel

GIS_geek — Fri, 13 Mar 2020 20:46:47 GMT

Thanks for contributing Jeff.

I get the attached error on the following line

writer.writerow(['Upstream Manhole', 'Downstream Manhole'])‍‍‍

Re: MS Access tables to Excel

Anonymous User — Fri, 13 Mar 2020 21:07:19 GMT

Yeah, the 'wb' is from python 2 and is indicating that it is in binary mode and is causing that error. In python 3 its just 'w' so just remove the 'b' in the writer and it should work ok.

Re: MS Access tables to Excel

GIS_geek — Tue, 01 Dec 2020 00:48:30 GMT

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")