MS Access tables to Excel

GIS_geek

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

<SPAN class="keyword token">import</SPAN> pyodbc

<SPAN class="comment token"># MS Access DB connection</SPAN>
pyodbc<SPAN class="punctuation token">.</SPAN>lowercase <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">False</SPAN>
conn <SPAN class="operator token">=</SPAN> pyodbc<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN>r<SPAN class="string token">'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'</SPAN> <SPAN class="operator token">+</SPAN>
    r<SPAN class="string token">'DBQ=C:\Desktop\PACP.MDB;'</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Open cursor and execute SQL</SPAN>
cursor <SPAN class="operator token">=</SPAN> conn<SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
cursor<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'select P_UpNumber, P_DownNumber FROM T_PIPES'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">.</SPAN>fetchall<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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.

GIS_geek

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