I am trying to create an data import button to attendance list
and i want to write python script that processing data from excel file like this
to data import list in erpnext
anyone know how to do this?
I am trying to create an data import button to attendance list
add the button, and customize the below python code as per your requirement
# removing empty employee
import pandas as pd
# Read the original Excel file
df = pd.read_excel('/home/rajat/Desktop/feb attendance myginne egp/Att. data 26 Jul - 6 Aug.xlsx')
# Select relevant columns
new_df = df[['AC-No.', 'Clock In', 'Clock Out','Date']]
# Convert the date format to Day/Month/Year
# new_df['Date'] = '11/03/2024'
# new_df['Date'] = pd.to_datetime(new_df['Date'], format='%Y/%m/%d').dt.strftime('%d/%m/%Y')
new_df['Date'] = pd.to_datetime(new_df['Date']).dt.strftime('%d/%m/%Y')
# Employee ID dictionary
employee_id_dict = {'3': 'HR-EMP-00071', '4': 'HR-EMP-00035', '7': 'HR-EMP-00042', '9':'HR-EMP-00050', '12': 'HR-EMP-00020',
'14': 'HR-EMP-00011', '16':'HR-EMP-00049', '17': 'HR-EMP-00061', '18': 'HR-EMP-00084', '19': 'HR-EMP-00019', '20': 'HR-EMP-00058',
'21': 'HR-EMP-00070', '22':'HR-EMP-00010', '23': 'HR-EMP-00009', '24': 'HR-EMP-00034', '25': 'HR-EMP-00032', '26': 'HR-EMP-00026',
'28': 'EMP-00054', '80': 'HR-EMP-00017', '103': 'HR-EMP-00039', '104': 'HR-EMP-00040', '150': 'HR-EMP-00036', '1001': 'HR-EMP-00018',
'1004': 'HR-EMP-00016', '10001': 'HR-EMP-00053', '100001': 'HR-EMP-00103', '100002': 'HR-EMP-00048', '150002': 'HR-EMP-00001', '150042': 'HR-EMP-00056',
'150080': 'HR-EMP-00046', '151929': 'HR-EMP-00106', '151953': 'HR-EMP-00051', '151979': 'HR-EMP-00045', '100105':'HR-EMP-00069','100051':'HR-EMP-00041',
'100101':'HR-EMP-00062', '100103':'HR-EMP-00065', '100104':'HR-EMP-00066', '100100':'HR-EMP-00057', '79':'HR-EMP-00079', '81':'HR-EMP-00081', '83':'HR-EMP-00082',
'100051':'HR-EMP-00041', '83':'HR-EMP-00083', '85':'HR-EMP-00085', '150003':'HR-EMP-00059', '73':'HR-EMP-00088', '98':'HR-EMP-00098',
'99':'HR-EMP-00099'}
# Create an empty DataFrame for the log entries
log_entries_df = pd.DataFrame(columns=['Employee', 'Device ID', 'time', 'Log Type'])
# Iterate through rows and extract log entries
for index, row in new_df.iterrows():
employee_id = employee_id_dict.get(str(row['AC-No.']), '') # Get employee ID from dictionary
new_clock_in = row['Clock In']
new_clock_out = row['Clock Out']
if pd.isna(new_clock_in) and pd.isna(new_clock_out):
continue
if new_clock_in != 0 and pd.isna(new_clock_out):
new_clock_out = '18:00'
if pd.isna(new_clock_in) and new_clock_out != 0:
new_clock_in = '09:30'
# Check if both "New Clock In" and "New Clock Out" are not 0
if new_clock_in != 0 and new_clock_out != 0:
log_entries_df.loc[len(log_entries_df)] = [employee_id, row['AC-No.'], f"{row['Date']} {new_clock_in}:00", 'IN']
log_entries_df.loc[len(log_entries_df)] = [employee_id, row['AC-No.'], f"{row['Date']} {new_clock_out}:00", 'OUT']
# Filter out rows where the "Employee" field is empty
log_entries_df = log_entries_df[log_entries_df['Employee'] != '']
# Write the new DataFrame to a new Excel file
log_entries_df.to_excel('attendance_date_26_06_august.xlsx', index=False)
i use this code to format my excel into the erpnext format, then upload it manually
maybe something helps from here
thank you very much, but do you know where to place this python code? I only know how to create button with client scirpt
Let say you have a doctype A in which you added a button through js code.
that doctype has a .py file also, you will write their
ask chatgpt it will help you further
Em chào anh ạ. Anh ơi anh đã làm được phần này chưa ạ. Em cũng đang tìm hiểu về đoạn này anh có thể cho em xin hướng dẫn với được không ạ.