Python Forum

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

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

Sample File

Test File attached.

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']

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

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

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)

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

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

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)