topic Re: Using Python with pyodbc to export SQL to Excel in Python Questions

Using Python with pyodbc to export SQL to Excel

RyanHefley — Sat, 11 Dec 2021 08:14:03 GMT

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

Re: Using Python with pyodbc to export SQL to Excel

MitchHolley1 — Sat, 11 Dec 2021 08:14:06 GMT

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

Re: Using Python with pyodbc to export SQL to Excel

JamesCrandall — Sat, 11 Dec 2021 08:14:09 GMT

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

Re: Using Python with pyodbc to export SQL to Excel

RandyBurton — Fri, 22 Sep 2017 19:29:03 GMT

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?

Re: Using Python with pyodbc to export SQL to Excel

RyanHefley — Fri, 22 Sep 2017 19:38:53 GMT

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.

Re: Using Python with pyodbc to export SQL to Excel

JamesCrandall — Fri, 22 Sep 2017 20:26:08 GMT

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.

Re: Using Python with pyodbc to export SQL to Excel

RyanHefley — Fri, 22 Sep 2017 21:02:23 GMT

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.

Re: Using Python with pyodbc to export SQL to Excel

JamesCrandall — Mon, 25 Sep 2017 13:48:36 GMT

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!

Re: Using Python with pyodbc to export SQL to Excel

RomanSergiiko — Tue, 02 Oct 2018 12:04:23 GMT

You need to replace

 a.writerows(line)

with:

 a.writerow(line)

Re: Using Python with pyodbc to export SQL to Excel

RomanSergiiko — Tue, 02 Oct 2018 12:12:48 GMT

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