Python – Converting CSV to DBF

arcgis-10.1arcpypythontable

I have a python created CSV that I am looking to turn into a dbf but it is not getting read properly by the TABLE TO TABLE conversion tool. I select the csv, set the output to be XXX.dbf but the output has 1 field. It contains the number of rows I need, but the values of each row is the comma separated data. I have no spaces or special characters.

Arcgis 10.1
enter image description here

Here is the PYTHON used to create the table. This is my first time using the CSV writer; maybe I am implementing it wrong to feed the dbf.

#This script looks in a defined folder for shapefiles and creates the beginnings of a data dictionary.
# Creator: David Campbell
# Date: March 12, 2015

import arcpy, os, sys, csv,datetime, time
from arcpy import env

print 'Start @ ' + datetime.datetime.fromtimestamp(time.time()).strftime('%H:%M:%S')

############## User Values #############
project = 'P747'
TSA_Table = 'TSA_LB_V2'
CLS_Table = 'CLS_LB_V2'
MDL_Table = 'MDL_LB_V2'
ARIS_Table = 'ARIS_V5'
RSA_Table = 'RSABLKS_V5'
AVI_Table = 'AVI_ATTR_V2'
FolderLocation = r"\\silver\clients\Projects\P747\3_Landbase\LB1\Submission_Data\LB_20150304"
LargestNumberForUniqueValues = 20
SDELocal = "C:\\Users\\david\\AppData\\Roaming\\ESRI\\Desktop10.1\\ArcCatalog\\TFC.sde\\"
Project_DB = "C:\\Users\\david\\AppData\\Roaming\\ESRI\\Desktop10.1\\ArcCatalog\\P747.sde\\"
ORA = 'ORATFC01'
#######################################

open(os.path.join(FolderLocation, "DD.csv"), "w")
FCView = FolderLocation + "\\FCView.lyr"
env.workspace = FolderLocation
SDELOC = SDELocal

if arcpy.Exists(FolderLocation + "\\DD.csv"):
    arcpy.Delete_management(FolderLocation + "\\DD.csv")

# if arcpy.Exists(SDELOC + "DD_Table") == False:
#     print "SDE Table does not exist"


if arcpy.Exists(SDELOC + "DD_Table"):
    arcpy.Delete_management(SDELOC + "DD_Table")

if arcpy.Exists(SDELOC + "DD_Table") == True:
    print "True"
    sys.exit()

a = "ID,PROJECT_NO,DATA_NAME,DATA_TYPE,DATA_LOCATION,SOURCE_CONTACT_NAME,PROJECTION,DATUM,UNITS,DATA_PRECISION,TOLERANCE,SCALE_OF_CAPTURE,GENERATION_ORIGIN,FEATURE_DESC,COLUMN_NAME,FLDTYPE_NAME,COLUMN_WIDTH,COLUMN_DECIMAL,COLUMN_DESC"
csvfile = FolderLocation + "\\DD.csv"
allrows = []
allrows.append(a)
SDETable = [TSA_Table, CLS_Table, MDL_Table, ARIS_Table, AVI_Table, RSA_Table]
#SDETable = ['TSA_LB_V2','CLS_LB_V2', 'MDL_LB_V2','ARIS_V5', 'RSABLKS_V5', 'AVI_ATTR_V2']

x = 1
for r in arcpy.ListFiles("*.shp"):

    if arcpy.Exists(FCView):
        arcpy.Delete_management(FCView)
    arcpy.MakeTableView_management(r, FCView)

    fields = arcpy.ListFields(r)
    desc = arcpy.Describe(r)

    for field in fields:
        FN = field.name
        DataString = str(x) + "," + project + ","+ r + "," + desc.shapeType + "," + FolderLocation + "," + "," + desc.spatialReference.name + "," + desc.spatialReference.GCS.datumName + "," + "Meters" + "," + "," + "," + "," + "," + "," + FN + "," + field.type + "," + str(field.length) + "," + str(field.precision)
        if field.type in ('String', 'SmallInteger'):
            UniqueValues = sorted(set([XX[0] for XX in arcpy.da.SearchCursor(FCView, FN)]))

            if int(len(UniqueValues)) >= LargestNumberForUniqueValues:
                b = DataString + ", Variable values"
                allrows.append(b)
                x += 1
            if int(len(UniqueValues)) < LargestNumberForUniqueValues:
                for XX in UniqueValues:
                    if XX == ' ':
                        continue
                    c = DataString + ", " + str(XX)
                    allrows.append(c)
                    x += 1
        else:
            e = DataString + ", 0 - X"
            allrows.append(e)
            x += 1


env.workspace = Project_DB
for SDE in SDETable:
    for SDEField in arcpy.ListFields(SDE):
        SDE_FN = SDEField.name
        ORASDEString = str(x) + "," + project + "," + str(SDE) + ",SDE Table" + "," + ORA + "," + "," + "," + "," + "," + "," + "," + "," + "," + "," + SDE_FN + "," + SDEField.type + "," + str(SDEField.length) + "," + str(SDEField.precision)

        if SDEField.type in ('String', 'SmallInteger'):
            UniqueValues2 = sorted(set([Y[0] for Y in arcpy.da.SearchCursor(SDE, SDE_FN)]))

            if int(len(UniqueValues2)) >= LargestNumberForUniqueValues:
                f = ORASDEString + ", Variable values"
                allrows.append(f)
                x += 1
            if int(len(UniqueValues2)) < LargestNumberForUniqueValues:
                for XXX in UniqueValues2:
                    g = ORASDEString + ", " + str(XXX)
                    allrows.append(g)
                    x += 1
        else:
            h = ORASDEString + ", 0 - X"
            allrows.append(h)
            x += 1

with open(csvfile, "w") as output:
    writer = csv.writer(output, lineterminator='\n')
    for val in allrows:
        writer.writerow([val])

env.workspace = SDELOC

if arcpy.Exists(FCView):
    arcpy.Delete_management(FCView)
arcpy.MakeTableView_management(csvfile, FCView)
arcpy.TableToTable_conversion(FCView, SDELOC,  "DD_Table")
print 'Completed @ ' + datetime.datetime.fromtimestamp(time.time()).strftime('%H:%M:%S')

Best Answer

You have quotes at the start and end of every line, and none in between, so ArcGIS is interpreting each line as a single text field. Remove the quotes and you should be ok in reading the file.

Otherwise if you want a programmatic solution to building the DBF file take a look at the Python dbf module for writing the DBF file. Using dbf.from_csv will quickly convert a CSV to a DBF file in memory wih

table = dbf.from_csv(path)

Or write directly to a file with the filename parameter

dbf.from_csv(path, "out_path.dbf")

This is unlikely to do precisely what you want though as it assumes that the first row of the CSV is not headers, but rather data, and that all fields are string fields. For something more complex you should work with the inbuilt Python csv module to read the data, and specify the table format yourself before writing the data.

Related Solutions

[GIS] Converting CSV to Shapefile using pyshp

You need to ensure that the angle read from the csv is cast as an integer and not a string as it may be at present. To do this change the line: angle.append(row[3]) to: angle.append(int(row[3]))

The second thing to correct is that you have not specified a length of the angle field, which can cause pyshp some problems, so specify a length, in this case 4 should be suitable:

w.field("angle","N",4)

These two fixes should allow the data to be written to the angle field of the attribute table.

Bulk Convert DBF to CSV Using Python in ArcGIS 10.1

I have only tested this very briefly (and with a limited variety of data), but this script demonstrates one way this might be accomplished:

import arcpy
import csv
import os
import codecs
import cStringIO

def batch_convert_dbf_to_csv(input_dir, output_dir, rename_func=None):
    """Converts shapefiles and standalone DBF tables within the input directory
    input_dir to CSV files within the output directory output_dir. An
    optional function rename_func may be used to manipulate the output file
    name."""
    # Set workspace to input directory
    arcpy.env.workspace = input_dir

    # List shapefiles and standalone DBF tables in workspace
    tables = list_tables()

    # Only proceed if there actually exists one or more shapefiles or DBF tables
    if tables:
        # Create output directory structure
        make_output_dir(output_dir)

        # Loop over shapefiles and DBF tables
        for table in tables:
            # Generate output filename
            output_name = os.path.splitext(os.path.basename(table))[0]
            if rename_func:
                output_name = rename_func(output_name)
            output_csv_file = os.path.join(output_dir,
                output_name + os.extsep + 'csv')

            # List input fields
            fields = list_fields(table)

            # Open input table for reading
            rows = read_rows(table, fields)

            # Set flag indicating whether we are overwriting an existing file
            output_exists = os.path.isfile(output_csv_file)

            # Attempt to create output CSV file
            try:
                write_unicode_csv(output_csv_file, rows, fields)

                # Warn if we overwrite anything
                if output_exists:
                    print 'warning: overwrote {0}'.format(output_csv_file)
                else:
                    print 'wrote {0}'.format(output_csv_file)
            except IOError:
                print 'warning: unable to create output CSV file {0}'.format(
                    output_csv_file)
    else:
        print 'No DBF files found in workspace {0}'.format(input_dir)

def list_tables():
    """Returns a list of shapefiles and standalone DBF tables in the current
    workspace."""
    tables = arcpy.ListFeatureClasses('*.shp')
    tables.extend(arcpy.ListTables('*', 'dBASE'))
    return tables

def list_fields(table):
    """Returns a list of fields in the specified table, excluding the shape
    field if present."""
    desc = arcpy.Describe(table)
    shape_field_name = desc.shapeFieldName if hasattr(
        desc, 'shapeFieldName') else ''
    return [field.name for field in desc.fields
        if field.name != shape_field_name]

def read_rows(table, fields='*'):
    """Generator function that yields the rows of a table, including only the
    specified fields."""
    with arcpy.da.SearchCursor(table, fields) as rows:
        for row in rows:
            yield row

def write_unicode_csv(output_csv, rows, header_row=None):
    """Creates a UTF-8 encoded CSV file specified by output_csv containing the
    specified rows and the optional header_row."""
    with open(output_csv, 'wb') as f:
        f.write(codecs.BOM_UTF8) # Write Byte Order Mark character so Excel
                                 # knows this is a UTF-8 file
        csv_writer = UnicodeWriter(f, dialect='excel', encoding='utf-8')
        if header_row:
            csv_writer.writerow(header_row)
        csv_writer.writerows(rows)

def make_output_dir(path):
    """Creates the output directory structure if it does not already exist."""
    if not os.path.isdir(path):
        try:
            os.makedirs(path)
            print 'created dir {0}'.format(path)
        except OSError:
            if not os.path.isdir(path):
                raise

class UnicodeWriter:
    """
    A CSV writer which will write rows to CSV file 'f',
    which is encoded in the given encoding.
    Based on: https://docs.python.org/2/library/csv.html#examples
    """

    def __init__(self, f, dialect=csv.excel, encoding='utf-8', **kwds):
        # Redirect output to a queue
        self.queue = cStringIO.StringIO()
        self.writer = csv.writer(self.queue, dialect=dialect, **kwds)
        self.stream = f
        self.encoder = codecs.getincrementalencoder(encoding)()

    def writerow(self, row):
        self.writer.writerow([str(s).encode('utf-8') for s in row])
        # Fetch UTF-8 output from the queue ...
        data = self.queue.getvalue()
        data = data.decode('utf-8')
        # ... and reencode it into the target encoding
        data = self.encoder.encode(data)
        # write to the target stream
        self.stream.write(data)
        # empty queue
        self.queue.truncate(0)

    def writerows(self, rows):
        for row in rows:
            self.writerow(row)

if __name__ == '__main__':
    # Configure script here, or modify to take parameters/arguments
    input_dir = r'path\to\input_directory'
    output_dir = r'path\to\output_directory'

    # Customize this function to change renaming logic
    def rename_func(input_name, default='output'):
        # Strips non-digits from string
        output_name = ''.join((char for char in input_name if char.isdigit()))

        # Give filename a sensible default name if there are no digits
        return output_name or default

    # Run it
    batch_convert_dbf_to_csv(input_dir, output_dir, rename_func)

This does not take any arguments/parameters so I leave that up to you. If you want to implement it as a script tool or Python toolbox, read the appropriate ESRI documentation.

It attempts some defensive coding techniques for things like mixed shapefile and standalone DBF content, omitting Shape fields, non-ASCII characters, non-existent directories, warning when it overwrites existing files, etc., but as I said, not well tested, so use at your own risk!

Best Answer

Related Solutions

[GIS] Converting CSV to Shapefile using pyshp

Bulk Convert DBF to CSV Using Python in ArcGIS 10.1

Related Question