Python Forum
Python validate excel values data types
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python validate excel values data types
#1
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,"
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  What data types can I use for default values? Mark17 1 485 Oct-09-2023, 02:07 PM
Last Post: buran
  Copy data from Excel and paste into Discord (Midjourney) Joe_Wright 4 1,925 Jun-06-2023, 05:49 PM
Last Post: rajeshgk
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,046 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  How to properly format rows and columns in excel data from parsed .txt blocks jh67 7 1,802 Dec-12-2022, 08:22 PM
Last Post: jh67
  validate large json file with millions of records in batches herobpv 3 1,221 Dec-10-2022, 10:36 PM
Last Post: bowlofred
  Trying to Get Arduino sensor data over to excel using Python. eh5713 1 1,620 Dec-01-2022, 01:52 PM
Last Post: deanhystad
  Appending a row of data in an MS Excel file azizrasul 3 1,138 Nov-06-2022, 05:17 PM
Last Post: azizrasul
  Moving data from one Excel to another and finding maximum profit azizrasul 7 1,411 Oct-06-2022, 06:13 PM
Last Post: azizrasul
  Create SQL connection function and validate mg24 1 905 Sep-30-2022, 07:45 PM
Last Post: deanhystad

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020