Converting DBF numeric-like column to string CSV

Question

I need to convert DBF file with numeric values into csv file as strings

DBF file column contains numeric data:

NUMBER,C,12 | ...
584413
079711
478293
000865

I use this python function to convert it to csv:

from dbfread import DBF
def dbf_to_csv(dbf_table_pth):#Input a dbf, output a csv, same name, same path, except extension
    csv_fn = dbf_table_pth[:-4]+ ".csv" #Set the csv file name
    table = DBF(dbf_table_pth)# table variable is a DBF object
    with open(csv_fn, 'w', encoding='utf-8', newline = '') as f:# create a csv file, fill it with dbf content
        writer = csv.writer(f, dialect='excel')
        writer.writerow(table.field_names)# write the column name
        for record in table:# write the rows
            writer.writerow(list(record.values()))
    return csv_fn# return the csv name

It leads to csv-result below, when I open it in notepad:

NUMBER, ...
"584413,0", ...
"79711,0", ...
"478293,0", ...
"865,0", ...

How can I improve func to get results like this?:

NUMBER, ...
584413, ...
079711, ...
478293, ...
000865, ...

Looks like there is a locale issue where the decimal marker is `,`. From this question [Convert locale](https://stackoverflow.com/questions/1779288/how-to-convert-a-string-to-a-number-if-it-has-commas-in-it-as-thousands-separato) maybe something like: `import locale locale.setlocale( locale.LC_ALL, 'de_DE.UTF-8' ) int(locale.atof('584413,0')) 584413`. You would have to transform each row. Maybe use custom parser as shown here [Custom field](https://dbfread.readthedocs.io/en/latest/introduction.html#custom-field-types) — Adrian Klaver, Apr 29 '23 at 15:29
I can't replicate, so you will need to provide more information about your environment. — Adrian Klaver, Apr 29 '23 at 15:59

Adrian Klaver · Answer 1 · 2023-04-29T21:29:33.213

Based on guess of how the data is formatted in your DBF table. Use FieldParser from here Custom Field Types to change the output for the NUMBER field. Uses locale to set to locale that recognizes , as decimal marker and then locale.atof to convert to float and int() to convert to integer.

import locale
from dbfread import DBF, FieldParser

locale.setlocale( locale.LC_ALL, 'de_DE.UTF-8' )

tbl_db = DBF('test.dbf')

for row in tbl_db:
    print(row)

OrderedDict([('NUMBER', '3298,0'), ('NAME', 'test')])
OrderedDict([('NUMBER', '6214'), ('NAME', 'dog')])
OrderedDict([('NUMBER', '9418,0'), ('NAME', 'cat')])

class NumericConvert(FieldParser):
    def parseC(self, field, data):
        if field.name == "NUMBER":
            return int(locale.atof(data.decode()))
        else:
            return data

tbl_db = DBF('test.dbf', parserclass=NumericConvert)

for row in tbl_db:
    print(row)

OrderedDict([('NUMBER', 3298), ('NAME', b'test      ')])
OrderedDict([('NUMBER', 6214), ('NAME', b'dog       ')])
OrderedDict([('NUMBER', 9418), ('NAME', b'cat       ')])

Converting DBF numeric-like column to string CSV

1 Answers1