CSV to feature class

[jaykapalczynski]

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.

[BruceHarold]

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.

[JoeBorgione]

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?

[EricShreve]

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[ ]:




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