Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract DAta from text | Excel Discussion (Misc queries) | |||
How can i extract some text data..... | Excel Programming | |||
Extract data from an old Quattro file | Excel Programming | |||
Search/Extract Data w/in Text File | Excel Discussion (Misc queries) | |||
open text file to extract data | Excel Programming |