within working on modifying hr_attendance module, so, it can convert MS Access file to postgresql we wrote this python script that can be used manually :
#!/usr/bin/python
import psycopg2 # module to connect to postgresql
import sys
import csv # to read csv files
from subprocess import call # to run system commands
mdb_path='/path/to/*.mdb file' # path to MS Access file
conversion_string="mdb-export %s CHECKINOUT | tail -n +2 | cut -d, -f1,2,3 | tr / - >/path/to/*.csv file"%mdb_path
call(conversion_string,shell=True) # getting the .csv file from .mdb file using {mdb-tools >> in Redhat/Centos/Fedora} || {mdbtools >> in Debian/Ubuntu
con=None # Initialize no connection
con = psycopg2.connect(database='DB_Name', user='postgres') # Connect to database named(test) with user(postgres)
cur = con.cursor()
i=1 # counter to count the number of errors, i.e, number of not inserted rows that mean the data is already exist
table_name='hr_attendance' # the table name to insert data
with open('/path/to/*.csv file','r') as f:
re = csv.reader(f,delimiter=',',quoting=csv.QUOTE_NONE)
for r in re:
employee_id= int(r[0])
name=r[1].replace('\"','')
checktype=r[2].replace('\"','')
if checktype == "I":
action = "sign_in"
else:
action = "sign_out"
try:
cur.execute("insert into %s (employee_id, name, action, day) values(%d,'%s','%s',date('%s'))"%(table_name,employee_id,name,action,name))
except psycopg2.DatabaseError, e:
print 'error %d'%i
i+=1
con.commit()
Now, I need to create a button to run this script to update the postgresql using the MS Accsess file
Thanks in advance :)