Hello All,
Below is the code that I'm using to fetch data from excel and import that into MS Access table and then do some transactions in Access DB. The below code took around 9 minutes to execute and does insert 4 records of two columns into a table. My requirement is to import 70 columns of 49000 records for each month which is taking me more than 48 minutes. If this is because of access, suggest any other database that I can go with. Thanks a ton in advance!
import pyodbc
import time
start_time = time.time()
#Reading the Workdayfile
exFile = r'C:\Users\U6066496\Desktop\Automations\Python-Access\Input_Files\Workday_Report_DummyNumbers.xlsx'
connEx = pyodbc.connect('DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ='+exFile,autocommit=True)
cursEx = connEx.cursor()
print("Reading data from excel...")
recEx_cnt = cursEx.execute("select count(*) from [Workday Report_DummyNumbers$]").fetchall()
print("Number of records", recEx_cnt)
#Inserting into DB
DBFile = r'C:\Users\U6066496\Desktop\Automations\Python-Access\New\Database.accdb'
connstr = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=C:\Users\U6066496\Desktop\Automations\Python-Access\New\Database.accdb;'
)
print("Connection being establised to Access database...")
connDB = pyodbc.connect(connstr)
curDB = connDB.cursor()
print("Cleaning the table...")
curDB.execute('Delete * from [Workday Report_DummyNumbers]');
print("Table has been cleaned...")
curDB.executemany('INSERT INTO [Workday Report_DummyNumbers] ([Employee ID],[Preferred Name],[Business Title],[Cost Center],[Company Code],[Company],[Worker Type],[Manager ID],[Manager Name],[HR Contact ID],[HR Contact Name],[Employee Status],[Leave Type],[First Day of Leave],[Termination Sub Category],[Time Type],[Employee Type],[Compensation Grade],[Hire Date],[Rehire Date],[Termination Date],[Base Pay Amount],[Target Percent],[Bonus Plan],[Base Pay Currency],[Compensation Plan Type],[Bonus Plan Effective Date],[Total Base Pay - Frequency]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', excel_results)
curDB.commit();
recDB_cnt = curDB.execute("select count(*) from [Workday Report_DummyNumbers]").fetchall()
print("Number of records updated in access DB", recDB_cnt)
rec_res = curDB.execute("select distinct [Bonus Plan] from [Workday Report_DummyNumbers] where [Bonus Plan] not like 'AIP%' AND [Bonus Plan] not like '%AIP' AND [Bonus Plan] not like '%AIP%' AND [Bonus Plan] not like '%EIP%' AND [Bonus Plan] not like 'EIP%' AND [Bonus Plan] not like '%EIP'").fetchall();
empID_null_chk = ('select * from [Workday Report_DummyNumbers] where [Employee ID] is null')
emp_stat_chk = ('select [Employee ID] from [Workday Report_DummyNumbers] where [Employee Status] is null')
emp_pln_chk = ('select [Employee ID] from [Workday Report_DummyNumbers] where [Bonus Plan] is null')
emp_coc_chk = ('select [Employee ID] from [Workday Report_DummyNumbers] where [Company Code] is null')
emp_cc_chk = ('select [Employee ID] from [Workday Report_DummyNumbers] where [Cost Center] is null')
emp_ID_null = curDB.execute(empID_null_chk).fetchall();
emp_stat_null = curDB.execute(emp_stat_chk).fetchall();
emp_pln_null = curDB.execute(emp_pln_chk).fetchall();
emp_coc_null = curDB.execute(emp_coc_chk).fetchall();
emp_cc_null = curDB.execute(emp_cc_chk).fetchall();
if emp_ID_null != []:
print("Few Employee ID's are missing! Please check the file once and rerun the programme...")
print("Please Correct the file and re-run the program...")
print(emp_stat_null)
if emp_stat_null != []:
curDB.executemany("INSERT INTO [Workday Report_DummyNumbers_Errors] ([Employee ID],Comments) values (?, 'Emp_Status')", emp_stat_null)
print("Records inserted for missing Employee Statuses")
print(emp_pln_null)
if emp_pln_null != []:
curDB.executemany("INSERT INTO [Workday Report_DummyNumbers_Errors] ([Employee ID],Comments) values (?, 'Plan')", emp_pln_null)
print("Records inserted for missing Employee Bonus Plans")
curDB.commit();
print(emp_coc_null)
if emp_coc_null != []:
curDB.executemany("INSERT INTO [Workday Report_DummyNumbers_Errors] ([Employee ID],Comments) values (?, 'Company Code')", emp_coc_null)
print("Records inserted for missing Company Codes")
curDB.commit();
print(emp_cc_null)
if emp_cc_null != []:
curDB.executemany("INSERT INTO [Workday Report_DummyNumbers_Errors] ([Employee ID],Comments) values (?, 'Cost Center')", emp_cc_null)
print("Records inserted for missing Employee Cost Centers")
curDB.commit();
print("--- %s seconds ---" % (time.time() - start_time))
#Closing the connections
cursEx.close
curDB.close
print ("Done")