Python Forum
How do i read particular text from text file and update those values in MS SQL table
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How do i read particular text from text file and update those values in MS SQL table
#11
what the line 7 looks like (the output of the print)
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#12
It's working.

Need some sql insert help (output values)..

which value i want insert in SQL...
Reply
#13
you have some decisions to make
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#14
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)
Reply
#15
(Nov-15-2018, 02:35 PM)ganeshsai2912 Wrote: but, it is showing error.
post the full traceback in error tags
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#16
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')
Reply
#17
you should use ?, not %s.
Also note about cursor.commit() and execute/executemany options

https://github.com/mkleehammer/pyodbc/wiki/Cursor
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#18
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')
Reply
#19
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#20
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))
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  subprocess check_output text cut off Axel_Erfurt 5 860 Feb-20-2025, 02:15 PM
Last Post: DeaD_EyE
  How can I write formatted (i.e. bold, italic, change font size, etc.) text to a file? JohnJSal 12 28,408 Feb-13-2025, 04:48 AM
Last Post: tomhansky
  Python - Hidden Text / Html Mail python1337 1 3,044 Feb-08-2025, 10:47 AM
Last Post: python1337
  Problems writing a large text file in python Vilius 4 1,086 Dec-21-2024, 09:20 AM
Last Post: Pedroski55
  How to read a file as binary or hex "string" so that I can do regex search? tatahuft 3 1,247 Dec-19-2024, 11:57 AM
Last Post: snippsat
  Get an FFMpeg pass to subprocess.PIPE to treat list as text file? haihal 2 1,107 Nov-21-2024, 11:48 PM
Last Post: haihal
  parsing a tree of text first the right most aligned blocks of text and so on arvindikchari 2 838 Nov-21-2024, 01:42 AM
Last Post: BashBedlam
  Paste text with caret already positioned inside a placeholder; Wehaveall 1 882 Oct-15-2024, 10:28 AM
Last Post: menator01
Photo image generation with text style Belialhun 0 693 Oct-08-2024, 01:53 PM
Last Post: Belialhun
  Read TXT file in Pandas and save to Parquet zinho 2 1,343 Sep-15-2024, 06:14 PM
Last Post: zinho

Forum Jump:

User Panel Messages

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