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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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)