Extract Text Table From File

H

Huso

Hi,

I am trying to extract some text table data from a log file. I am trying different methods, but I don't seem to get anything to work. I am kind of new to python as well. Hence, appreciate if someone could help me out.

Below is just ONE block of the traffic i have in the log files. There will be more in them with different data.

ROUTES TRAFFIC RESULTS, LSR
TRG MP DATE TIME
37 17 120824 0000

R TRAFF NBIDS CCONG NDV ANBLO MHTIME NBANSW
AABBCCO 6.4 204 0.0 115 1.0 113.4 144
AABBCCI 3.0 293 115 1.0 37.0 171
DDEEFFO 0.2 5 0.0 59 0.0 107.6 3
EEFFEEI 0.0 0 59 0.0 0.0 0
HHGGFFO 0.0 0 0.0 30 0.0 0.0 0
HHGGFFI 0.3 15 30 0.0 62.2 4
END

Thanks
 
L

Laszlo Nagy

Hi,

I am trying to extract some text table data from a log file. I am trying different methods, but I don't seem to get anything to work. I am kind of new to python as well. Hence, appreciate if someone could help me out.
Click to expand...

#
# Write test data to test.txt
#

data = """
ROUTES TRAFFIC RESULTS, LSR
TRG MP DATE TIME
37 17 120824 0000

R TRAFF NBIDS CCONG NDV ANBLO MHTIME NBANSW
AABBCCO 6.4 204 0.0 115 1.0 113.4 144
AABBCCI 3.0 293 115 1.0 37.0 171
DDEEFFO 0.2 5 0.0 59 0.0 107.6 3
EEFFEEI 0.0 0 59 0.0 0.0 0
HHGGFFO 0.0 0 0.0 30 0.0 0.0 0
HHGGFFI 0.3 15 30 0.0 62.2 4
END
"""
fout = open("test.txt","wb+")
fout.write(data)
fout.close()

#
# This is how you iterate over a file and process its lines
#
fin = open("test.txt","r")
for line in fin:
# This is one possible way to extract values.
values = line.strip().split()
print values


This will print:

[]
['ROUTES', 'TRAFFIC', 'RESULTS,', 'LSR']
['TRG', 'MP', 'DATE', 'TIME']
['37', '17', '120824', '0000']
[]
['R', 'TRAFF', 'NBIDS', 'CCONG', 'NDV', 'ANBLO', 'MHTIME', 'NBANSW']
['AABBCCO', '6.4', '204', '0.0', '115', '1.0', '113.4', '144']
['AABBCCI', '3.0', '293', '115', '1.0', '37.0', '171']
['DDEEFFO', '0.2', '5', '0.0', '59', '0.0', '107.6', '3']
['EEFFEEI', '0.0', '0', '59', '0.0', '0.0', '0']
['HHGGFFO', '0.0', '0', '0.0', '30', '0.0', '0.0', '0']
['HHGGFFI', '0.3', '15', '30', '0.0', '62.2', '4']
['END']


The "values" list in the last line contains these values. This will work
only if you don't have spaces in your values. Otherwise you can use
regular expressions to parse a line. See here:

http://docs.python.org/library/re.html

Since you did not give any specification on your file format, it would
be hard to give a concrete program that parses your file(s)

Best,

Laszlo
 
H

Huso

Hi,

I am trying to extract some text table data from a log file. I am trying different methods, but I don't seem to get anything to work. I am kind of new to python as well. Hence, appreciate if someone could help me out.
Click to expand...



#

# Write test data to test.txt

#



data = """

ROUTES TRAFFIC RESULTS, LSR

TRG MP DATE TIME

37 17 120824 0000



R TRAFF NBIDS CCONG NDV ANBLO MHTIME NBANSW

AABBCCO 6.4 204 0.0 115 1.0 113.4 144

AABBCCI 3.0 293 115 1.0 37.0 171

DDEEFFO 0.2 5 0.0 59 0.0 107.6 3

EEFFEEI 0.0 0 59 0.0 0.0 0

HHGGFFO 0.0 0 0.0 30 0.0 0.0 0

HHGGFFI 0.3 15 30 0.0 62.2 4

END

"""

fout = open("test.txt","wb+")

fout.write(data)

fout.close()



#

# This is how you iterate over a file and process its lines

#

fin = open("test.txt","r")

for line in fin:

# This is one possible way to extract values.

values = line.strip().split()

print values





This will print:



[]

['ROUTES', 'TRAFFIC', 'RESULTS,', 'LSR']

['TRG', 'MP', 'DATE', 'TIME']

['37', '17', '120824', '0000']

[]

['R', 'TRAFF', 'NBIDS', 'CCONG', 'NDV', 'ANBLO', 'MHTIME', 'NBANSW']

['AABBCCO', '6.4', '204', '0.0', '115', '1.0', '113.4', '144']

['AABBCCI', '3.0', '293', '115', '1.0', '37.0', '171']

['DDEEFFO', '0.2', '5', '0.0', '59', '0.0', '107.6', '3']

['EEFFEEI', '0.0', '0', '59', '0.0', '0.0', '0']

['HHGGFFO', '0.0', '0', '0.0', '30', '0.0', '0.0', '0']

['HHGGFFI', '0.3', '15', '30', '0.0', '62.2', '4']

['END']





The "values" list in the last line contains these values. This will work

only if you don't have spaces in your values. Otherwise you can use

regular expressions to parse a line. See here:



http://docs.python.org/library/re.html



Since you did not give any specification on your file format, it would

be hard to give a concrete program that parses your file(s)



Best,



Laszlo
Click to expand...

Hi,

Thank you for the information.
The exact way I want to extract the data is like as below.

TRG, MP and DATE and TIME is common for that certain block of traffic.
So I am using those and dumping it with the rest of the data into sql.
Table will have all headers (TRG, MP, DATE, TIME, R, TRAFF, NBIDS, CCONG, NDV, ANBLO, MHTIME, NBANSW).

So from this text, the first data will be 37, 17, 120824, 0000, AABBCCO, 6.4, 204, 0.0, 115, 1.0, 113.4, 144.

Thanking,
Huso
 
H

Huso

Hi,

I am trying to extract some text table data from a log file. I am trying different methods, but I don't seem to get anything to work. I am kind of new to python as well. Hence, appreciate if someone could help me out.
Click to expand...



#

# Write test data to test.txt

#



data = """

ROUTES TRAFFIC RESULTS, LSR

TRG MP DATE TIME

37 17 120824 0000



R TRAFF NBIDS CCONG NDV ANBLO MHTIME NBANSW

AABBCCO 6.4 204 0.0 115 1.0 113.4 144

AABBCCI 3.0 293 115 1.0 37.0 171

DDEEFFO 0.2 5 0.0 59 0.0 107.6 3

EEFFEEI 0.0 0 59 0.0 0.0 0

HHGGFFO 0.0 0 0.0 30 0.0 0.0 0

HHGGFFI 0.3 15 30 0.0 62.2 4

END

"""

fout = open("test.txt","wb+")

fout.write(data)

fout.close()



#

# This is how you iterate over a file and process its lines

#

fin = open("test.txt","r")

for line in fin:

# This is one possible way to extract values.

values = line.strip().split()

print values





This will print:



[]

['ROUTES', 'TRAFFIC', 'RESULTS,', 'LSR']

['TRG', 'MP', 'DATE', 'TIME']

['37', '17', '120824', '0000']

[]

['R', 'TRAFF', 'NBIDS', 'CCONG', 'NDV', 'ANBLO', 'MHTIME', 'NBANSW']

['AABBCCO', '6.4', '204', '0.0', '115', '1.0', '113.4', '144']

['AABBCCI', '3.0', '293', '115', '1.0', '37.0', '171']

['DDEEFFO', '0.2', '5', '0.0', '59', '0.0', '107.6', '3']

['EEFFEEI', '0.0', '0', '59', '0.0', '0.0', '0']

['HHGGFFO', '0.0', '0', '0.0', '30', '0.0', '0.0', '0']

['HHGGFFI', '0.3', '15', '30', '0.0', '62.2', '4']

['END']





The "values" list in the last line contains these values. This will work

only if you don't have spaces in your values. Otherwise you can use

regular expressions to parse a line. See here:



http://docs.python.org/library/re.html



Since you did not give any specification on your file format, it would

be hard to give a concrete program that parses your file(s)



Best,



Laszlo
Click to expand...

Hi,

Thank you for the information.
The exact way I want to extract the data is like as below.

TRG, MP and DATE and TIME is common for that certain block of traffic.
So I am using those and dumping it with the rest of the data into sql.
Table will have all headers (TRG, MP, DATE, TIME, R, TRAFF, NBIDS, CCONG, NDV, ANBLO, MHTIME, NBANSW).

So from this text, the first data will be 37, 17, 120824, 0000, AABBCCO, 6.4, 204, 0.0, 115, 1.0, 113.4, 144.

Thanking,
Huso
 
L

Laszlo Nagy

Hi,

Thank you for the information.
The exact way I want to extract the data is like as below.

TRG, MP and DATE and TIME is common for that certain block of traffic.
So I am using those and dumping it with the rest of the data into sql.
Table will have all headers (TRG, MP, DATE, TIME, R, TRAFF, NBIDS, CCONG, NDV, ANBLO, MHTIME, NBANSW).

So from this text, the first data will be 37, 17, 120824, 0000, AABBCCO, 6.4, 204, 0.0, 115, 1.0, 113.4, 144.
Click to expand...
How many blocks do you have in a file? Do you want to create different
data sets for those blocks? How do you identify those blocks? (E.g. are
they all saved into the same database table the same way?)

Anyway here is something:

import re
# AABBCCO 6.4 204 0.0 115 1.0 113.4 144
pattern = re.compile(r"""([A-Z]{7})"""+7*r"""\s+([\d\.]+)""")

#
# This is how you iterate over a file and process its lines
#
fin = open("test.txt","r")
blocks = []
block = None
for line in fin:
# This is one possible way to extract values.
values = line.strip().split()
if values==['R', 'TRAFF', 'NBIDS', 'CCONG', 'NDV', 'ANBLO',
'MHTIME', 'NBANSW']:
if block is not None:
blocks.append(block)
block = []
elif block is not None:
res = pattern.match(line.strip())
if res:
values = list(res.groups())
values[1:] = map(float,values[1:])
block.append(values)
if block is not None:
blocks.append(block)

for idx,block in enumerate(blocks):
print "BLOCK",idx
for values in block:
print values

This prints:

BLOCK 0
['AABBCCO', 6.4, 204.0, 0.0, 115.0, 1.0, 113.4, 144.0]
['DDEEFFO', 0.2, 5.0, 0.0, 59.0, 0.0, 107.6, 3.0]
['HHGGFFO', 0.0, 0.0, 0.0, 30.0, 0.0, 0.0, 0.0]
 
H

Huso

Hi,

There can be any number of blocks in the log file.
I distinguish the block by the start header 'ROUTES TRAFFIC RESULTS, LSR' and ending in 'END'. Each block will have a unique [date + time] value.

I tried the code you mentioned, it works for the data part.
But I need to get the TRG, MP, DATE and TIME for the block with those data as well. This is the part that i'm really tangled in.

Thanking,
Huso
 
H

Huso

Hi,

There can be any number of blocks in the log file.
I distinguish the block by the start header 'ROUTES TRAFFIC RESULTS, LSR' and ending in 'END'. Each block will have a unique [date + time] value.

I tried the code you mentioned, it works for the data part.
But I need to get the TRG, MP, DATE and TIME for the block with those data as well. This is the part that i'm really tangled in.

Thanking,
Huso
 
L

Laszlo Nagy

Hi,

There can be any number of blocks in the log file.
I distinguish the block by the start header 'ROUTES TRAFFIC RESULTS, LSR' and ending in 'END'. Each block will have a unique [date + time] value.

I tried the code you mentioned, it works for the data part.
But I need to get the TRG, MP, DATE and TIME for the block with those data as well. This is the part that i'm really tangled in.

Thanking,
Huso
Click to expand...
Well, I suggest that you try to understand my code and make changes in
it. It is not too hard. First you start reading documentation of the
"re" module. It is worth learning Python. Especially for mining data out
of text files. :)

Best,

Laszlo
 
T

Tim Chase

Below is just ONE block of the traffic i have in the log files. There will be more in them with different data.

ROUTES TRAFFIC RESULTS, LSR
TRG MP DATE TIME
37 17 120824 0000

R TRAFF NBIDS CCONG NDV ANBLO MHTIME NBANSW
AABBCCO 6.4 204 0.0 115 1.0 113.4 144
AABBCCI 3.0 293 115 1.0 37.0 171
DDEEFFO 0.2 5 0.0 59 0.0 107.6 3
HHGGFFI 0.3 15 30 0.0 62.2 4
END
Click to expand...

In the past I've used something like the following to find columnar
data based on some found headers:

import re
token_re = re.compile(r'\b(\w+)\s*')
f = file(FILENAME)
headers = f.next() # in your case, you'd
# search forward until
# you got to a header line
# and use that TRAFF... line
header_map = dict(
# build a map of field-name to slice
(
matchobj.group(1).upper(),
slice(*matchobj.span())
)
for matchobj
in token_re.finditer(headers)
)

You can then access your values as you iterate through the rest of
the rows:

for row in f:
if row.startswith("END"): break
traff = float(row[header_map["TRAFF"]])
# ...

which makes the code pretty easy to read, effectively turning it
into a CSV file.

It has the advantage that, if for some reason data in the columns
have spaces in them, it won't throw off the row as a .split() would.

-tkc
 
R

Ramchandra Apte

Hi,

I am trying to extract some text table data from a log file. I am trying different methods, but I don't seem to get anything to work. I am kind of new to python as well. Hence, appreciate if someone could help me out.
Click to expand...



#

# Write test data to test.txt

#



data = """

ROUTES TRAFFIC RESULTS, LSR

TRG MP DATE TIME

37 17 120824 0000



R TRAFF NBIDS CCONG NDV ANBLO MHTIME NBANSW

AABBCCO 6.4 204 0.0 115 1.0 113.4 144

AABBCCI 3.0 293 115 1.0 37.0 171

DDEEFFO 0.2 5 0.0 59 0.0 107.6 3

EEFFEEI 0.0 0 59 0.0 0.0 0

HHGGFFO 0.0 0 0.0 30 0.0 0.0 0

HHGGFFI 0.3 15 30 0.0 62.2 4

END

"""

fout = open("test.txt","wb+")

fout.write(data)

fout.close()



#

# This is how you iterate over a file and process its lines

#

fin = open("test.txt","r")

for line in fin:

# This is one possible way to extract values.

values = line.strip().split()

print values





This will print:



[]

['ROUTES', 'TRAFFIC', 'RESULTS,', 'LSR']

['TRG', 'MP', 'DATE', 'TIME']

['37', '17', '120824', '0000']

[]

['R', 'TRAFF', 'NBIDS', 'CCONG', 'NDV', 'ANBLO', 'MHTIME', 'NBANSW']

['AABBCCO', '6.4', '204', '0.0', '115', '1.0', '113.4', '144']

['AABBCCI', '3.0', '293', '115', '1.0', '37.0', '171']

['DDEEFFO', '0.2', '5', '0.0', '59', '0.0', '107.6', '3']

['EEFFEEI', '0.0', '0', '59', '0.0', '0.0', '0']

['HHGGFFO', '0.0', '0', '0.0', '30', '0.0', '0.0', '0']

['HHGGFFI', '0.3', '15', '30', '0.0', '62.2', '4']

['END']





The "values" list in the last line contains these values. This will work

only if you don't have spaces in your values. Otherwise you can use

regular expressions to parse a line. See here:



http://docs.python.org/library/re.html
Click to expand...
the csv module should be used for this not regex
 
R

Ramchandra Apte

Hi,

I am trying to extract some text table data from a log file. I am trying different methods, but I don't seem to get anything to work. I am kind of new to python as well. Hence, appreciate if someone could help me out.
Click to expand...



#

# Write test data to test.txt

#



data = """

ROUTES TRAFFIC RESULTS, LSR

TRG MP DATE TIME

37 17 120824 0000



R TRAFF NBIDS CCONG NDV ANBLO MHTIME NBANSW

AABBCCO 6.4 204 0.0 115 1.0 113.4 144

AABBCCI 3.0 293 115 1.0 37.0 171

DDEEFFO 0.2 5 0.0 59 0.0 107.6 3

EEFFEEI 0.0 0 59 0.0 0.0 0

HHGGFFO 0.0 0 0.0 30 0.0 0.0 0

HHGGFFI 0.3 15 30 0.0 62.2 4

END

"""

fout = open("test.txt","wb+")

fout.write(data)

fout.close()



#

# This is how you iterate over a file and process its lines

#

fin = open("test.txt","r")

for line in fin:

# This is one possible way to extract values.

values = line.strip().split()

print values





This will print:



[]

['ROUTES', 'TRAFFIC', 'RESULTS,', 'LSR']

['TRG', 'MP', 'DATE', 'TIME']

['37', '17', '120824', '0000']

[]

['R', 'TRAFF', 'NBIDS', 'CCONG', 'NDV', 'ANBLO', 'MHTIME', 'NBANSW']

['AABBCCO', '6.4', '204', '0.0', '115', '1.0', '113.4', '144']

['AABBCCI', '3.0', '293', '115', '1.0', '37.0', '171']

['DDEEFFO', '0.2', '5', '0.0', '59', '0.0', '107.6', '3']

['EEFFEEI', '0.0', '0', '59', '0.0', '0.0', '0']

['HHGGFFO', '0.0', '0', '0.0', '30', '0.0', '0.0', '0']

['HHGGFFI', '0.3', '15', '30', '0.0', '62.2', '4']

['END']





The "values" list in the last line contains these values. This will work

only if you don't have spaces in your values. Otherwise you can use

regular expressions to parse a line. See here:



http://docs.python.org/library/re.html
Click to expand...
the csv module should be used for this not regex
 
T

Tim Chase

[trimming out a bunch of superfluous text so the thread is actually
readable]

the csv module should be used for this not regex
Click to expand...

The problem is that the csv module expects a single delimiter
character, not columnar data.

-tkc
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract Text Format Table Data 0
writing on file not until the end 8
Text processing 29
atan2 weirdness 3
Please critique my solution: rendering HTML table using sparse data 3
How can I make a better program from the following one 1
BC30451: Name 'Ctest' is not declared 0
Help with Dll 0

Members online

No members online now.
Total: 29 (members: 0, guests: 29)
Robots: 433

Forum statistics

Threads
473,770
Messages
2,569,583
Members
45,073
Latest member
DarinCeden

Latest Threads

Top