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?? PyODBC_Excel.JPG PyODBC_Code.JPG

You need to replace a . writerows ( line ) with: a . writerow ( line )

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

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

connection <SPAN class="operator token">=</SPAN> pyodbc<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Driver={SQL Server Native Client 11.0};"</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="string token">"Server=Server;"</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="string token">"Database=Database;"</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="string token">"uid=username;pwd=password"</SPAN><SPAN class="punctuation token">)</SPAN>

cursor <SPAN class="operator token">=</SPAN> connection<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 Name, SITE_NAME,ADDRESS from Power_plants"</SPAN><SPAN class="punctuation token">)</SPAN>
data<SPAN class="operator token">=</SPAN>cursor<SPAN class="punctuation token">.</SPAN>fetchall<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">with</SPAN> open<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'dataTester.csv'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'w'</SPAN><SPAN class="punctuation token">,</SPAN> newline<SPAN class="operator token">=</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> fp<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; a<SPAN class="operator token">=</SPAN> csv<SPAN class="punctuation token">.</SPAN>writer<SPAN class="punctuation token">(</SPAN>fp<SPAN class="punctuation token">,</SPAN> delimiter<SPAN class="operator token">=</SPAN><SPAN class="string token">','</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> line <SPAN class="keyword token">in</SPAN> data<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a<SPAN class="punctuation token">.</SPAN>writerows<SPAN class="punctuation token">(</SPAN>line<SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> data<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
cursor<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
connection<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><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>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

PyODBC_Excel.JPG

PyODBC_Code.JPG

Trouver plus de publications taguées avec

Accepted answers

All comments

RomanSergiiko

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

RomanSergiiko

You need to replace

 a<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">.</SPAN>writerows<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">(</SPAN>line<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">)</SPAN>

with:

 a<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">.</SPAN>writerow<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">(</SPAN>line<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">)</SPAN>

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!

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

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

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.

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

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

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> sys<SPAN class="punctuation token">,</SPAN> os
<SPAN class="keyword token">import</SPAN> numpy
<SPAN class="keyword token">import</SPAN> pandas <SPAN class="keyword token">as</SPAN> pd

ws <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'myworkspace.sde'</SPAN>
fcname <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'MyFeatureClass'</SPAN>
input_fc <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>ws<SPAN class="punctuation token">,</SPAN> fcname<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token">#create the numpy array</SPAN>
narr <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>FeatureClassToNumPyArray<SPAN class="punctuation token">(</SPAN>input_fc<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Field1"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Field2"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Field3"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token">#I prefer to use pandas to export from.&nbsp; personal preference is all</SPAN>
df <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">(</SPAN>narr<SPAN class="punctuation token">)</SPAN>
df<SPAN class="punctuation token">.</SPAN>to_csv<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'H:\somefolder\dfoutput.csv'</SPAN><SPAN class="punctuation token">,</SPAN> header<SPAN class="operator token">=</SPAN>None<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>‍</SPAN></SPAN>

MitchHolley1

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

<SPAN class="keyword token">with</SPAN> open<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'dataTester.csv'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'w'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> fp<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; a<SPAN class="operator token">=</SPAN> csv<SPAN class="punctuation token">.</SPAN>writer<SPAN class="punctuation token">(</SPAN>fp<SPAN class="punctuation token">,</SPAN> delimiter<SPAN class="operator token">=</SPAN><SPAN class="string token">','</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> line <SPAN class="keyword token">in</SPAN> data<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a<SPAN class="punctuation token">.</SPAN>writerows<SPAN class="punctuation token">(</SPAN>line<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.