topic Re: CSV to feature class in Python Questions

CSV to feature class

jaykapalczynski — Thu, 06 Feb 2020 21:14:14 GMT

I have a csv file in a folder location that gets updated every hour.

I need to take this info and update a Feature Class that is being published to a feature service.

Looking for the smartest and most effective means in which to do this.

Stored Procedure and then to FC? Any ideas would be appreciated.

Re: CSV to feature class

BruceHarold — Thu, 06 Feb 2020 21:30:53 GMT

An ETL approach can operate directly on both the CSV file and the feature service:

https://community.esri.com/community/open-platform-standards-and-interoperability/blog/2019/05/31/automate-your-etl-processes-on-a-schedule

You could script it entirely too a few ways. Is the geometry in the data or are you geocoding?

Re: CSV to feature class

jaykapalczynski — Fri, 10 Dec 2021 22:07:24 GMT

I can run a stored procedure and read the data....I think I need to then read the Stored Procedure via python to create the Feature Class? Using an insert cursor?

ALTER PROCEDURE [dbo].[addData] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	CREATE TABLE #ImportData
	(
		CFS varchar(MAX), 
		Code varchar(MAX),
	)

    -- Insert statements for procedure here
BULK INSERT #ImportData from 'C:\Scripts\Service\DailyCalls.csv' WITH
(
	FIELDTERMINATOR = ',',
	FIRSTROW = 2
)
SELECT * FROM #ImportData
DROP TABLE #ImportData
END‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: CSV to feature class

jaykapalczynski — Thu, 06 Feb 2020 21:33:26 GMT

I have an XY field in the csv file

Re: CSV to feature class

BruceHarold — Thu, 06 Feb 2020 21:37:28 GMT

This topic describes using the arcgis API:

Overwriting feature layers | ArcGIS for Developers

Re: CSV to feature class

JoeBorgione — Thu, 06 Feb 2020 21:48:06 GMT

What I've done in a similar situation is to turn the csv into a xy event, ( Make XY Event Layer—Data Management toolbox | Documentation ) and then create a feature class from that. All in arcpy an all in memory. Then truncate the target feature class and append the in_memory feature class to it.

Re: CSV to feature class

BruceHarold — Thu, 06 Feb 2020 21:55:07 GMT

Nice one Joe, however it may be a requirement with this update frequency to have no downtime, in which case I would lean to the ETL approach which operates one feature at a time or to the feature service REST API so you only operate on the feature service change set in milliseconds. Sorry to be pedantic ;-).

Re: CSV to feature class

jaykapalczynski — Thu, 06 Feb 2020 21:57:13 GMT

Alright I simply created a model

Event Layer then to Geodatabase. Will set this python script to run on a scheduled task

Re: CSV to feature class

jaykapalczynski — Thu, 06 Feb 2020 21:58:03 GMT

Good point Bruce....I will have to continue your approach...but still confused....

Re: CSV to feature class

jaykapalczynski — Thu, 06 Feb 2020 21:59:05 GMT

I just did what Joe was talking about.....so now to continue with your solution

Re: CSV to feature class

BruceHarold — Thu, 06 Feb 2020 22:05:20 GMT

The ETL approach requires Data Interoperability extension and the ability to author the processing, but we have big sites taking this no-code approach to maintaining hosted feature services from arbitrary source data on a regular schedule, so it works.

Re: CSV to feature class

JoeBorgione — Thu, 06 Feb 2020 22:09:18 GMT

Not knowing how the points are published, I would think if it is to a local on-prem portal that then is an AGOL item , updates including truncates and append should be invisible to the end users, no?

Re: CSV to feature class

EricShreve — Fri, 10 Dec 2021 22:07:26 GMT

This is a script that created in Jupyter Notebook and exported as a Python Script that runs on a server once a day. It parses a website that builds a nice data frame/CSV that then appends to a point feature service. Line 134 should work for your workflow. Hope that helps.

#!/usr/bin/env python
# coding: utf-8

# In[42]:


import bs4 as bs
import urllib.request
import pandas as pd
import numpy as np
from arcgis.gis import GIS
from arcgis import features
from copy import deepcopy
import arcpy


# In[43]:


source = urllib.request.urlopen('https://smoke.azdeq.gov/').read()
soup = bs.BeautifulSoup(source,'lxml')


# In[44]:


table = soup.table


# In[45]:


table = soup.find('table')


# In[46]:


table_rows = table.find_all('tr')


# In[47]:


for tr in table_rows:
    td = tr.find_all('td')
    row = [i.text for i in td]
    print(row)


# In[48]:


dfs = pd.read_html('https://smoke.azdeq.gov/',header=0)
for df in dfs:
    print(df)


# In[49]:


df.head()


# In[50]:


df['Location'] = df['Location']                          .astype(str).str.replace(r"[\(\)']", '')


# In[51]:


lat = []
lon = []


# In[52]:


# For each row in a varible,
for row in df['Location']:
    # Try to,
    try:
        # Split the row by comma and append
        # everything before the comma to lat
        lat.append(row.split(',')[0])
        # Split the row by comma and append
        # everything after the comma to lon
        lon.append(row.split(',')[1])
    # But if you get an error
    except:
        # append a missing value to lat
        lat.append(np.NaN)
        # append a missing value to lon
        lon.append(np.NaN)


# In[53]:


# Create two new columns from lat and lon
df['latitude'] = lat
df['longitude'] = lon


# In[54]:


df['latitude'] = df['latitude'].apply(lambda x: ' '.join(x.split(' ')[1:]))


# In[55]:


del df['Location']


# In[56]:


df.head()


# In[57]:


df.to_csv(r'C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\CSV\ADEQ_RX_Burns1.csv', encoding='utf-8', index=False)


# In[63]:


gis = GIS("https://www.arcgis.com", "Your Username", "Your Password")
print("Logged in as " + str(gis.properties.user.username))


# In[64]:


arcpy.management.XYTableToPoint(r"C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\CSV\ADEQ_RX_Burns1.csv", r"C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1", "longitude", "latitude", None, "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119521E-09;0.001;0.001;IsHighPrecision")


# In[65]:


arcpy.management.CalculateField(r"C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1", "Ignition_Date", "datetime.datetime.now()", "PYTHON3", '', "TEXT")


# In[66]:


arcpy.management.Append(r"'C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1'", "https://services6.arcgis.com/l7uujk4hHifqabRB/arcgis/rest/services/ADEQ_RX_Burns1/FeatureServer/0", "NO_TEST", r'Smoke_Unit_Number "Smoke Unit Number" true true false 2147483647 Text 0 0,First,#,C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1,Smoke_Unit_Number,0,5000;Burn_Number "Burn Number" true true false 2147483647 Text 0 0,First,#,C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1,Burn_Number,0,5000;Burn_Name "Burn Name" true true false 2147483647 Text 0 0,First,#,C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1,Burn_Name,0,5000;Ignition_Date "Ignition Date" true true false 8 Date 0 0,First,#,C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1,Ignition_Date,-1,-1;Approved_Acres "Approved Acres" true true false 0 Long 0 0,First,#,C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1,Approved_Acres,-1,-1;Notes "Notes" true true false 2147483647 Text 0 0,First,#,C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1,Notes,0,5000;latitude "latitude" true true false 0 Double 0 0,First,#,C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1,latitude,-1,-1;longitude "longitude" true true false 0 Double 0 0,First,#,C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1,longitude,-1,-1', '', '')


# In[67]:


arcpy.management.Delete(r"'C:\Code\Python\Web_Scraping\ADEQ_RX_Burns\New File Geodatabase.gdb\ADEQ_RX_Burns1'", '')


# In[ ]:




‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍