extract data from a text file
 

I would very much appreciate some help in extracting a value from a text
file. If the text file was imported to XL and parsed it should cover 5
columns. The data is in blocks determined by a row descriptor at the bottom
of each block. I need a value in that row that could be in any of the other
four columns, it is governed by a column heading at the top of that block
however these headings vary per block so they are not consistent. I can do
all this in XL but it is very messy using a series of loops etc.

As i have to repeat this exercise several times it struck me that it might
be less prone to error and maybe more efficient to extract this value
straight from the unparsed text file. If so i will be very grateful for some
code to do this or to be pointed in the right direction.
--
with kind regards

Spike

extract data from a text file
 

I neglected to say the text files are space separated
--
with kind regards

Spike


"Spike" wrote:

I would very much appreciate some help in extracting a value from a text
file. If the text file was imported to XL and parsed it should cover 5
columns. The data is in blocks determined by a row descriptor at the bottom
of each block. I need a value in that row that could be in any of the other
four columns, it is governed by a column heading at the top of that block
however these headings vary per block so they are not consistent. I can do
all this in XL but it is very messy using a series of loops etc.

As i have to repeat this exercise several times it struck me that it might
be less prone to error and maybe more efficient to extract this value
straight from the unparsed text file. If so i will be very grateful for some
code to do this or to be pointed in the right direction.
--
with kind regards

Spike

extract data from a text file
 

You can put in the particulars:

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

--
Regards,
Tom Ogilvy


"Spike" wrote:

I neglected to say the text files are space separated
--
with kind regards

Spike


"Spike" wrote:

I would very much appreciate some help in extracting a value from a text
file. If the text file was imported to XL and parsed it should cover 5
columns. The data is in blocks determined by a row descriptor at the bottom
of each block. I need a value in that row that could be in any of the other
four columns, it is governed by a column heading at the top of that block
however these headings vary per block so they are not consistent. I can do
all this in XL but it is very messy using a series of loops etc.

As i have to repeat this exercise several times it struck me that it might
be less prone to error and maybe more efficient to extract this value
straight from the unparsed text file. If so i will be very grateful for some
code to do this or to be pointed in the right direction.
--
with kind regards

Spike

extract data from a text file
 

Thank you very much.

I do not think i explained myself very well, what i am after is a method of
searching through a text file to find a value that is governed by a row
heading (the first value in a text row)and a word (what would be in an XL
spreadsheet,a column heading type word) that is actually some way down in the
body of the text file as opposed to being at the top.
--
with kind regards

Spike


"Tom Ogilvy" wrote:

You can put in the particulars:

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

--
Regards,
Tom Ogilvy


"Spike" wrote:

I neglected to say the text files are space separated
--
with kind regards

Spike


"Spike" wrote:

I would very much appreciate some help in extracting a value from a text
file. If the text file was imported to XL and parsed it should cover 5
columns. The data is in blocks determined by a row descriptor at the bottom
of each block. I need a value in that row that could be in any of the other
four columns, it is governed by a column heading at the top of that block
however these headings vary per block so they are not consistent. I can do
all this in XL but it is very messy using a series of loops etc.

As i have to repeat this exercise several times it struck me that it might
be less prone to error and maybe more efficient to extract this value
straight from the unparsed text file. If so i will be very grateful for some
code to do this or to be pointed in the right direction.
--
with kind regards

Spike

extract data from a text file
 

Yes, I understand. I gave you the basic information you need. LineofText
will sequentially contain each line of text in the file as it is read in.
You would then check the left "n" characters to find the row heading. when
the line is found, you would then look for whatever other information it is
you are seeking in that line of text.

Or, if you are looking for some value in each line, then search lineoftext
for that value. Then if it is found, get the left "n" characters from
lineoftext to reveal the row header where the value is found.



--
Regards,
Tom Ogilvy


"Spike" wrote:

Thank you very much.

I do not think i explained myself very well, what i am after is a method of
searching through a text file to find a value that is governed by a row
heading (the first value in a text row)and a word (what would be in an XL
spreadsheet,a column heading type word) that is actually some way down in the
body of the text file as opposed to being at the top.
--
with kind regards

Spike


"Tom Ogilvy" wrote:

You can put in the particulars:

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

--
Regards,
Tom Ogilvy


"Spike" wrote:

I neglected to say the text files are space separated
--
with kind regards

Spike


"Spike" wrote:

I would very much appreciate some help in extracting a value from a text
file. If the text file was imported to XL and parsed it should cover 5
columns. The data is in blocks determined by a row descriptor at the bottom
of each block. I need a value in that row that could be in any of the other
four columns, it is governed by a column heading at the top of that block
however these headings vary per block so they are not consistent. I can do
all this in XL but it is very messy using a series of loops etc.

As i have to repeat this exercise several times it struck me that it might
be less prone to error and maybe more efficient to extract this value
straight from the unparsed text file. If so i will be very grateful for some
code to do this or to be pointed in the right direction.
--
with kind regards

Spike

extract data from a text file
 

Thank you that is fine i missed the point sorry.

Your advices are very much appreciated

many thanks
--
with kind regards

Spike


"Tom Ogilvy" wrote:

Yes, I understand. I gave you the basic information you need. LineofText
will sequentially contain each line of text in the file as it is read in.
You would then check the left "n" characters to find the row heading. when
the line is found, you would then look for whatever other information it is
you are seeking in that line of text.

Or, if you are looking for some value in each line, then search lineoftext
for that value. Then if it is found, get the left "n" characters from
lineoftext to reveal the row header where the value is found.



--
Regards,
Tom Ogilvy


"Spike" wrote:

Thank you very much.

I do not think i explained myself very well, what i am after is a method of
searching through a text file to find a value that is governed by a row
heading (the first value in a text row)and a word (what would be in an XL
spreadsheet,a column heading type word) that is actually some way down in the
body of the text file as opposed to being at the top.
--
with kind regards

Spike


"Tom Ogilvy" wrote:

You can put in the particulars:

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

--
Regards,
Tom Ogilvy


"Spike" wrote:

I neglected to say the text files are space separated
--
with kind regards

Spike


"Spike" wrote:

I would very much appreciate some help in extracting a value from a text
file. If the text file was imported to XL and parsed it should cover 5
columns. The data is in blocks determined by a row descriptor at the bottom
of each block. I need a value in that row that could be in any of the other
four columns, it is governed by a column heading at the top of that block
however these headings vary per block so they are not consistent. I can do
all this in XL but it is very messy using a series of loops etc.

As i have to repeat this exercise several times it struck me that it might
be less prone to error and maybe more efficient to extract this value
straight from the unparsed text file. If so i will be very grateful for some
code to do this or to be pointed in the right direction.
--
with kind regards

Spike

extract data from a text file
 

Just wondering if you managed to find a solution or not. I deal with text
data files all the time, and got tired long ago of reading them line by line.
I use excel to process all of my text data.

If you still need some help, i can search through some of my files for you.


--
Jeff "Spike" Zapinski


"Spike" wrote:

Thank you that is fine i missed the point sorry.

Your advices are very much appreciated

many thanks
--
with kind regards

Spike


"Tom Ogilvy" wrote:

Yes, I understand. I gave you the basic information you need. LineofText
will sequentially contain each line of text in the file as it is read in.
You would then check the left "n" characters to find the row heading. when
the line is found, you would then look for whatever other information it is
you are seeking in that line of text.

Or, if you are looking for some value in each line, then search lineoftext
for that value. Then if it is found, get the left "n" characters from
lineoftext to reveal the row header where the value is found.



--
Regards,
Tom Ogilvy


"Spike" wrote:

Thank you very much.

I do not think i explained myself very well, what i am after is a method of
searching through a text file to find a value that is governed by a row
heading (the first value in a text row)and a word (what would be in an XL
spreadsheet,a column heading type word) that is actually some way down in the
body of the text file as opposed to being at the top.
--
with kind regards

Spike


"Tom Ogilvy" wrote:

You can put in the particulars:

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

--
Regards,
Tom Ogilvy


"Spike" wrote:

I neglected to say the text files are space separated
--
with kind regards

Spike


"Spike" wrote:

I would very much appreciate some help in extracting a value from a text
file. If the text file was imported to XL and parsed it should cover 5
columns. The data is in blocks determined by a row descriptor at the bottom
of each block. I need a value in that row that could be in any of the other
four columns, it is governed by a column heading at the top of that block
however these headings vary per block so they are not consistent. I can do
all this in XL but it is very messy using a series of loops etc.

As i have to repeat this exercise several times it struck me that it might
be less prone to error and maybe more efficient to extract this value
straight from the unparsed text file. If so i will be very grateful for some
code to do this or to be pointed in the right direction.
--
with kind regards

Spike