I have files names metadata.txt which contains metadata for all xlsx files with sheetnames and col header information I need to do some validations comparing metadata.txt with xlsx files and throw out exception.(validations are provided below) I have around 30 xlsx with different sheets (i have provided example for few files ) I m new to python looking for suggestion /sample code on how it can be achieved .
Validatons :
Check metadata.txt and compare with emp.xlsx , dept.xlsx,locations.xlsx
(basically i need to loop filenames and sheetnames from metadata.txt with
directory path C://Files)
if there is mismatch in header(ie Col_header of metadata with header of
xlsx(example: dept.xlsx(description not matching with dept_name) )
then throw error
If there is duplicates found with column header
(ex:locations.xlsx(loc_name repeated twice when it is compared with
metadata.txt) throw error
filename:sheet_name:col_header
emp.xlsx:emp_details:emp_id,sal,dept_id,hiredate
dept.xlsx:dept_details:dept_id,dept_name,created_date
locations.xlsx:loc_details:loc_id,loc_name,created_date
emp_id,sal,dept_id,hiredate
1,2000,10,10-jan-2018
2,4000,20,12-jan-2018
3,5000,30,13-jan-2018
dept_id,description,created_date
10,HR,10-apr-2018
20,IT,20-may-2018
30,MED,12-jun-2018
loc_id,loc_name,created_date,loc_name
100,BAN,10-jan-17,BAN
200,CHE,20-jan-17,CHE
Print my results would be into new file
File_name,count,systemdate,validationstatus
emp.xlsx,3,27-jan-19,succcess
dept.xlsx,3,27-jan-19,failed
locations.xlsx,3,27-jan-19,failed
There are many solutions to this problem. Below is one of them. From your example I see that the tab names do not really matter, so I did not include them in the analysis. And extra spaces may cause problems also. Here I am cleaning them as well. Enjoy. Please let me know if anything is not clear. Hope it helps:
import pandas as pd
from os import chdir
from datetime import datetime
# point to the folder where the data is
chdir(".\\Data")
emp = pd.read_excel("emp.xlsx")
dept = pd.read_excel("dept.xlsx")
locations = pd.read_excel("locations.xlsx")
# build a dictionary where keys are the file names and values are a list of columns names and number of rows
dictDFColNames = {
"emp.xlsx": [list(emp.columns), emp.shape[0]],
"dept.xlsx": [list(dept.columns), dept.shape[0]],
"locations.xlsx": [list(locations.columns), locations.shape[0]]
}
dictDFColNames = {k:[[c.strip() for c in v[0]], v[1]] for k,v in dictDFColNames.items()}
# dictionary of the metadata
dictColNames = dict()
with open("metadata.txt") as f:
next(f)
for line in f:
line = line.strip()
key = line.split(":")[0]
values = line.split(":")[-1].split(",")
values = [c.strip() for c in values]
dictColNames[key] = values
f = open("validation.csv", "w")
header = "File_name,count,systemdate,validationstatus\n"
f.write(header)
for k, v in dictDFColNames.items():
s = ""
col_names = [x.split(".")[0] for x in v[0]]
s_failed = k + "," + str(v[1]) + "," + datetime.today().strftime('%Y-%m-%d') + ",failed\n"
s_success = k + "," + str(v[1]) + "," + datetime.today().strftime('%Y-%m-%d') + ",success\n"
if len(col_names) > len(set(col_names)):
s = s_failed
else:
if set(dictDFColNames[k][0]) == set(dictColNames[k]):
s = s_success
else:
s = s_failed
f.write(s)
f.close()