473,324 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

csv format to DBase III format

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.

Jan 3 '06 #1
6 2779
co***********@yahoo.com.hk wrote:
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/Coo.../Recipe/362715

Peter
Jan 3 '06 #2

Peter Otten wrote:
co***********@yahoo.com.hk wrote:
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/Coo.../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

Jan 6 '06 #3

Peter Otten wrote:
co***********@yahoo.com.hk wrote:
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/Coo.../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

Jan 7 '06 #4
William wrote:
Peter Otten wrote:
co***********@yahoo.com.hk wrote:
> 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/Coo.../Recipe/362715

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
Jan 7 '06 #5

Peter Otten wrote:
William wrote:
Peter Otten wrote:
co***********@yahoo.com.hk wrote:

> 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/Coo.../Recipe/362715

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


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

Jan 10 '06 #6
Hi,
>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/Coo.../Recipe/362715
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.

....> 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

Jan 15 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Nicolae Fieraru | last post by:
Hi All, I have an access query which retrieves some record. I can export the results of it as an excel document, using the function TransferText. What I actually need is to export the query as a...
0
by: Sameers (theAngrycodeR) via .NET 247 | last post by:
I think I will get MAD very soon. Its very weired problem I amfacing here. Let me explain. I have two DBF files from which I want to export data in CSVformat. I created a desktop application and...
0
by: Meer | last post by:
Hi, I'm having problem to read *.DBF (DBase File) using ASP.NET. Below is my code, taken from one of forumer here.... Dim connection As OleDbConnection Dim command As OleDbCommand Dim reader As...
2
by: Yannick Turgeon | last post by:
Hello all, I'm (still) using A97. I'd like to save/export a table (a link to a SS2000 table) in dBASE 5 format, programaticly using VBA. Is that possible? I'm currently doing it by hand but...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.