-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpdbf
executable file
·160 lines (135 loc) · 6.19 KB
/
pdbf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
#!/usr/bin/env python
from dbf import VfpTable
import dbf
import sys
import os
import sqlite3
import argparse
import re
parser = argparse.ArgumentParser(description="read and convert visual foxpro database files")
group = parser.add_mutually_exclusive_group()
group.add_argument("-n", "--info", action="store_true", help="display dbf info")
group.add_argument("-p", "--pretty", action="store_true", help="display pretty-printed table")
group.add_argument("-t", "--table", action="store_true", help="display tab-separated table")
group.add_argument("-d", "--dump", action="store_true", help="dump table")
group.add_argument("-s", "--sqlite", metavar='DB', help="write to sqlite database file DB")
parser.add_argument("-F", "--force", action="store_true", help="overwrites existing table in sqlite database")
parser.add_argument("-v", "--verbose", action="store_true")
parser.add_argument("-c", "--concat", action="store_true", help="put different dbfs in same table")
parser.add_argument("-i", "--ignore-errors", action="store_true", help="tell DBF library to ignore typecasting errors")
parser.add_argument("-f", "--fields", help="only use field number n")
parser.add_argument('infiles', metavar='DBF', nargs='+', help="input files in visual foxpro format")
args = parser.parse_args()
conn = None
def on_init():
""" open sqlite db if -s and use run to process dbf"""
global conn, fields
if args.fields: fields = map(int, args.fields.split(","))
# import pdb
# pdb.set_trace()
if args.sqlite:
print "sqlite: Opening db %s ..." % (args.sqlite)
conn = sqlite3.connect(args.sqlite)
try:
for dbf_name in args.infiles:
run(dbf_name)
except sqlite3.Error as e:
print "sqlite: " + e.args[0]
finally:
if args.sqlite:
print "sqlite: Commit and close ..."
conn.commit()
conn.close()
def run(dbf_name):
global conn, fields
table = dbf.VfpTable(dbf_name, codepage='utf8')
table.open()
if not args.fields: fields = range(len(table.field_names))
if args.verbose: print "--> " + dbf_name + " <--"
if args.table:
for fld in fields:
sys.stdout.write('%s\t'%(table.field_names[fld]))
print "-------------"
for rec in table[:min(2, len(table))]:
for fld in fields:
sys.stdout.write('%s\t'%(rec[fld]))
print
elif args.pretty:
out = [ table.field_names ]
for el in table:
out.append([unicode(s).encode('utf-8').strip() for s in el ])
print_table(out)
elif args.sqlite:
""" Important part is -c (concat) do not drop table, append records
from new dbf file into the existing table
if number of columns change, means new table structure is found
raise exception and exit"""
table_name = None
file_date = None
if args.concat:
filename_parts = re.split('[\.\/]', dbf_name)
#! HACK hard code position require file name ./20050522/si_ci.dbf
# result of find in the /f/sip/ directory
file_date = filename_parts[2]
table_name = filename_parts[3]
if not (re.match("[0-9]+", file_date) and re.match("^si_.*", table_name)):
raise Exception("concat %s date and name error: %s %s"%(dbf_name, file_date, table_name))
netto_name = table_name if args.concat else re.sub('[^A-Za-z0-9]', '_', dbf_name).strip('_')
print "Importing %s into table %s ..." % (dbf_name, netto_name)
cur = conn.cursor()
cur.execute("PRAGMA table_info(%s)"%(netto_name))
field_count = len(cur.fetchall())
print "fieldcount of %s is %d"%(netto_name, field_count)
if field_count == 0:
""" table does not exist"""
cr_stat = "CREATE TABLE IF NOT EXISTS %s (%s%s)" % (netto_name, 'date,' if args.concat else '', ",".join([table.field_names[f] for f in fields]))
if args.force: conn.execute('DROP TABLE IF EXISTS `%s` ' % (netto_name))
try:
conn.execute(cr_stat)
except Exception, e:
print "sqlite: Error creating table %s!" % netto_name
print "You might want to try the --force option."
raise e
elif field_count != len(fields)+1:
""" table structure changed """
raise Exception("table structure changed")
for el in table:
#! HACK dbf module _v2.py line 3887 replace \xc2 \xa0 with ''
ins_stat = "INSERT INTO %s VALUES (%s'%s')" % (netto_name, "'"+file_date+"'," if args.concat else "", "','".join([str(el[f]).encode('utf-8',errors='ignore').strip().replace("'","''") for f in fields ]))
if args.verbose: print "sqlite: executing: %s" % ins_stat
conn.execute(ins_stat)
elif args.dump:
print( "\t%s\t%s\t" % (os.path.getsize(dbf_name),dbf_name))
for rec in table:
for fld in fields:
print('%s: %s'%(table.field_names[fld], rec[fld]))
print
else:
sys.stdout.write( "%s\n"%str(table))
table.close()
def print_table(rows):
"""print_table(rows)
Prints out a table using the data in `rows`, which is assumed to be a
sequence of sequences with the 0th element being the header.
"""
# - figure out column widths
widths = [ len(max(columns, key=len)) for columns in zip(*rows) ]
#widths = [len(str(max(i, key=lambda x: len(str(x).strip()))).strip()) for i in zip(header, data)]
#print widths
# - print the separator
print('+-'+ '-+-'.join( '-' * width for width in widths ) +'-+')
# - print the header
header, data = rows[0], rows[1:]
print('| '+
' | '.join( format(title, "%ds" % width) for width, title in zip(widths, header) )
+' |')
# - print the separator
print('|-'+ '-+-'.join( '-' * width for width in widths ) +'-|')
# - print the data
for row in data:
print('| '+
" | ".join( format(cdata, "%ds" % width) for width, cdata in zip(widths, row) )
+' |')
# - print the separator
print('+-'+ '-+-'.join( '-' * width for width in widths ) +'-+')
on_init()