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