5

I would like to convert 1000 dbf files to CSV. All dbf files are in one folder and they have sequential names (S:\output_tables\tcb1.dbf, S:\output_tables\tcb2.dbf, S:\output_tables\tcb3.dbf, up to S:\output_tables\tcb1000.dbf).

I would like S:\output_tables\1.csv, S:\output_tables\2.csv, S:\output_tables\3.csv up to S:\output_tables\1000.csv

I would like to eventually run in a ARCGIS 10.1 model so ArcPy would be the preferred solution but I'm open to other options.

  • DBF is a part of a shapefile and is a very common GIS component and once operational I would like to run in a GIS model as part of a larger GIS process. – If you do not know- just GIS Apr 16 '14 at 17:44
  • Are ALL of the DBFs you are talking about associated with shapefiles, or only some, or none? – blah238 Apr 16 '14 at 17:52
  • A mixture or I would use the 'Export Features to ASCII tool'. I can do them as two batches I suppose one with the tool and one with code but it would be nice for the model to have them as one single ArcPy script. – If you do not know- just GIS Apr 16 '14 at 18:02
  • why not just convert them with ogr2ogr in a shell script? – Llaves Apr 23 '14 at 3:20
6

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!

4

This should work for both shapefile and separate dbf file

import os
import arcpy
import csv

def dbf2csv(dbfpath, csvpath):
    ''' To convert .dbf file or any shapefile/featureclass to csv file
    Inputs: 
        dbfpath: full path to .dbf file [input] or featureclass
        csvpath: full path to .csv file [output]

    '''
    #import csv
    rows = arcpy.SearchCursor(dbfpath)
    csvFile = csv.writer(open(csvpath, 'wb')) #output csv
    fieldnames = [f.name for f in arcpy.ListFields(dbfpath)]

    allRows = []
    for row in rows:
        rowlist = []
        for field in fieldnames:
            rowlist.append(row.getValue(field))
        allRows.append(rowlist)

    csvFile.writerow(fieldnames)
    for row in allRows:
        csvFile.writerow(row)
    row = None
    rows = None

Call this function dbf2csv for every dbf file In your case 1000 times, this is just an example of calling, perhaps it will work for you without any modification

dbf_dir = 'S:/output_tables/'
csv_dir = 'S:/output_tables/csv1/'
for dbf_file in os.listdir(dbf_dir):
    # Loop through all dbf files
    # and export to dbf
    fileName, fileExt = os.path.splitext(dbf_file)  #[0] or [1] for file
    if '.dbf' in fileExt:
        # construct full path to dbf file and csv file
        dbfpath = os.path.join(dbf_dir, fileName+fileExt)
        csvpath = os.path.join(csv_dir, fileName+'.csv')
        if os.path.exists(dbfpath):
            # this may not be necessary
            #    print 'processing: ', dbfpath, csvpath                    
            if not os.path.exists(csvpath):
                ## to prevent overwrite of existing csv file
                ## call the function to convert .dbf file to csv file
                print 'Export nexrad {0} to {1}'.format(dbfpath, csvpath)
                dbf2csv(dbfpath, csvpath)

Since it this python, make sure the indentations are right

  • Nice solution - this was the only one I could get to work for me. – HFBrowning Jun 17 '15 at 18:21
3

If you look for a full arcpy solution (without dbf) you can use

import glob
glob.glob('S:\\output_tables\\*.dbf')

for listing you tables, then

arcpy.ListFields() 

for the field names and

outname = os.path.basename(inputtable)[3:-4] + ".csv"

to create your output names

and finally

arcpy.da.SearchCursor()

to get a Python iterable that you can use directly with csv.writerow() (and even with csv.writerows() to e verified)

  • Based on my testing, ListTables() will not list DBFs associated with shapefiles. So you'll have to either combine this with ListFeatureClasses(), or use ListFiles() or Python's glob() or listdir() functions. – blah238 Apr 16 '14 at 18:14
  • You're right, I'll change that – radouxju Apr 16 '14 at 20:44
1

Here is the best solution I have found thus far using dbfpy and arcpy.

import arcpy
from dbfpy import dbf
from arcpy import env

def DBFtoCSV():
    '''Convert every DBF table into CSV table. 
    '''
    env.workspace = pathlist[1] # Set new workplace where tables are located 
    tablelist = arcpy.ListTables() # list tables in file
    for table in tablelist: # iterate through every table
        #make sure you are just working with .dbf tables 
        if table.endswith('.dbf'):
            #name csv the same as the .dbf table just with .csv at the end
            csv_fn = table[:-4]+ ".csv"
            with open(pathlist[2]+csv_fn,'wb') as csvfile: # name output path
                in_db = dbf.Dbf(pathlist[1]+table)
                out_csv = csv.writer(csvfile)
                #copy row names and items in rows from dbf to csv
                names = []
                for field in in_db.header.fields:
                    names.append(field.name)
                out_csv.writerow(names)
                for rec in in_db:
                    out_csv.writerow(rec.fieldData)
                in_db.close()
        #keep track of processing
        print "\n Processing ",table[:-4]+".csv table complete."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.