jim.krist

Datetime from CSV file

Discussion created by jim.krist on Nov 6, 2012
Latest reply on Nov 13, 2012 by dkwiens
Good day. I am trying to parse a large amount of data (in CSV format) into minute intervals and then geocode the data into a geodatabase. Problem is that the datetime field does not display as text format in the geodatabase. It still shows a DATE format. The CSV file has the proper YYYY-MM-DD HH:MM text format after parsing. I have converted the date field to text format prior to geoprocessing but the data still appears to be processed in ArcGIS in date format.

I know shapefiles do not support the HH:MM timestamp date format so I have performed a text conversion on the date:

dt = DTTM[:19]
dtime = datetime.strptime(dt, '%Y-%m-%d %H:%M:%S')
str_date = dtime.strftime('%Y-%m-%d %H:%M')

And the datetime field is set as text format in the schema.ini file prior to geoprocessing.

[1output.csv]
Format=CSVDelimited
Col4=DTTM Text

I have also tried to write directly from the CSV to a file geodatabase, using CopyRows, i.e. arcpy.CopyRows_management(filename, geodb), but the geodatabase still contains the date format.

Any ideas what is going on?

Thanks in advance for all responses.

My code snippets are included below:

min_interval.py:

import csv
from datetime import datetime
import os

def min_aggr(infile, outfile):

ifile = open(infile, 'rb')
reader = csv.reader(ifile)
out = open(outfile, 'wb')
writer = csv.writer(out)
header = reader.next()
hdr = ['ID','LON','LAT','DTTM','HEADING','CALL_SIGN','vesselType']
writer.writerow(hdr)

for ID,LON,LAT,DTTM,HEADING,CALL_SIGN,vesselType in reader:
dt = DTTM[:19]
dtime = datetime.strptime(dt, '%Y-%m-%d %H:%M:%S')
str_date = dtime.strftime('%Y-%m-%d %H:%M')
data = [ID,LON,LAT,DTTM,HEADING,CALL_SIGN,vesselType]
writer.writerow(data)

filenum = 0
path = raw_input("Enter the full path name (ex. c:\\test\):")
datapath = raw_input('Enter the path for the output directory (ex. c:\\output\):')
dirList=os.listdir(path)
dirList.sort()
for fname in dirList:
filenum += 1
filename = path + fname
outfile = datapath + str(filenum) + 'output.csv'
try:
min_aggr(filename, outfile)
except IOError:
print filename

print 'files have been processed'

geocode.py:

import os
import arcpy
from arcpy import env
env.overwriteOutput = True

coord = "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]]" #spatial projection

#create file geodatabase
dir = raw_input('Enter path to create geodatabase (ex. c:\\data\):')
dbname = raw_input('Enter the name for the geodatabase (ex. test.gdb):')
arcpy.CreateFileGDB_management(dir, dbname)
print 'geodatabase ' + dbname + ' has been created'
geodb = dir + dbname

filenum = 0
path = raw_input('Enter the full path name for input files (ex. c:\\test1\):')
datapath = raw_input('Enter the path for the output directory (ex. c:\\output1\):')
dirList=os.listdir(path)
dirList.sort()
for fname in dirList:
filenum += 1
filename = path + fname
shp = datapath + str(filenum) + 'output.shp'
try:
arcpy.MakeTableView_management(filename,'View')
arcpy.MakeXYEventLayer_management('View','LON','LAT','Layer')
arcpy.CopyFeatures_management('Layer', shp)
arcpy.DefineProjection_management(shp, coord)
arcpy.FeatureClassToGeodatabase_conversion([shp], geodb)
print 'file ' + filename + ' has been geoprocessed'
except IOError:
print 'ERROR: ' + filename

print 'geoprocessing complete'

Outcomes