I would like to write two headers, but am receving my actual two headers and 3 blank fields. What could be the cause of this? Here is my script and output. The script parses a JSON web-service and sends lat/lng of buses to a table in a file geodatabase. import requests import json import urllib2 import csv import arcpy if arcpy . Exists ( "C:\MYLATesting.gdb\API_Table" ): arcpy . Delete_management ( "C:\MYLATesting.gdb\API_Table" ) url = " http://api.metro.net/agencies/lametro/vehicles/ " parsed_json = json . load ( urllib2 . urlopen ( " http://api.metro.net/agencies/lametro/vehicles/ " )) details = { 'items' : 'longitude' } headers = { 'Content-type' : 'application/x-www-form-urlencoded' , 'Accept' : '/' } response = requests . get ( url , data = json . dumps ( details ), headers = headers ) # tell computer where to put CSV outfile_path = 'C:\Users\Administrator\Desktop\API_Testing.csv' # open it up, the w means we will write to it writer = csv . writer ( open ( outfile_path , 'wb' )) #create a list with headings for our columns headers = [ 'latitude' , 'longitude' ] for items in parsed_json [ 'items' ]: row = [] row . append ( str ( items [ 'latitude' ]). encode ( 'utf-8' )) row . append ( str ( items [ 'longitude' ]). encode ( 'utf-8' )) writer . writerow ( row ) i = 1 i = i + 1 f = open ( outfile_path , 'wb' ) writer = csv . writer ( f ) #write the row of headings to our CSV file writer . writerow ( headers ) f . close () #Temporary Delete Function to Overwrite Table arcpy . TableToTable_conversion ( outfile_path , "C:\MYLATesting.gdb" , "API_Table" ) print response . text

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; } } }

Arcpy TableToTable_Conversion writing extra fields?

enterprisemaps-gwest

I would like to write two headers, but am receving my actual two headers and 3 blank fields. What could be the cause of this? Here is my script and output. The script parses a JSON web-service and sends lat/lng of buses to a table in a file geodatabase.

<SPAN class="kwd" style="color: #00008b;">import</SPAN><SPAN class="pln" style="color: #000000;"> requests
</SPAN><SPAN class="kwd" style="color: #00008b;">import</SPAN><SPAN class="pln" style="color: #000000;"> json
</SPAN><SPAN class="kwd" style="color: #00008b;">import</SPAN><SPAN class="pln" style="color: #000000;"> urllib2
</SPAN><SPAN class="kwd" style="color: #00008b;">import</SPAN><SPAN class="pln" style="color: #000000;"> csv
</SPAN><SPAN class="kwd" style="color: #00008b;">import</SPAN><SPAN class="pln" style="color: #000000;"> arcpy





</SPAN><SPAN class="kwd" style="color: #00008b;">if</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">Exists</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="str" style="color: #800000;">"C:\MYLATesting.gdb\API_Table"</SPAN><SPAN class="pun" style="color: #000000;">):</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">Delete_management</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="str" style="color: #800000;">"C:\MYLATesting.gdb\API_Table"</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">

url </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;"><SPAN>"</SPAN><A class="jive-link-external-small" href="http://api.metro.net/agencies/lametro/vehicles/" rel="nofollow noopener noreferrer" target="_blank">http://api.metro.net/agencies/lametro/vehicles/</A><SPAN>"</SPAN></SPAN><SPAN class="pln" style="color: #000000;">
parsed_json </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> json</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">load</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">urllib2</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">urlopen</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="str" style="color: #800000;"><SPAN>"</SPAN><A class="jive-link-external-small" href="http://api.metro.net/agencies/lametro/vehicles/" rel="nofollow noopener noreferrer" target="_blank">http://api.metro.net/agencies/lametro/vehicles/</A><SPAN>"</SPAN></SPAN><SPAN class="pun" style="color: #000000;">))</SPAN><SPAN class="pln" style="color: #000000;">
details </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">{</SPAN><SPAN class="str" style="color: #800000;">'items'</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">'longitude'</SPAN><SPAN class="pun" style="color: #000000;">}</SPAN><SPAN class="pln" style="color: #000000;">
headers </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">{</SPAN><SPAN class="str" style="color: #800000;">'Content-type'</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">'application/x-www-form-urlencoded'</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">'Accept'</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">'/'</SPAN><SPAN class="pun" style="color: #000000;">}</SPAN><SPAN class="pln" style="color: #000000;">
response </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> requests</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">get</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">url</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> data</SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;">json</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">dumps</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">details</SPAN><SPAN class="pun" style="color: #000000;">),</SPAN><SPAN class="pln" style="color: #000000;"> headers</SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;">headers</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">


</SPAN><SPAN class="com" style="color: #808080;"># tell computer where to put CSV</SPAN><SPAN class="pln" style="color: #000000;">
outfile_path</SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="str" style="color: #800000;">'C:\Users\Administrator\Desktop\API_Testing.csv'</SPAN><SPAN class="pln" style="color: #000000;">

</SPAN><SPAN class="com" style="color: #808080;"># open it up, the w means we will write to it</SPAN><SPAN class="pln" style="color: #000000;">
writer </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> csv</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">writer</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">open</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">outfile_path</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">'wb'</SPAN><SPAN class="pun" style="color: #000000;">))</SPAN><SPAN class="pln" style="color: #000000;">

</SPAN><SPAN class="com" style="color: #808080;">#create a list with headings for our columns</SPAN><SPAN class="pln" style="color: #000000;">
headers </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">[</SPAN><SPAN class="str" style="color: #800000;">'latitude'</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="str" style="color: #800000;">'longitude'</SPAN><SPAN class="pun" style="color: #000000;">]</SPAN><SPAN class="pln" style="color: #000000;">

</SPAN><SPAN class="kwd" style="color: #00008b;">for</SPAN><SPAN class="pln" style="color: #000000;"> items </SPAN><SPAN class="kwd" style="color: #00008b;">in</SPAN><SPAN class="pln" style="color: #000000;"> parsed_json</SPAN><SPAN class="pun" style="color: #000000;">[</SPAN><SPAN class="str" style="color: #800000;">'items'</SPAN><SPAN class="pun" style="color: #000000;">]:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; row </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">[]</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; row</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">append</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">str</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">items</SPAN><SPAN class="pun" style="color: #000000;">[</SPAN><SPAN class="str" style="color: #800000;">'latitude'</SPAN><SPAN class="pun" style="color: #000000;">]).</SPAN><SPAN class="pln" style="color: #000000;">encode</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="str" style="color: #800000;">'utf-8'</SPAN><SPAN class="pun" style="color: #000000;">))</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; row</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">append</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">str</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">items</SPAN><SPAN class="pun" style="color: #000000;">[</SPAN><SPAN class="str" style="color: #800000;">'longitude'</SPAN><SPAN class="pun" style="color: #000000;">]).</SPAN><SPAN class="pln" style="color: #000000;">encode</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="str" style="color: #800000;">'utf-8'</SPAN><SPAN class="pun" style="color: #000000;">))</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; writer</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">writerow</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">row</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">

i </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="lit" style="color: #800000;">1</SPAN><SPAN class="pln" style="color: #000000;">
i </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> i </SPAN><SPAN class="pun" style="color: #000000;">+</SPAN><SPAN class="lit" style="color: #800000;">1</SPAN><SPAN class="pln" style="color: #000000;">

f </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> open</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">outfile_path</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">'wb'</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
writer </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> csv</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">writer</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">f</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
</SPAN><SPAN class="com" style="color: #808080;">#write the row of headings to our CSV file</SPAN><SPAN class="pln" style="color: #000000;">
writer</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">writerow</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">headers</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">

f</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">close</SPAN><SPAN class="pun" style="color: #000000;">()</SPAN><SPAN class="pln" style="color: #000000;">

</SPAN><SPAN class="com" style="color: #808080;">#Temporary Delete Function to Overwrite Table</SPAN><SPAN class="pln" style="color: #000000;">

arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">TableToTable_conversion</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">outfile_path</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"C:\MYLATesting.gdb"</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"API_Table"</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">

</SPAN><SPAN class="kwd" style="color: #00008b;">print</SPAN><SPAN class="pln" style="color: #000000;"> response</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">text</SPAN>

Vind meer berichten getagd met

Accepted answers

JoshuaBixby

The above concept can be expanded to download all the information and create a point feature class.

import arcpy  
import json  
import numpy  
import urllib2  

fc = #feature class location  
if arcpy.Exists(fc):    
  arcpy.Delete_management(fc)  

url = "http://api.metro.net/agencies/lametro/vehicles/"    
parsed_json = json.load(urllib2.urlopen(url))  
sr = arcpy.SpatialReference(4326)  #assuming wgs84 coords but didn't verify

ndtype = numpy.dtype([
    ('id', 'S12'),
    ('route_id', 'S12'),
    ('run_id', 'S12'),
    ('latitude', 'f8'),
    ('longitude', 'f8'),
    ('heading', 'f8'),
    ('seconds_since_report', 'i4'),
    ('predictable', '?')
])

vehicles = []  
for item in parsed_json['items']:  
    vehicles.append(tuple(item for k in ndtype.names))

narr = numpy.array(vehicles,  ndtype)  
arcpy.da.NumPyArrayToFeatureClass(narr, fc, ['longitude', 'latitude'], sr)

All comments

JoshuaBixby

In this case, there is no url to close. The url variable in the script is just a string. The script never directly interacts with the file-like object returned by urllib2.urlopen. Instead, the json.load method calls urllib2.open and iterates through the returned file-like object. Once json.load returns, everything it created goes out of scope, including objects returned by urllib2.urlopen.

blake.terhune

Is it necessary to close the url when getting JSON?

python - closing files properly opened with urllib2.urlopen() - Stack Overflow

JoshuaBixby

No. The strings can be whatever length you specify, I simply chose 12 because looking over the data, it appeared 12 characters was more than enough to hold all the potential values. If you need a 48 character string, either 'S48' or '|S48' would work. If you are working with Unicode, then use a U in place of an S.

enterprisemaps-gwest

Hi Joshua, This implementation works wonders, however like you stated the NumPy documentation isn't all of that helpful. My question is will string data types ALWAYS be 12 bytes? This script is for a proof of concept, and another JSON web service will be used which I why I was using the requests module in the initial script, because I will have to execute a POST instead of GET request to the service. I appreciate your help.

enterprisemaps-gwest

Agreed, thank you Joshua.

XanderBakker

Hi Joshua Bixby‌, nice short piece of code to get the job done!

JoshuaBixby

The above concept can be expanded to download all the information and create a point feature class.

import arcpy  
import json  
import numpy  
import urllib2  

fc = #feature class location  
if arcpy.Exists(fc):    
  arcpy.Delete_management(fc)  

url = "http://api.metro.net/agencies/lametro/vehicles/"    
parsed_json = json.load(urllib2.urlopen(url))  
sr = arcpy.SpatialReference(4326)  #assuming wgs84 coords but didn't verify

ndtype = numpy.dtype([
    ('id', 'S12'),
    ('route_id', 'S12'),
    ('run_id', 'S12'),
    ('latitude', 'f8'),
    ('longitude', 'f8'),
    ('heading', 'f8'),
    ('seconds_since_report', 'i4'),
    ('predictable', '?')
])

vehicles = []  
for item in parsed_json['items']:  
    vehicles.append(tuple(item for k in ndtype.names))

narr = numpy.array(vehicles,  ndtype)  
arcpy.da.NumPyArrayToFeatureClass(narr, fc, ['longitude', 'latitude'], sr)

JoshuaBixby

Instead of troubleshooting your current approach, I am going to suggest a different one for a couple of reasons. First, there seems to be lines in your code that aren't doing anything, or at least not contributing to building your table. Fortunately your screenshots can be used to figure out the intent of the code. Second, writing the results to an intermediate/temporary CSV file on disk adds extra steps and slows performance.

The NumPy conversion functions in the ArcPy Data Access module (arcpy.da) are very robust, high performing, and I would argue designed for situations just like this one. Although I still find NumPy documentation lacking in clarity in many areas, it is well worth learning and using with and without ArcPy.

import arcpy
import json
import numpy
import urllib2

tbl = #table location
if arcpy.Exists(tbl):  
  arcpy.Delete_management(tbl)

url = "http://api.metro.net/agencies/lametro/vehicles/"  
parsed_json = json.load(urllib2.urlopen(url))

LatLongs = []
for item in parsed_json['items']:
    LatLongs.append((
        str(item['latitude']).encode('utf-8'),
        str(item['longitude']).encode('utf-8')
    ))
narr = numpy.array(LatLongs,
                   numpy.dtype([('latitude', '|S12'), ('longitude', '|S12')]))
arcpy.da.NumPyArrayToTable(narr, tbl)

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

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