+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: How do i read particular text from text file and update those values in MS SQL table (/thread-14119.html)
How do i read particular text from text file and update those values in MS SQL table - ganeshsai2912 - Nov-15-2018
I have multiple text file in my folder.
Every text file contains some data (like empno,name,salary).
Ex : empno:101 name:Rasul
SAL:30000
Like above i have more than 1000 rows in every text file.
I want to fetch the above data in every file and update in SQL table.
my MS-SQL Table column has empno|name|SAL.
Finally i want to fetch all the data from all the text file and update into corresponding table.
I am struggling to read specific text in below code.
import sys
import glob
import errno
path = 'C:/Test/Python/Mag/*.txt'
files = glob.glob(path)
for name in files: # 'file' is a builtin type, 'name' is a less-ambiguous variable name.
try:
with open(name) as f: # No need to specify 'r': this is the default.
#sys.stdout.write(f.read())
str = f.readline()
except IOError as exc:
if exc.errno != errno.EISDIR: # Do not fail if a directory is found, just ignore it.
raise # Propagate other kinds of IOError.output in SQL table as follow.empno|name|SAL
---------------
101 |Rasul|30000
102 |Raj |50000
------
------
------
can anyone help how to proceed further..?
RE: How do i read particular text from text file and update those values in MS SQL table - buran - Nov-15-2018
can you show how exactly a txt file looks like?
also, don't use
str as variable name
RE: How do i read particular text from text file and update those values in MS SQL table - ganeshsai2912 - Nov-15-2018
Sample File
Test File attached.
RE: How do i read particular text from text file and update those values in MS SQL table - buran - Nov-15-2018
something like
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)
pattern = 'C:/Test/Python/Mag/*.txt'
files = glob.glob(pattern)
for file_name in files: # 'file' is a builtin type, 'name' is a less-ambiguous variable name.
emp_no, emp_name, emp_sal = process_file(file_name)
# here add code to write to DBto test the parse_line functiondef 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
Emp Designation: Manager
No. of Projects handled: 10
"""
line = my_file.split('\n')[6]
print(parse_line(line))Output:['101', 'RASUL K', '30000']RE: How do i read particular text from text file and update those values in MS SQL table - ganeshsai2912 - Nov-15-2018
Thanks dear.
getting error.
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-22-6fbff8d2580a> in <module>()
15 files = glob.glob(pattern)
16 for file_name in files: # 'file' is a builtin type, 'name' is a less-ambiguous variable name.
---> 17 emp_no, emp_name, emp_sal = parse(file_name)
18 # here add code to write to DB
NameError: name 'parse' is not defined
I am new to python and connecting MS SQL.
can you pls. provide the steps to insert into SQL table those final outputs..
RE: How do i read particular text from text file and update those values in MS SQL table - buran - Nov-15-2018
sorry, I have fixed this, it should be
process_file, not parse
RE: How do i read particular text from text file and update those values in MS SQL table - ganeshsai2912 - Nov-15-2018
still following error coming.
---------------------------------------------------------------------------
ValueError Traceback (most recent call last)
<ipython-input-26-b254d21fe8a0> in <module>()
16 files = glob.glob(pattern)
17 for file_name in files: # 'file' is a builtin type, 'name' is a less-ambiguous variable name.
---> 18 emp_no, emp_name, emp_sal = process_file(file_name)
19 # here add code to write to DB
ValueError: too many values to unpack (expected 3)
RE: How do i read particular text from text file and update those values in MS SQL table - buran - Nov-15-2018
as to the sql - you will need to put some effort your self
choose what you will use, e.g. pymssql, pyodbc or something like sqlalchemy
some docs for reading
https://docs.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server?view=sql-server-2017
https://github.com/mkleehammer/pyodbc/wiki
https://docs.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server?view=sql-server-2017
https://docs.sqlalchemy.org/en/latest/dialects/mssql.html
RE: How do i read particular text from text file and update those values in MS SQL table - buran - Nov-15-2018
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]
print(line)
return parse_line(line)
pattern = 'C:/Test/Python/Mag/*.txt'
files = glob.glob(pattern)
for file_name in files: # 'file' is a builtin type, 'name' is a less-ambiguous variable name.
emp_no, emp_name, emp_sal = process_file(file_name)
# here add code to write to DBadd a print as line 11 in process_file, this way you will see what the line 7 is. I guess you have other files that do not fit the format provided
RE: How do i read particular text from text file and update those values in MS SQL table - ganeshsai2912 - Nov-15-2018
Thanks..
Output is coming with the following error.
Also, it will not read all the text file which contains in the folder. it will read only one file.
---------------------------------------------------------------------------
ValueError Traceback (most recent call last)
<ipython-input-26-b254d21fe8a0> in <module>()
16 files = glob.glob(pattern)
17 for file_name in files: # 'file' is a builtin type, 'name' is a less-ambiguous variable name.
---> 18 emp_no, emp_name, emp_sal = process_file(file_name)
19 # here add code to write to DB
ValueError: too many values to unpack (expected 3)