Using Python with pyodbc to export SQL to Excel

[RyanHefley]

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

[MitchHolley1]

I think you need to remove the ( , newline='') from the open cursor.  Try the code below and see what you get. 

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

[RyanHefley]

Removing (, newline=' ') did nothing. I was expecting it to do something, at least throw an error or something. But the file stays in the same format with each letter having it's own cell in the excel sheet.

[RomanSergiiko]

withuot newline=' ', script will create one line with data and one empty line following it

[JamesCrandall]

Even easier would be to just convert your Feature Class to a numpy array and export that to .csv.

import arcpy
import sys, os
import numpy
import pandas as pd

ws = r'myworkspace.sde'
fcname = r'MyFeatureClass'
input_fc = os.path.join(ws, fcname)

#create the numpy array
narr = arcpy.da.FeatureClassToNumPyArray(input_fc, ("Field1", "Field2", "Field3"))

#I prefer to use pandas to export from.  personal preference is all
df = pd.DataFrame(narr)
df.to_csv('H:\somefolder\dfoutput.csv', header=None)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[RandyBurton]

Since you are using SQL Server, would it be possible to create a view of your feature/table (removing fields like geometry, blobs, rasters, etc. that you don't want), and then set up a direct ODBC connection between Excel and the view?

[JamesCrandall]

Alternatively...

Why are you creating an external process (a .py script/tool) to do this?  This seems like it should just run as a time-event on the sql server itself.

[RyanHefley]

I would like to do an external script mainly because this data that I will be pulling is not part of ArcGIS. But rather an outside source. We have internet "meter" points surrounding our state. The up and down speed etc are being pulled to another SQL server from a 3rd party... However, I was able to pull in the data using SQL with Excel just by connecting into it. 

[JamesCrandall]

It's all still contained in an RDBMS with tables storing rows of features/attributes.  It just so happens there's a column that holds spatial information.  Glad to hear you've worked it out!

[RomanSergiiko]

You need to replace 

 a.writerows(line)

with:

 a.writerow(line)