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

[ganeshsai2912]

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..?

[buran]

can you show how exactly a txt file looks like?
also, don't use str as variable name

[ganeshsai2912]

Sample File

Test File attached.

[buran]

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 DB

to test the parse_line function

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 	
Emp Designation: Manager 	
No. of Projects handled:	10


"""

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

Output:
['101', 'RASUL K', '30000']

[ganeshsai2912]

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..

[buran]

sorry, I have fixed this, it should be process_file, not parse

[ganeshsai2912]

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)

[buran]

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/con...erver-2017
https://github.com/mkleehammer/pyodbc/wiki
https://docs.microsoft.com/en-us/sql/con...erver-2017
https://docs.sqlalchemy.org/en/latest/di...mssql.html

[buran]

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 DB

add 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

[ganeshsai2912]

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)