csv format to DBase III format

[coriolis_wong]

Hi,

I need to transfer csv format file to DBase III format file.
How do i do it in Python language?


Any help is appreciated.
Thanks.

 

[Peter Otten]

I need to transfer csv format file to DBase III format file.
How do i do it in Python language?

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/362715

Peter

 

[coriolis_wong]

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/362715

Peter

Hi,

I create a dbf file, it can be opened by Excel but it cannot be opened
by Access. Where is the error in my script. My script is as follows,

def dbfwriter(f, fieldnames, fieldspecs, records):
""" Return a string suitable for writing directly to a binary dbf
file.

File f should be open for writing in a binary mode.

Fieldnames should be no longer than ten characters and not include
\x00.
Fieldspecs are in the form (type, size, deci) where
type is one of:
C for ascii character data
M for ascii character memo data (real memo fields not
supported)
D for datetime objects
N for ints or decimal objects
L for logical values 'T', 'F', or '?'
size is the field width
deci is the number of decimal places in the provided decimal
object
Records can be an iterable over the records (sequences of field
values).

"""
# header info
ver = 3
now = datetime.datetime.now()
yr, mon, day = now.year-1900, now.month, now.day
numrec = len(records)
numfields = len(fieldspecs)
lenheader = numfields * 32 + 33
# lenrecord = sum(field[1] for field in fieldspecs) + 1
num = 0
for field in fieldspecs :
num = num + int(field[1])

lenrecord = num + 1

hdr = struct.pack('<BBBBLHH20x', ver, yr, mon, day, numrec,
lenheader, lenrecord)
f.write(hdr)

# field specs
for name, (typ, size, deci) in itertools.izip(fieldnames,
fieldspecs):
# name = name.ljust(11, '\x00')
name = name.ljust(11)
fld = struct.pack('<11sc4xBB14x', name, typ, size, deci)
f.write(fld)

# terminator
f.write('\r')

# records
for record in records:
f.write(' ') # deletion flag
for (typ, size, deci), value in itertools.izip(fieldspecs,
record):
if typ == "N":
# value = str(value).rjust(size, ' ')
value = str(value).rjust(size)
elif typ == 'D':
# value = value.strftime('%Y%m%d')
value = value
elif typ == 'L':
value = str(value)[0].upper()
else:
# value = str(value)[:size].ljust(size, ' ')
value = str(value)[:size].ljust(size)
assert len(value) == size
f.write(value)

# End of file
f.write('\x1A')
f.close()


# -------------------------------------------------------
# Example calls
if __name__ == '__main__':

import sys, csv
from cStringIO import StringIO
# from operator import itemgetter


# Create a new DBF
# f = StringIO()

f = open('test.dbf','w')
fieldnames = ['CUSTOMER_ID','EMPLOY_ID','ORDER_DATE','ORDER_AMT']
fieldspecs = [('C',11,0),('C',11,0),('D',8,0),('N',12,2)]
records = [['MORNS','555','19950626','17.40'],\
['SAWYH','777','19950629','97.30'],\
['WALNG','555','19950522','173.40']]


dbfwriter(f, fieldnames, fieldspecs, records)



William

 

[William]

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/362715

Peter

Hi,

I create a dbf file, it can be opened by Excel but it cannot be opened
by Access. Where
is the error in my script. My script is as follows:

#!/opt/bin/python2.3

import struct, datetime,itertools,time


def dbfwriter(f, fieldnames, fieldspecs, records):

""" Return a string suitable for writing directly to a binary dbf
file.

File f should be open for writing in a binary mode.

Fieldnames should be no longer than ten characters and not include
\x00.
Fieldspecs are in the form (type, size, deci) where
type is one of:
C for ascii character data
M for ascii character memo data (real memo fields not
supported)
D for datetime objects
N for ints or decimal objects
L for logical values 'T', 'F', or '?'
size is the field width
deci is the number of decimal places in the provided decimal
object
Records can be an iterable over the records (sequences of field
values).

"""
# header info
ver = 3
now = datetime.datetime.now()
yr, mon, day = now.year-1900, now.month, now.day
numrec = len(records)
numfields = len(fieldspecs)
lenheader = numfields * 32 + 33
# lenrecord = sum(field[1] for field in fieldspecs) + 1
num = 0
for field in fieldspecs :
num = num + int(field[1])

lenrecord = num + 1

hdr = struct.pack('<BBBBLHH20x', ver, yr, mon, day, numrec,
lenheader, lenrecord)
f.write(hdr)

# field specs
for name, (typ, size, deci) in itertools.izip(fieldnames,
fieldspecs):
# name = name.ljust(11, '\x00')
name = name.ljust(11)
fld = struct.pack('<11sc4xBB14x', name, typ, size, deci)
f.write(fld)

# terminator
f.write('\r')

# records
for record in records:
f.write(' ') # deletion flag
for (typ, size, deci), value in itertools.izip(fieldspecs,
record):
if typ == "N":
# value = str(value).rjust(size, ' ')
value = str(value).rjust(size)
elif typ == 'D':
# value = value.strftime('%Y%m%d')
value = value
elif typ == 'L':
value = str(value)[0].upper()
else:
# value = str(value)[:size].ljust(size, ' ')
value = str(value)[:size].ljust(size)
assert len(value) == size
f.write(value)

# End of file
f.write('\x1A')
f.close()


# -------------------------------------------------------
# Example calls
if __name__ == '__main__':

import sys, csv
from cStringIO import StringIO
# from operator import itemgetter


# Create a new DBF
# f = StringIO()

f = open('test.dbf','w')
fieldnames = ['CUSTOMER_ID','EMPLOY_ID','ORDER_DATE','ORDER_AMT']
fieldspecs = [('C',11,0),('C',11,0),('D',8,0),('N',12,2)]
records = [['MORNS','555','19950626','17.40'],\
['SAWYH','777','19950629','97.30'],\
['WALNG','555','19950522','173.40']]


dbfwriter(f, fieldnames, fieldspecs, records)

Thanks,

William

 

[Peter Otten]

I create a dbf file, it can be opened by Excel but it cannot be opened
by Access. Where is the error in my script.

No idea, but here's some brainstorming.

f = open('test.dbf','w')

First make sure that you open the file in binary mode 'wb'.
If you have an application around that can generate dbfs you could compare a
manually created file with the python-generated one. Have you tried the dbf
with no records? If that is opened without error, you could successively
add records until you find the culprit. Finally, if Excel and Access
disagree about the dbf's validity, the Access import filter could be
broken. Are there other filters for the Dbase family (Foxpro, Clipper)? Try
one of them.

Peter

 

[William]

No idea, but here's some brainstorming.


First make sure that you open the file in binary mode 'wb'.
If you have an application around that can generate dbfs you could compare a
manually created file with the python-generated one. Have you tried the dbf
with no records? If that is opened without error, you could successively
add records until you find the culprit. Finally, if Excel and Access
disagree about the dbf's validity, the Access import filter could be
broken. Are there other filters for the Dbase family (Foxpro, Clipper)? Try
one of them.

Peter

I have no idea too. I use Foxpro to open the file, but it is failure.
The warning message
is as follows
"Either the table record count does not match the actual records in the
table, or the file size on the disk does not match the expected file
size from the table header."

Anybody have any idea?

William

 

[Thomas Ganss]

Hi,

....>
I have no idea too. I use Foxpro to open the file, but it is failure.
The warning message is as follows
"Either the table record count does not match the actual records in the
table, or the file size on the disk does not match the expected file
size from the table header."

The error message is quite clear -
in the .dbf format the record count is written to the file header.

The filesize should be record_count*recordsize + headersize.

There are some options to fix a dbf, even via foxpro.
Comparing file size / header info should point you to your error.

Such a task (if targeted to run on a win machine) is probably
better done in foxpro or another dbase clone / file handler:
would take probably less a handful of lines and be much safer.


my 0.02 EUR

thomas