AnsweredAssumed Answered

Using Python with pyodbc to export SQL to Excel

Question asked by ryan.hefley on Sep 22, 2017
Latest reply on Oct 2, 2018 by cartman.pth

Hello, so I am very new to using python more and more with GIS. However, I have ran across a problem that I cannot seem to figure out. We are trying an evaluation copy of ArcGIS GeoEvent Server. One cool idea that we are going for is basically reading an excel sheet as it is updated. I am messing around with some feature class that we have not pretaining to actual real time data that changes, and wanted to give it a shot to see if i could export it to excel. 

 

import pyodbc
import csv

connection = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=Server;"
                      "Database=Database;"
                      "uid=username;pwd=password")

cursor = connection.cursor()

cursor.execute("select Name, SITE_NAME,ADDRESS from Power_plants")
data=cursor.fetchall()

with open('dataTester.csv', 'w', newline='') as fp:
    a= csv.writer(fp, delimiter=',')
    for line in data:
        a.writerows(line)

for row in data:
    print (row[0],row[1],row[2])
cursor.close()
connection.close()

Currently this is what I have. I was able to connect to the database fine, however I changed all of that for security. But then I have it print to console, and that also works fine. However when it goes into excel, it somehow makes every letter in the words a new column. See attached screen shot. 


What do I need to do in my code to fix this?? 

Attachments

Outcomes