i have a xlsx file that it's "C" Column contains barcodes of things and i wanna to insert the barcodes to count them (number of each barcode will put in corresponding cell in "G" column) the main issue is : i want to receive a message that shown "Barcode not found" when the inserted barcode doesn't exist in any cells of column "C" here is a link of the file i wanna work on it :

and the code is :

but i will give the output : "Barcode not found" when i insert barcodes (for those which exist in "C" column or others Which aren,t). i like to get "Barcode not found" when the inserted barcode doesn't exist in C column and continue asking for input

but i have this error when i insert a barcode that i'm sure exist :

any suggestions?

CodePudding user response：

I think this will work:

from numpy import int64 # handle large integers
import pandas as pd
from io import StringIO
import time


# df = pd.read_csv(StringIO(inp),sep=';')
df = pd.read_excel(r'C:\Users\dcg601\Downloads\test2.xlsx', header=None)
# df[2] = df[2].astype('int') # need to do that to enable comparisons
# df.iloc[:2]
df.iloc[:,6] = df.iloc[:,6].fillna(0)
df.iloc[:,2] = df.iloc[:,2].astype('str')
df.iloc[:,2] = df.iloc[:,2].str.strip('\u202a') # strips the right to left character
df.iloc[:,2] = df.iloc[:,2].astype('int64') # necessary cause numbers are too large

n = input("SCAN Barcode : ")
n = int64(n)
while n != 0 :
    if(n in df[2].values):
        df.loc[df[2]==int64(n),6] =1
        df.to_excel(r'C:\Users\dcg601\Downloads\test2.xlsx',index=False,header=False)
    else :
        print("Barcode not found")
        time.sleep(5)
    print()
    n = input("SCAN Barcode : ")
    n = int64(n)

Notes:

1. df[2] = df[2].astype('int') otherwise comparisons fail. pandas default values are strings if I am not wrong,
2. After inputting you should cast ton integers with n = int(n), otherwise n from the input is string,
3. The if condition should check n in df[column].values and not just n in df[column].

Edit: added some ilocs but theyr probably not much necessary, made some strips and conversions to and from str