Import CSV

jaykapalczynski · ‎10-03-2016

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?

jaykapalczynski · ‎10-04-2016

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)

BrittneyWhite1 · ‎10-04-2016

"bikes" is the name of the feature class

jaykapalczynski · ‎10-04-2016

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

BrittneyWhite1 · ‎10-04-2016

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.

jaykapalczynski · ‎10-04-2016

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")

MitchHolley1 · ‎10-04-2016

Shouldn't this:

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

Be this:

cursor = arcpy.InsertCursor (fc...)

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

BrittneyWhite1 · ‎10-04-2016

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.

jaykapalczynski · ‎10-04-2016

How do you start reading the CSV File from the second line...I have a header...

BrittneyWhite1 · ‎10-05-2016

In the example code, I do this with the count variable. I first set the count equal to 0. Then, inside the for loop, I add one to every time we go through the for loop. The insertRow is only executed if the count is greater than 0. So, the first line of the csv (the header), the count is 0, so it does not execute the insertRow. For all of the other records, the count has incremented by 1, so it is true for if count > 0. Make sure your lines of code are properly indented inside the for loop and if statement.

#Set count at zero outside of the loop
count = 0
for line in csv:
    if count > 0: #Only run the insertRow if we've passed over the header row
        #do something, like insert row
#Add one to the count every time (make sure not indented under the if statement)
    count +=1‍‍‍‍‍‍‍

jaykapalczynski · ‎10-05-2016

Actually didnt work again.... Can you look at my indents and see if you see something out of the ordinary?

import arcpy

import datetime

import os, fnmatch

import shutil

def getdate(Hour, Minute):

return datetime.datetime.strptime(str(Hour).zfill(2) + ' ' + str(Minute).zfill(2), '%H %M')

def findFiles (path, filter):

for root, dirs, files in os.walk(path):

for file in fnmatch.filter(files, filter):

yield os.path.join(root, file)

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

#Insert cursor for the Bikes feature class

cursor = arcpy.da.InsertCursor (fc,["CollarSeri", "Year", "Julianday", "Hour", "Minute", "Activity", "Temperatur", "Latitude", "Longitude", "HDOP", "NumSats", "FixTime", "Date", "_2D_3D", "BearID", "CalcDate", "CalcDate3", "FileName", "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 textFile in findFiles(r'C:/Users/tjv36463/Desktop/BearCollar/NewFiles/', '*.txt'):

#print(textFile)

for ln in open (textFile, '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)

CollarSeri = long(lineSplit[0])

Year = str(lineSplit[1])

Julianday = str(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 = str(lineSplit[9])

NumSats = int(lineSplit[10])

FixTime = int(lineSplit[11])

_2D_3D = int(lineSplit[12])

Date = str(lineSplit[13])

getTime = getdate(Hour, Minute)

getTime2 = str(getTime)

getTime3 = getTime2[-8:]

getDateTime = (Date + getTime3)

FileName = str(textFile)

#print (FileName)

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, BearID, getTime3, getDateTime, FileName, shapeVal])

count += 1

del cursor

print ("Processing Finished")