ArcGIS10.0-PythonWindow:Bring the text data into file geodatabase-wrong date!!!

[ScottChang]

Hi all,
I executed the following script on the attached txt file:

>>> # open the text file, read it into memory
... file = open('c:/testing/lat-lon-date-conversions.txt').read().splitlines()
... # define a new, empty list object
... l = []
... # iterate through the lines of the file, slice up the line by index
... #   position of each data column
... for line in file:
...     v1 = line[0:8].strip()  # lat dms
...     v2 = v1[0:2]  # lat deg
...     v3 = v1[2:4]  # lat min
...     v4 = v1[4:6]+'.'+v1[6:8]  # lat sec
...     v5 = str(int(v2)+(float(v3)/60)+(float(v4)/3600))  # lat dd
...     v6 = line[9:23].strip()  # lon dms
...     v7 = v6[0:3]  # lon deg
...     v8 = v6[3:5]  # lon min
...     v9 = v6[5:7]+'.'+v6[7:9]  # lon sec
...     v10 = str('-'+str(int(v7)+(float(v8)/60)+float(v9)/3600))  # lon dd
...     v11 = line[19:23]  # date as MMYY
...     v12 = str(v11[0:2])+'/'+str(v11[2:4])  # concatenate date as MM/YY
...     # add our line to list l
...     # do string formatting to lat/long to display only 5 decimal points
...     l.extend([[('%.5f' % float(v5)),('%.5f' % float(v10)),v12]])
...     # print out each line to stdout
... for each in l:
...     print each
... 
['36.44000', '-94.03667', '06/94']

['36.44000', '-94.04833', '06/94']

['33.09167', '-93.90333', '09/05']

['33.02667', '-93.89167', '07/05']

['34.34111', '-93.50056', '05/06']

['34.24833', '-93.67667', '01/07']

['34.22500', '-93.89500', '07/06']

['33.76833', '-92.48500', '07/06']

['33.74500', '-92.47667', '07/06']

['33.68000', '-92.46667', '07/06']

>>> import csv
... >>> # open and read the csv file into memory
... >>> file = open('C:/testing/simple-csv.csv')
... >>> reader = csv.reader(file)
... >>> # iterate through the lines and print them to stdout
... >>> # the csv module returns us a list of lists and we
... >>> #  simply iterate through it
... >>> for line in reader:
... ...     print line
... ...
... 
Parsing error <type 'exceptions.SyntaxError'>: invalid syntax (line 2)
>>> import csv
>>> file = open('C:/testing/simple-csv.csv')
>>> reader = csv.reader(file)
>>> for line in reader:
... ...     print line
... ...
... 
Parsing error <type 'exceptions.IndentationError'>: expected an indented block (line 2)
>>> for line in reader:
...     print line
...  
... 
['34.79038,-96.80871."4/13/1983"']

['34.93032,-96.44490,"2/5/1967"']

['34.95507,-96.92268,"12/23/2001"']

['34.95689,-96.92263,"8/9/1999"']

['34.92559,-96.68021,"8/25/1954"']

>>> import csv
>>> file = open('C:/testing/simple-csv.csv')
>>> reader = csv.reader(file)
>>> for line in reader:
...     print line
... 
['34.79038,-96.80871,"4/13/1983"']

['34.93032,-96.44490,"2/5/1967"']

['34.95507,-96.92268,"12/23/2001"']

['34.95689,-96.92263,"8/9/1999"']

['34.92559,-96.68021,"8/25/1954"']

>>> import arcgisscripting, csv, os
... gp = arcgisscripting.create()
... # Here we have three custom functions we will call later in our code
... def killObject( object ):
...     """ Kills an input object """
...     if gp.Exists(object):
...         gp.Delete_management(object)
... def makeFgdb( dir, db, fd, spatRef ):
...     """ Create a file geodatabase and featuredataset """
...     if os.path.isdir(dir) != 1:
...         os.mkdir(dir)
...     killObject( os.path.join(dir, db) )
...     gp.CreateFileGDB_management(dir, db)
...     gp.CreateFeatureDataset_management(os.path.join(dir, db), fd, spatRef)
...     
... def createGdbTable( db, table, fields ):
...     """ Creates an empty standalone GDB table and adds fields provided in a list 
...         - with a set schema """
...     killObject(db + '/' + table)
...     gp.CreateTable(db, table)
...     for field in fields:
...         if field[1] == 'TEXT':
...             gp.AddField_management(db + '/' + table,field[0],field[1],'#','#',field[2],field[3],'NULLABLE','NON_REQUIRED','#')
...         else:
...             gp.AddField_management(db + '/' + table,field[0],field[1],'#','#','#',field[3],'NULLABLE','NON_REQUIRED','#')
... # our spatial reference, this can be copied from a prj file
... sr = 'GEOGCS["GCS_North_American_1927",DATUM["D_North_American_1927",SPHEROID["Clarke_1866",6378206.4,294.9786982]],\
...       PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]'
... # our list of fields and their properties:
... #   [field_name,field_type,field_length (zero for non-text fields),field_alias]
... fields = [['LAT','DOUBLE','0','Latitude'],
...           ['LON','DOUBLE','0','Longitude'],
...           ['SOMEDATE','TEXT','6','Some Date']]
...           
... # create our new file geodatabase to work with
... makeFgdb( 'C:/Testing', 'NBI.gdb', 'Bridges', sr )
... # create our standalone geodatabase table and its schema
... createGdbTable( 'C:/Testing/NBI.gdb', 'Test', fields )
... # start slurping our text file into gdb table
... # create a cursor on the table
... rows = gp.InsertCursor('C:/Testing/NBI.gdb/Test')
... # open the text file, read it into memory
... file = open('c:/Testing/lat-lon-date-conversions.txt').read().splitlines()
... # define a new, empty list object
... l = []
... # iterate through the lines of the file, slice up the line by index
... #   position of each data column
... for line in file:
...     v1 = line[0:8].strip()  # lat dms
...     v2 = v1[0:2]  # lat deg
...     v3 = v1[2:4]  # lat min
...     v4 = v1[4:6]+'.'+v1[6:8]  # lat sec
...     v5 = str(int(v2)+(float(v3)/60)+(float(v4)/3600))  # lat dd
...     v6 = line[9:18].strip()  # lon dms
...     v7 = v6[0:3]  # lon deg
...     v8 = v6[3:5]  # lon min
...     v9 = v6[5:7]+'.'+v6[7:9]  # lon sec
...     v10 = str('-'+str(int(v7)+(float(v8)/60)+float(v9)/3600))  # lon dd
...     v11 = line[18:23]  # date as MMYY
...     v12 = str(v11[0:2])+'/'+str(v11[2:4])  # concatenate date as MM/YY
...     # add our line to list l
...     # do string formatting to lat/long to only display 5 decimal points
...     l.extend([[('%.5f' % float(v5)),('%.5f' % float(v10)),v12]])
... ln = 0
... # iterate through our data
... for line in l:
...     t = 0
...     # create a new row
...     row = rows.NewRow()
...     # iterate through the fields
...     for field in fields:
...         val = line.strip()
...         # set the value for each field
...         if field[1] == 'DOUBLE':
...             row.SetValue(field[0], '%.5f' % float(val))
...         else:
...             row.SetValue(field[0], val)
...         t = t + 1
...     # insert the row into the table
...     rows.InsertRow(row)
...     ln = ln + 1
...     del row
...     del t
...     del line
... del rows
... 
>>> 

It worked. But the dates in the column "Some Date" are weird - see the attached mxd file.

Please kindly help and advise me how I can fix it.

Thanks,
Scott Chang

[Anonymous User]

Original User: msayler-w

I got bad output running the existing code on that text file. I'm guessing something is going wrong with the slicing as the date is off by a digit to the left:

['36.44000', '-94.03667', ',0/69']
['36.44000', '-94.04833', ',0/69']
['33.09167', '-93.90333', ',0/90']
['33.02667', '-93.89167', ',0/70']
['34.34111', '-93.50056', ',0/50']
['34.24833', '-93.67667', ',0/10']
['34.22500', '-93.89500', ',0/70']
['33.76833', '-92.48500', ',0/70']
['33.74500', '-92.47667', ',0/70']
['33.68000', '-92.46667', ',0/70']

I'd start by splitting the line into a list, x = line.split(",") and then do the slicing on the individual pieces. Takes care of the commas and makes it a little easier to work with.

try:

def latLongDate(file):
    l = []
    for line in file:
        x = line.split(",")
    
        lat = x[0].strip()
        latDeg = int(lat[0:2])
        latMin = float(lat[2:4])/60
        latSec = float(lat[4:6] + "." + lat[6:])/3600
        latCalc = str(latDeg + latMin + latSec)
        
        lon = x[1].strip()
        lonDeg = int(lon[0:3])
        lonMin = float(lon[3:5])/60
        lonSec = float(lon[5:7] + "." + lon[7:])/3600
        lonCalc = '-' + str(lonDeg + lonMin + lonSec)
        
        date = x[2].strip()
        dateCalc = date[0:2] + "/" + date[2:4]
        
        # add our line to list l
        # do string formatting to lat/lon to display only 5 decimal points
        l.extend([[('%.5f' % float(latCalc)), ('%.5f' % float(lonCalc)), dateCalc]])
        # print out each line to stdout
    return l

Should also work if the decimal part of Seconds isn't given as a fixed number of digits (just in case).