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

[buran]

what the line 7 looks like (the output of the print)

[ganeshsai2912]

It's working.

Need some sql insert help (output values)..

which value i want insert in SQL...

[buran]

you have some decisions to make

[ganeshsai2912]

I tried the below code..

but, it is showing error.

what is the problem in the below code..

cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cur = cnxn.cursor()
sql = "INSERT INTO test1 (EmpNo, Name, SAL) VALUES (%s, %s, %s)"
val = (Staff_no, Emp_name, emp_dept)
res = cur.execute(sql, val)

[buran]

but, it is showing error.

post the full traceback in error tags

[ganeshsai2912]

sorry. error is below.

---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
<ipython-input-49-e00dcd4f8f95> in <module>()
27 sql = "INSERT INTO test1 (Staffno, EmpName, Department) VALUES (%s, %s, %s)"
28 val = (Staff_no, Emp_name, emp_dept)
---> 29 res = cur.execute(sql, val)
30 cnxn.commit()

ProgrammingError: ('The SQL contains 0 parameter markers, but 3 parameters were supplied', 'HY000')

[buran]

you should use ?, not %s.
Also note about cursor.commit() and execute/executemany options

https://github.com/mkleehammer/pyodbc/wiki/Cursor

[ganeshsai2912]

Yes. i used executemany. below i the error

params = (Staff_no, Emp_name, emp_dept)
#res = cursor.executemany(sql, val)
res = cursor.executemany("insert into test1(Staffno, EmpName, Department) values (?, ?, ?)", params)
cursor.commit()

---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<ipython-input-54-06ea60fdf959> in <module>()
28 params = (Staff_no, Emp_name, emp_dept)
29 #res = cursor.executemany(sql, val)
---> 30 res = cursor.executemany("insert into test1(Staffno, EmpName, Department) values (?, ?, ?)", params)
31 cursor.commit()

TypeError: ('Params must be in a list, tuple, or Row', 'HY000')

[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

[ganeshsai2912]

Thank You.

Is it possible to read one more item fro the text file..

currently i am using empno, name, sal.

along with this, i need to add two more parameter (relieve date,EmpDesignation,No.ofPrjhandled).

I thing below line is reading particular text from the text fille.
line = my_file.split('\n')[6]

if i want another parameter in another line how do we specify..?


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()]

my_file = """Please find below emp details,

EMP DETAILS
________________________________

Emp No: 101 Name:RASUL K SAL: 30000
________________________________

Join Date: 10 JAN 2010 From Native: First
Releave Date: 25 APR 2018 To Native: Second
EmpDesignation: Manager
No.ofProjects handled: 10


"""

line = my_file.split('\n')[6]
print(parse_line(line))

---

Is it possible to read one more item fro the text file..

currently i am using empno, name, sal.

along with this, i need to add two more parameter (relieve date,EmpDesignation,No.ofPrjhandled).

I thing below line is reading particular text from the text fille.

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()]

my_file = """Please find below emp details, 

EMP DETAILS 
________________________________

Emp No: 101 Name:RASUL K SAL: 30000 
________________________________

Join Date: 10 JAN 2010 From Native: First 
Releave Date: 25 APR 2018 To Native: Second 
EmpDesignation: Manager 
No.ofProjects handled: 10


"""

line = my_file.split('\n')[6]
print(parse_line(line))