Python Forum
Python validate excel values data types - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Python validate excel values data types (/thread-17355.html)

Python validate excel values data types - Useruser00 - Apr-08-2019

I am using Pandas to read excel file and validate the columns for Integer, Date and check column value is present in List.
xl = pd.ExcelFile(os.path.join(os.getcwd() , "Inputfile.xlsx"))
df = xl.parse("Sheet1")
listvalues = ['list1','list2','list3']
df[errorcolumn] = df[errorcolumn] + np.where(df[listcolumn].isin(listvalues), "", "list value not valid")
df[errorcolumn] = df[errorcolumn] + np.where(df[intcolumn].dtype.kind in 'i', " int value not valid, ")
writer = pd.ExcelWriter(os.path.join(os.getcwd(), "Inputfile.xlsx"), engine='xlsxwriter')
self.df.to_excel(writer, sheet_name='Sheet1', header=True, index=False)
writer.save()
writer.close()
print("Please fix the fields mentioned in the Error column of input file to proceed")
sys.exit(1)
Input excel:
Output:
ID  listcolumn  intcolumn   errorcolumn
1   list1       5           ""
2   list22      5.6         ""
3   list3       6.25        ""
Using the below code to validate only the values from list are present in the column and append the error message for the rows which have invalid values,

df[errorcolumn] = df[errorcolumn] + np.where(df[listcolumn].isin(listvalues), "", "list value not valid")
For integer only validation i used the below code which adds error message to rows when one row invalid row is found,

df[errorcolumn] = df[errorcolumn] + np.where(df[intcolumn].dtype.kind in 'i', " int value not valid, ")
Current Output excel:
Output:
ID  listcolumn  intcolumn   errorcolumn
1   list1       5           "int value not valid, "
2   list22      5.6         "list value not valid, int value not valid,"
3   list3       6.25        "int value not valid,"
Output:
Expected Output excel:

ID  listcolumn  intcolumn   errorcolumn
1   list1       5           ""
2   list22      5.6         "list value not valid, int value not valid,"
3   list3       6.25        "int value not valid,"