CODEWITHSUNDEEP
pythonpython-3.x
Iamwhoiam
Iamwhoiam

Reputation: 169

Reading .txt file for specific data and storing into sql fields

I'm In the process of trying to understand Python and need help on how I will read a text file containing massive amounts of data, take specific information that is needed, and store it into database.

*-------------------------------------------*                                                                                        
* xxxxxxxxxxxx STARTED AT 2017-06-07-21.32.43.                                                                                  
*-------------------------------------------*                                                                                        
                                                                                                                                                                                                                                                                               
******* D I S P L A Y ************                                                                                  
*                                                 *                                                                                  
*REC READ     =  56,813                                                                                                   
*REC WRITTEN  =  56,813                                                                                                   
*CALLS   =     617                                                                                                   
*FOUND   =     963                                                                                                   
*NOT FND =         54                                                                                                   
*FOUND   =     4963                                                                                                   
*NOT FND =       0                                                                                                   
*SYS   =           1                                                                                                   
*SYS   =     462                                                                                                   
*--------------------------------------------*                                                                                       
* xxxxxxxxxxxx COMPLETED AT 2017-06-07-21.35                                                                                  
*--------------------------------------------* 

    with open(fname) as f:
content = f.readlines()
content = [x.strip() for x in content]

My idea: From my understanding should I approach this by reading the .txt line by line, storing it into an array somehow, then using an if statement to test to see if the value in the indexes are true for example (Rec read, written) etc. How will I get the values next to it?... (Just an idea, this might be completely wrong)

UPDATE: Using the code below, I'm able to read all the line and get the correct information that is needed. The Keywords get stored in line[0] and the values get stored in line[1]. I am now trying to append each value into a list so I can run a query on the list and add it to the correct fields in my access database. Right now when I print a list it only shows me one value rather then the others. My original file have multiple values paired with the same data. In other words there is multiple "Rec Read".

   file = open(r"C:\Users\cqt7wny\Desktop\joblogs.txt")

rec_read = []
rec_written = []
calls = []


for line in file:
   if "REC READ" in line: #This if statement looks through the line
     line = line.split("=") #This makes the line two items in a list
     rec_read.append(line[1])

   if "REC WRITTEN" in line:
       line = line.split("=")
       rec_written.append(line[1])
   if "CC01 CALLS" in line:
       line = line.split("=")
       calls.append(line[1])

print(rec_read)

The output:

['   7,558,265

What I want:

[7558265,324322,22232,etc]

Upvotes: 0

Views: 657

Answers (1)

cosinepenguin
cosinepenguin

Reputation: 1575

You have a good theoretical approach. Here's some code to help you get started, but you will have to change it to match your needs.

To open a file with Python:

file = open('filename.txt')

To iterate through the file line by line:

for line in file:
   if "REC READ" in line: #This if statement looks through the line
     line = line.split("=") #This makes the line two items in a list
     print(line[0])

This for statement can also be made into a while loop if necessary. More information about looking through files in python can be found here.

I do not know what type of database you want to implement (there are several kinds for different purposes). A common one is postgresql, which is accessed through python by the psycopg2 driver for Python (psycopg2 install info here).

You can then start working on the database in python:

import psycopg2
conn = psycopg2.connect(database=url.path[1:],user=url.username,password=url.password,host=url.hostname,port=url.port)
cur = conn.cursor()
cur.execute("""CREATE TABLE tablename (col1, col2, col3)""")
cur.execute("""INSERT INTO tablename (col1, col2, col3) VALUES (%s, %s, %s);""", (item1, item2, item3))
conn.commit()

Hope this can help you get started. Keep trying different things, then post your questions or cool things you find along the way to stackoverflow!

Cheers!

Upvotes: 1

Related Questions