topic Re: Import CSV in Python Questions

Import CSV

jaykapalczynski — Mon, 03 Oct 2016 12:39:42 GMT

I have a loaded processing question and looking for some ideas...below is what I need to do...

I am importing a CSV file which ultimately will be a feature class. So if we start with the END result (Feature class) and I am looking to import a CSV file the question arises.....Can I append a CSV File to a Feature Class....I say no because there is no geometry in the CSV file other than Lat Long...SO that would bring us full circle (Assuming that the Master Feature Class already exists) to the beginning where I would have create a Staging feature class from the CSV and then append into the Master Feature Class housing all the records.

Proposed Steps:

Delete Staging Feature Class (Used to import the CSV file)
Import CSV file to stand alone table
Create Event Layer via Lat Long Fields in CSV File
Export to Feature Class (This is the Staging layer mentioned above)
Do some field processing to new Feature Class
Append to Master Feature Class

Any other thoughts...did that make sense?

Re: Import CSV

MitchHolley1 — Mon, 03 Oct 2016 15:50:03 GMT

Create and Event Layer, then save the results.

Personally, I've never had luck using the Append tool with python. The field mappings are too confusing for me.

Re: Import CSV

NeilAyres — Mon, 03 Oct 2016 15:59:33 GMT

You can do virtually anything using python / arcpy

So, if you want to go directly from your input csv and append the records into an existing fc, that is no problem.

But your step outlined, are basically what you would do manually using a series of steps (or build a model).

No problem with either approach.

Is your target fc in the same coordinate system as the coordinates in the csv?

Re: Import CSV

BrittneyWhite1 — Mon, 03 Oct 2016 15:59:38 GMT

I would recommend looking into using a Data Access module InsertCursor. You can read through the lines of your CSV and insert them into an existing feature class. Since you have the latitude and longitude in the CSV, you can enter in the geometry using the SHAPE@XY token.

Re: Import CSV

AmyKlug — Mon, 03 Oct 2016 18:04:53 GMT

A trick I use for using append and other gp tools in python is to run it as a tool on the layer with your field mappings set and then in the geoprocessing results right click and "copy as python snippet" and paste it into my python code. I do this with most tools that have complicated inputs.

Re: Import CSV

MitchHolley1 — Mon, 03 Oct 2016 18:45:52 GMT

This may get you started...

Re: Import CSV

jaykapalczynski — Mon, 03 Oct 2016 18:58:17 GMT

How does it know what fields my XY Coordinates are in?

Add fields like this?

#search

....snip (csv, ['SHAPEXY'], [date], [field1], [field2]) as cursor: ...snip

# Insert

....snip (fc, ['SHAPEXY'], [date], [field1], [field2]) as cursor: ...snip

Re: Import CSV

MitchHolley1 — Mon, 03 Oct 2016 19:04:18 GMT

Jay,

Don't forget the '@' in the SHAPE@XY. Please format it like this:

... (csv, ["SHAPE@XY", "Date", "Field_1", "Field_2"]) as cursor

Each field is exasperated by quotations, not brackets.

ArcPy reads the geometries stored in the feature without them being an actual attribute.

Please read Brittany White's response above for a more in depth look into the InserCursor function and the SHAPE@XY token.

Re: Import CSV

jaykapalczynski — Mon, 03 Oct 2016 20:23:25 GMT

I have a CSV file I dont think I can use SHAPE@XY. DO I use the two XY FIelds somehow in the script?

Re: Import CSV

jaykapalczynski — Mon, 03 Oct 2016 20:33:20 GMT

Do I need to import into a table and then create an event layer then append to Feature class?

Re: Import CSV

BrittneyWhite1 — Sun, 12 Dec 2021 05:20:53 GMT

If I understand your end goal correctly, you should be able to use Python to:

1. Read through each row in your csv

2. Insert each row from your csv into your feature class

The way you will write your geometry is using the SHAPE@XY token. Looking at the InsertCursor documentation, the SHAPE@XY expects "a tuple of the feature's centroid x,y coordinates". Your csv has an X field and Y field, so you can use that to create the tuple that will be passed to the cursor.

Here is an example of reading through the lines of a csv that has a header row of "X,Y,ADDRESS,TERMINAL_NUMBER,NUMBER_OF_BIKES":

import arcpy

#Insert cursor for the Bikes feature class
cursor = arcpy.da.InsertCursor ("bikes",["X", "Y","Address","SHAPE@XY"])



#Read through each line of the csv, but skip the first row because it is the header
#The count variable skips of the first row
count =0
for ln in open (r"C:\Path\To\File.csv", 'r').readlines():

    if count > 0:
        #Each line in the csv is a string, so turn it into a list so you can reference each column
        lineSplit = ln.split (",")

        #Use index positions to get the right column from the csv
        #Make sure the data is the correct type (i.e. X and Y need to be numbers, not strings)
        xVal = float(lineSplit[0])
        yVal = float(lineSplit[1])
        addVal = lineSplit[2]
        shapeVal = (xVal, yVal)

        #Insert the values from csv into feature class
        #The order of the fields here, matches the order of the fields in line 4
        cursor.insertRow([xVal, yVal, addVal, shapeVal])
    count += 1

del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Import CSV

AmyKlug — Mon, 03 Oct 2016 21:26:19 GMT

I think this is what you need. I think you will need to copy the feature layer to an actual feature class before you can append.

http://desktop.arcgis.com/en/arcmap/10.3/tools/data-management-toolbox/make-xy-event-layer.htm

Re: Import CSV

DanPatterson_Retired — Sun, 12 Dec 2021 05:20:56 GMT

a csv file has to be brought in via the event them route... then converted/saved to a shapefile or featureclass. Once you have a useable file, you can access the SHAPE@WhateverStuff via anything.

What is wrong with the process you described in your first thread? It seems to work. Are you looking for a faster method?

For the future... consider two tables with two columns... you convert them to numpy arrays, using FeatureClassToNumPyArray ... You 'stack' them (aka... append them) in numpy and you spit it out back to a featureclass etc using NumPyArrayToFeatureClass.

>>> a
array([('B', 1), ('B', 2), ('A', 2), ('C', 3), ('D', 4)], 
      dtype=[('Class', '<U5'), ('Value', '<i4')])
>>> b
array([('A', 10), ('B', 20), ('C', 30), ('A', 5), ('A', 6)], 
      dtype=[('Class', '<U5'), ('Value', '<i4')])
>>> c = np.vstack((a,b))
>>> c
array([[('B', 1), ('B', 2), ('A', 2), ('C', 3), ('D', 4)],
       [('A', 10), ('B', 20), ('C', 30), ('A', 5), ('A', 6)]], 
      dtype=[('Class', '<U5'), ('Value', '<i4')])‍‍‍‍‍‍‍‍‍‍‍

Note, 2 columns (Class and Values) each file has 1 record, the output has 2.

Now I did fail to mention that python and/or numpy can read csv files directly. Meaning you could just read the csv file as numpy array, the featureclass/shapefile as a numpy array, stack/append them as a numpy array and spit out the result back to a featureclass.

The only complication is the field names and mapping stuff... but the forethinking user would have accounted for that already. But if not, then numpy handles field renaming and a whole load of other stuff .... using the easy interface... recfunctions... here is its import and a list of functions that you can use, such as being to rename fields, join fields etc.

>>> from numpy.lib import recfunctions as rfn
>>>
>>> dir(rfn)
['MaskedArray', 'MaskedRecords', '__all__ ... snip ...

'absolute_import', 'append_fields', 'basestring', 'division', 'drop_fields', 
'find_duplicates', 'flatten_descr', 'get_fieldstructure', 'get_names', 
'get_names_flat', 'itertools', 'izip_records', 'join_by', 'ma', 'merge_arrays', 
'ndarray', 'np', 'print_function', 'rec_append_fields', 'rec_drop_fields', 'rec_join', 
'recarray', 'recursive_fill_fields', 'rename_fields', 'stack_arrays', 'sys', 
'zip_descr']
‍‍‍‍‍‍‍‍‍‍
‍‍‍‍‍‍‍‍‍‍‍‍

And of course you can work with nulls in fields, via the maskedarray class. People looking for a gentler interface can check out Pandas. All ot this documented countless times on my blog.

Re: Import CSV

jaykapalczynski — Sun, 12 Dec 2021 05:20:58 GMT

Brittney...thanks for you reply

So in you table/csv file example you have 3 fields correct? Xval, Yval and an address Correct?

"bikes" is you variable to the csv file location correct?

cursor = arcpy.da.InsertCursor ("bikes",["X", "Y","Address","SHAPE@XY"])

        xVal = float(lineSplit[0])
        yVal = float(lineSplit[1])
        addVal = lineSplit[2]
        shapeVal = (xVal, yVal)

Re: Import CSV

jaykapalczynski — Tue, 04 Oct 2016 14:26:39 GMT

I modified your code a bit to this...I am getting an error....

I am a bit confused with the SHAPE@XY I am using that when I index the variables and when I add it to the Feature Class....but there is no field for shapeXY....

Any thoughts? THANKS again for you help.

>>> ================================ RESTART ================================
>>>
<da.InsertCursor object at 0x0000000007000C30>

Traceback (most recent call last):
File "C:/Users/tjv36463/Desktop/Bear Collar/ImportBearData.py", line 28, in <module>
CollarSerialNumber = int(lineSplit[0])
NameError: name 'lineSplit' is not defined

import arcpy

fc = r'C:\Users\tjv36463\Desktop\Bear Collar\BearLocationImports.gdb\BearCollar'

#Insert cursor for the Bikes feature class

#cursor = arcpy.da.InsertCursor ("bikes",["X", "Y","Address","SHAPE@XY"])

cursor = arcpy.da.InsertCursor (fc,["CollarSerialNumber", "Year", "Julianday", "Hour", "Minute", "Activity", "Temperature", "Latitude", "Longitude", "HDOP", "NumSats", "FixTime", "2D/3D", "Date", "SHAPE@XY"])

#Read through each line of the csv, but skip the first row because it is the header

#The count variable skips of the first row

count =0

for ln in open (r"C:\Users\tjv36463\Desktop\Bear Collar\rangedate_D032491_20160822124644.csv", 'r').readlines():

if count > 0:

#Each line in the csv is a string, so turn it into a list so you can reference each column

lineSplit = ln.split(",")

#Use index positions to get the right column from the csv

#Make sure the data is the correct type (i.e. X and Y need to be numbers, not strings)

# xVal = float(lineSplit[0] # yVal = float(lineSplit[1] # addVal = lineSplit[2] # shapeVal = (xVal, yVal)

CollarSerialNumber = int(lineSplit[0])

Year = int(lineSplit[1])

Julianday = int(lineSplit[2])

Hour = int(lineSplit[3])

Minute = int(lineSplit[4])

Activity = int(lineSplit[5])

Temperature = int(lineSplit[6])

Latitude = float(lineSplit[7])

Longitude = float(lineSplit[8])

HDOP = int(lineSplit[9])

NumSats = int(lineSplit[10])

FixTime = int(lineSplit[11])

_2D_3D = int(lineSplit[12])

Date = str(lineSplit[13])

shapeVal = (Longitude,Latitude)

#Insert the values from csv into feature class

#The order of the fields here, matches the order of the fields in line 4

#cursor.insertRow([xVal, yVal, addVal, shapeVal])

cursor.insertRow ([CollarSerialNumber, Year, Julianday, Hour, Minute, Activity, Temperature, Latitude, Longitude, HDOP, NumSats, FixTime, _2D_3D, Date, shapeVal])

count += 1

del cursor

Re: Import CSV

BrittneyWhite1 — Tue, 04 Oct 2016 16:19:20 GMT

"bikes" is the name of the feature class

Re: Import CSV

BrittneyWhite1 — Tue, 04 Oct 2016 16:25:35 GMT

I would recommend adding a line of code after you create your lineSplit variable to print it out (i.e. print lineSplit) to check to see what you are getting back. What you should be getting back is a list of the values from your csv for each row. The indexing (i.e. lineSplit[0]) will allow you to get values from particular columns in your csv. Before running the insertRow, make sure you are getting the values you expect.

Re: Import CSV

jaykapalczynski — Tue, 04 Oct 2016 16:26:44 GMT

OK I think I am making great progress with you help Brittney....THANKS A TON....This is what I have....

IF I go into the csv file and remove the header field names I get it to import fine.

If I keep the header there I am getting errors because of field types etc...I cant seem to get it to start on the SECOND line of the CSV file....

import arcpy

fc = r'C:\Users\tjv36463\Desktop\Bear Collar\BearLocationImports.gdb\BearCollar'

#Insert cursor for the Bikes feature class

#cursor = arcpy.da.InsertCursor ("bikes",["X", "Y","Address","SHAPE@XY"])

cursor = arcpy.da.InsertCursor (fc,["CollarSeri", "Year", "Julianday", "Hour", "Minute", "Activity", "Temperatur", "Latitude", "Longitude", "HDOP", "NumSats", "FixTime", "Date", "_2D_3D", "SHAPE@XY"])

#Read through each line of the csv, but skip the first row because it is the header

#The count variable skips of the first row

count = 0

for ln in open (r"C:\Users\tjv36463\Desktop\Bear Collar\rangedate_D032491_20160822124644.csv", 'r').readlines():

#if count > 0:

lineSplit = ln.split(",")

print lineSplit

#Each line in the csv is a string, so turn it into a list so you can reference each column

#lineSplit = ln.split(",")

#print lineSplit

#Use index positions to get the right column from the csv

#Make sure the data is the correct type (i.e. X and Y need to be numbers, not strings)

CollarSeri = long(lineSplit[0])

Year = str(lineSplit[1])

Julianday = str(lineSplit[2])

Hour = str(lineSplit[3])

Minute = str(lineSplit[4])

Activity = str(lineSplit[5])

Temperature = str(lineSplit[6])

Latitude = float(lineSplit[7])

Longitude = float(lineSplit[8])

HDOP = str(lineSplit[9])

NumSats = str(lineSplit[10])

FixTime = str(lineSplit[11])

_2D_3D = str(lineSplit[12])

Date = str(lineSplit[13]) # working with Date Time http://stackoverflow.com/questions/1521906/how-to-specify-date-and-time-in-python

shapeVal = (Longitude, Latitude)

#Insert the values from csv into feature class

#The order of the fields here, matches the order of the fields in line 4

cursor.insertRow ([CollarSeri, Year, Julianday, Hour, Minute, Activity, Temperature, Latitude, Longitude, HDOP, NumSats, FixTime, Date, _2D_3D, shapeVal])

count += 1

del cursor

print ("done")

Re: Import CSV

MitchHolley1 — Tue, 04 Oct 2016 17:30:43 GMT

Shouldn't this:

cursor = arcpy.da.InsertCursor (fc...)

Be this:

cursor = arcpy.InsertCursor (fc...)

Drop the 'da'? brittney.white.ucf‌

Re: Import CSV

BrittneyWhite1 — Tue, 04 Oct 2016 17:41:52 GMT

There are two types of cursors, the older InsertCursor and the data access module InsertCursor (da.InsertCursor). The syntax here specifying the fields matches up with the da.InsertCursor.