Add columns to a .DBF file and load data from a tab delimited file

user_11277569 0
Viewed 232

I have a couple thousand .DBF files which need to be updated. These files currently have a header with one or two columns (Name, Geopath) or (Name), and a single row of data.

Updates include:

  1. deleting the geopath column if it exists
  2. adding additional columns
  3. populating the new columns in the (only) data row with data from a tab delimited file.

I am unable to add the columns and populate them. (See below for error)

At a high level, my intention is to:

Loop through the records in a source file For each record: Use the 'FileName' in the record to open the appropriate .dbf file (in the current directory) Add additional column headers Populate the new columns with data from the current record in the source file

Source file - tab delimited, with columns: GUID C(24) VenueName C(100) Network C(50) Address1 C(75) City C(50) SVID Float Filename (ex: test1.dbf)

This works for step 1:

import dbf    
    
file = open('testing.tsv')
for line in file:   #'GUID\tVenueName\tNetworkName\tAddress1\tCity\tSalesVenueID\n'
    fields = line.strip().split('\t')
    res=[fields[0], fields[1], fields[2], fields[3], fields[4],fields[5], fields[6]]
    print("the elements of the list are : " + str(res))
    #fields[0]=Guid, fields[1]=VenueName, fields[2]=NetworkName, fields[3] = Address1, fields[4]=City, fields[5]=SalesVenueID, fields[6]=FilePath
    filename=fields[6]
    print("filename is: " + str(filename))
    if not str(filename)=='FileName':
        with dbf.Table(str(filename)) as db:
                     
            try: 
                db.delete_fields('geojson')
            except Exception:
                pass
            db.pack()

For steps 2 and 3, this slightly different snippet writes the columns but errors with 'data to append must be a tuple, dict, record, or template; not a <class 'str'>' at the table.append step.

import dbf    
    
file = open('testing.tsv')
for line in file:   #'GUID\tVenueName\tNetworkName\tAddress1\tCity\tSalesVenueID\n'
    fields = line.strip().split('\t')
    res=[fields[0], fields[1], fields[2], fields[3], fields[4],fields[5], fields[6]]
    print("the elements of the list are : " + str(res))
    #fields[0]=Guid, fields[1]=VenueName, fields[2]=NetworkName, fields[3] = Address1, fields[4]=City, fields[5]=SalesVenueID, fields[6]=FilePath
    currentfilename=str(fields[6])
    print("filename is: " + currentfilename)
    if not currentfilename=='FileName':   #Ignore header row

# create an in-memory table
        table = dbf.Table(
            filename=currentfilename,
            field_specs='GUID C(24); VenueName C(100); Network C(50) ; Address1 C(75); City C(50); SVID C(20)',
            on_disk=True,
            )
        table.open(dbf.READ_WRITE)

# add some records to it
        for datum in (
            (tuple(res))
            ):
            table.append(datum)

# iterate over the table, and print the records
        for record in table:
            print(record)
            print('--------')


   table.pack()

See screenshot for what is in variable variable 'res'

Edited to include the final (working) code:

    import dbf    
    
    file = open('SourceFile.tsv')
    for line in file:   
    #'GUID\tVenueName\tNetworkName\tAddress1\tCity\tSalesVenueID\n'
    fields = line.strip().split('\t')
    res=[fields[0], fields[1], fields[2], fields[3], fields[4]]
    print("the elements of the list are : " + str(res))
    #fields[0]=VenueName, fields[1]=NetworkName, fields[2] = Address1, 
    fields[3]=City, fields[4]=SalesVenueID, fields[5]=FilePath
    currentfilename=str(fields[5])
    print("filename is: " + currentfilename)
    if not currentfilename=='FileName':   #Ignore header row

    # create or open existing file
        table = dbf.Table(
            filename=currentfilename,
            field_specs='VenueName C(100); Network C(50) ; Address1 
C(75); City C(50); SVID C(20)',
            on_disk=True,
            )
        table.open(dbf.READ_WRITE)

# Update record
    VenueName=fields[1]
        
    try:
        table.append(tuple(res)) 
            
    except Exception as e:
        print('--------')
        print('--------')
        print('Error on VenueName: ' + VenueName )
        print(e)
        pass

    # iterate over the table, and print the records
    for record in table:
        print(record)
        print('--------')
    table.pack()
dbf shapes python
2 Answers

When you iterate over tuple(res) to get datum, it looks like you're extracting individual field values, & at least one of those is a string. From the error message, I would bet the Table.append() method is expecting a whole row at a time, so the res variable would be the argument to pass, as that represents the whole row.

Also, the values for fields and res look identical (unless you are intentionally only storing the first 7 values of fields). If that's the case, you can just refer to fields, rather than having two variables performing the same role. Of course, if doing it the way you have it now makes it easier for you to read your code, no harm.

user_20817600

untested:

import dbf

NEW_FIELDS = "GUID C(24);VenueName C(100);Network C(50);Address1 C(75);City C(50);SVID C(20)"

    
file = open('testing.tsv')
for line in file:   #'GUID\tVenueName\tNetworkName\tAddress1\tCity\tSalesVenueID\n'
    fields = line.strip().split('\t')
    guid, venue_name, network, address1, city, svid, filename = fields
    if filename != 'FileName':
        with dbf.Table(filename) as db:
            # remove geojson if it exists
            if 'geojson' in dbf.fields(db):
                db.delete_fields('geojson')
            # add the new fields
            db.add_fields(NEW_FIELDS)
            with db[0] as rec:         # the first and only record
                rec.guid = guid
                rec.venuename = venuename
                rec.network = network
                rec.address1 = address1
                rec.city = city
                rec.svid = svid

Some comments about the code:

  • the file is opened in text mode, so all lines are already str (no need to keep calling str() on the data)

  • the pack() function removes deleted rows; it has no effect on deleted columns

  • != is preferred over not something == something_else

  • assigning multiple variables at once is common, just make sure you have enough (in other words, if the header line ended with "...\tCity\n" then the multiple assignment would fail).

  • changing a list into a tuple doesn't change your algorithm -- you're still trying to append one data field at a time; the correct line is:

    table.append(tuple(res)) # add the whole row at once

Post a comment if the code above has any errors, and good luck!

user_2088800
Related