How do i read particular text from text file and update those values in MS SQL table

[buran]

plase, start using error tags, when post traceback.
in the above code you don't have many employees data, you pass only one employee data, so you cannot use execute many (or more correct - it doesn't make sense to use it.
you either parse all files and collect all data e.g. in list of lists or process/submit one file at a time.
the advantage of process all files and then submit is that you will insert all data or none. if you process one file at a time and you get error somewhere in the middle, then you will have to keep track what is already inserted and what not.
something like this (NOT TESTED)

import glob

def parse_line(line):
    for item in ['Emp No', 'Name', 'SAL']:
        line = line.replace(item, '')
    return [item.strip() for item in line.split(':') if item.strip()]

def process_file(file_name):
    with open(file_name) as f: # No need to specify 'r': this is the default.
        line = f.readlines()[6]
    return parse_line(line)

def write_db(data, server, database, password):
    connection_string = f'DRIVER=\{SQL Server Native Client 11.0\};SERVER={server};DATABASE={database};UID={username};PWD={password}'
    with pyodbc.connect(connection_string, autocommit=False) as cnxn:
        cur = cnxn.cursor()
        sql = "INSERT INTO test1 (EmpNo, Name, SAL) VALUES (?, ?, ?)"
        cur.executemany(sql, data)
        

pattern = 'C:/Test/Python/Mag/*.txt'   
files = glob.glob(pattern)
all_data = [parse(file_name) for file_name in files]
write_db(data=all_data, server=server, database=database, password=password) # replace srever, database, password with actual values