I have a problem wich i cant seem to resolve.
Posted it before, but this is hopefully a better explanation;

Have a txt file with alot of data placed in one row (50.000 rows).
Across this row of data there is a constant value coming back called
SHEET

If imported in excel;


Example: SHEET 7K295 or SHEET 6H320 etc.

So find the string SHEET and when found copy the name
SHEET +7K295
I want to do this in a loop (the value SHEET could exist more than
100
times in that column).

And then also copy the 4th row below the string "SHEET", and the
9th,
the 14th and 19th and put those values like this in excel: (4,9,14,19
= always constant so this is the same for all data)


ColumA
SHEET 7K295
SHEET 6H320
etc

Column B
data 4th row below SHEET 7K295
data 4th row below SHEET 6H320
etc


Column C
data 9th row below SHEET 7K295
data 9th row below SHEET 6H320
etc


Column D
data 14th row below SHEET 7K295
data 14th row below SHEET 6H320
etc


Column E
data 19th row below SHEET 7K295
data 19th row below SHEET 6H320
etc

Hopefully someone can help!

I usually find problems like this arre better solved by modifying the text
file. I usually write a filter which reads one text file and write the
modified text to a second file.

In this case I would create a CSV file and then read the CSV file into excel
after the changes are made.

I would add semicolons to force excel to read data into a new column, and
add carriagge returns to have excel put the data in a new row.


"(E-Mail Removed)" wrote:

> I have a problem wich i cant seem to resolve.
> Posted it before, but this is hopefully a better explanation;
>
> Have a txt file with alot of data placed in one row (50.000 rows).
> Across this row of data there is a constant value coming back called
> SHEET
>
> If imported in excel;
>
>
> Example: SHEET 7K295 or SHEET 6H320 etc.
>
> So find the string SHEET and when found copy the name
> SHEET +7K295
> I want to do this in a loop (the value SHEET could exist more than
> 100
> times in that column).
>
> And then also copy the 4th row below the string "SHEET", and the
> 9th,
> the 14th and 19th and put those values like this in excel: (4,9,14,19
> = always constant so this is the same for all data)
>
>
> ColumA
> SHEET 7K295
> SHEET 6H320
> etc
>
> Column B
> data 4th row below SHEET 7K295
> data 4th row below SHEET 6H320
> etc
>
>
> Column C
> data 9th row below SHEET 7K295
> data 9th row below SHEET 6H320
> etc
>
>
> Column D
> data 14th row below SHEET 7K295
> data 14th row below SHEET 6H320
> etc
>
>
> Column E
> data 19th row below SHEET 7K295
> data 19th row below SHEET 6H320
> etc
>
> Hopefully someone can help!
>
>

Well the problem is that i have loads of these txt files, so
automation would save alot of time.

Isnt it possible?

Joel wrote:
> I usually find problems like this arre better solved by modifying the text
> file. I usually write a filter which reads one text file and write the
> modified text to a second file.
>
> In this case I would create a CSV file and then read the CSV file into excel
> after the changes are made.
>
> I would add semicolons to force excel to read data into a new column, and
> add carriagge returns to have excel put the data in a new row.
>
>
> "(E-Mail Removed)" wrote:
>
> > I have a problem wich i cant seem to resolve.
> > Posted it before, but this is hopefully a better explanation;
> >
> > Have a txt file with alot of data placed in one row (50.000 rows).
> > Across this row of data there is a constant value coming back called
> > SHEET
> >
> > If imported in excel;
> >
> >
> > Example: SHEET 7K295 or SHEET 6H320 etc.
> >
> > So find the string SHEET and when found copy the name
> > SHEET +7K295
> > I want to do this in a loop (the value SHEET could exist more than
> > 100
> > times in that column).
> >
> > And then also copy the 4th row below the string "SHEET", and the
> > 9th,
> > the 14th and 19th and put those values like this in excel: (4,9,14,19
> > = always constant so this is the same for all data)
> >
> >
> > ColumA
> > SHEET 7K295
> > SHEET 6H320
> > etc
> >
> > Column B
> > data 4th row below SHEET 7K295
> > data 4th row below SHEET 6H320
> > etc
> >
> >
> > Column C
> > data 9th row below SHEET 7K295
> > data 9th row below SHEET 6H320
> > etc
> >
> >
> > Column D
> > data 14th row below SHEET 7K295
> > data 14th row below SHEET 6H320
> > etc
> >
> >
> > Column E
> > data 19th row below SHEET 7K295
> > data 19th row below SHEET 6H320
> > etc
> >
> > Hopefully someone can help!
> >
> >

your problem seems to be very simple to solve, but you started a new tread
and with the same failing:
it is hard to understand what is realy your data structure.

you are talking about data in *one* row, and looking for text *under* string
SHEET
what does mean *under*, is it character position, word position, cell parsed
position ...

please post here few top lines either of your input file, or parsed cvs file
after read-in into excel, just keep in mind to anotate where lines begin/end
since line wrap while posting will destroy the layout.


<(E-Mail Removed)> je napisao u poruci interesnoj
grupi:(E-Mail Removed)...
>I have a problem wich i cant seem to resolve.
> Posted it before, but this is hopefully a better explanation;
>
> Have a txt file with alot of data placed in one row (50.000 rows).
> Across this row of data there is a constant value coming back called
> SHEET
>

Thanks so much for the helping hand;

This is what the txt file looks like:

_______________________________SHEET 7K294
0,2949.41
276.52,3104.61
TEXT
_______________________________7K294
1.8
NORMAL
271.02,32.11
TEXT
_______________________________TRANSPORTSYSTEM - A
1.5
NORMAL
193.62,32.91
TEXT
_______________________________CENTER SYSTEM
1.5
NORMAL
193.42,28.61
TEXT
_______________________________SFC STEP 4 - WAIT
1.5
NORMAL
193.42,24
TEXT
1256345
1.2
NORMAL
223.55,20
TEXT
A
1.2
NORMAL
195.78,15
TEXT
12-12-06
1.2
NORMAL
193.22,12.1
TEXT
SHEET 7K2946
etc

----------------------------------------

To understand the "important" lines to collect I have put
_______________________________ in from of the data to make it more
understanable in this post.

As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in
excel (see prev. post).

Thanks so much!!!

Best Regards

Let me know if this works. I think there may be some problems with the
example you gave. change the path name, input file, and output file as
needed. the program creates a csv file which can be opened in excel after
the macro is run.

Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "longtext.txt"
WriteFileName = "longtext.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'remove spaces at beginning of line
Do While Left(InputLine, 1) = " "
InputLine = Mid(InputLine, 2)
Loop

If (Left(InputLine, 3) = "___") Then

If Len(OutputLine) > 0 Then
OutputLine = OutputLine + ";"
tswrite.WriteLine OutputLine
OutputLine = ""
End If

'remove underline
Do While Left(InputLine, 1) = "_"
InputLine = Mid(InputLine, 2)
Loop
OutputLine = InputLine
Else
OutputLine = OutputLine + "," + InputLine
End If

Loop

If Len(OutputLine) > 0 Then
OutputLine = OutputLine + ";"
tswrite.WriteLine OutputLine
OutputLine = ""
End If


tswrite.Close
tsread.Close

Exit Sub
End Sub


"(E-Mail Removed)" wrote:

> Thanks so much for the helping hand;
>
> This is what the txt file looks like:
>
> _______________________________SHEET 7K294
> 0,2949.41
> 276.52,3104.61
> TEXT
> _______________________________7K294
> 1.8
> NORMAL
> 271.02,32.11
> TEXT
> _______________________________TRANSPORTSYSTEM - A
> 1.5
> NORMAL
> 193.62,32.91
> TEXT
> _______________________________CENTER SYSTEM
> 1.5
> NORMAL
> 193.42,28.61
> TEXT
> _______________________________SFC STEP 4 - WAIT
> 1.5
> NORMAL
> 193.42,24
> TEXT
> 1256345
> 1.2
> NORMAL
> 223.55,20
> TEXT
> A
> 1.2
> NORMAL
> 195.78,15
> TEXT
> 12-12-06
> 1.2
> NORMAL
> 193.22,12.1
> TEXT
> SHEET 7K2946
> etc
>
> ----------------------------------------
>
> To understand the "important" lines to collect I have put
> _______________________________ in from of the data to make it more
> understanable in this post.
>
> As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in
> excel (see prev. post).
>
> Thanks so much!!!
>
> Best Regards
>
>

I didn't notice the comment at the bottom your posting that you added the
"____" to the file. I thought it was part of the data. This should work


Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "longtext.txt"
WriteFileName = "longtext.csv"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)


Do While tsread.atendofstream = False

InputLine = tsread.ReadLine


If (InStr(InputLine, "TEXT") > 0) Then

If Len(OutPutLine) > 0 Then
tswrite.WriteLine OutPutLine
OutPutLine = ""

End If
Else
If Len(OutPutLine) > 0 Then
OutPutLine = OutPutLine + "," + InputLine
Else
OutPutLine = InputLine
End If
End If

Loop

tswrite.Close
tsread.Close

Exit Sub
End Sub


"(E-Mail Removed)" wrote:

> Thanks so much for the helping hand;
>
> This is what the txt file looks like:
>
> _______________________________SHEET 7K294
> 0,2949.41
> 276.52,3104.61
> TEXT
> _______________________________7K294
> 1.8
> NORMAL
> 271.02,32.11
> TEXT
> _______________________________TRANSPORTSYSTEM - A
> 1.5
> NORMAL
> 193.62,32.91
> TEXT
> _______________________________CENTER SYSTEM
> 1.5
> NORMAL
> 193.42,28.61
> TEXT
> _______________________________SFC STEP 4 - WAIT
> 1.5
> NORMAL
> 193.42,24
> TEXT
> 1256345
> 1.2
> NORMAL
> 223.55,20
> TEXT
> A
> 1.2
> NORMAL
> 195.78,15
> TEXT
> 12-12-06
> 1.2
> NORMAL
> 193.22,12.1
> TEXT
> SHEET 7K2946
> etc
>
> ----------------------------------------
>
> To understand the "important" lines to collect I have put
> _______________________________ in from of the data to make it more
> understanable in this post.
>
> As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in
> excel (see prev. post).
>
> Thanks so much!!!
>
> Best Regards
>
>

JOEL Thanks so much!!! This is fantastic. It's 100% what i was looking
for! Can't i send you a present ?

Thanks!!!

J.Lemmens



Joel wrote:
> I didn't notice the comment at the bottom your posting that you added the
> "____" to the file. I thought it was part of the data. This should work
>
>
> Sub Gettext()
>
> Const ForReading = 1, ForWriting = 2, ForAppending = 3
> Const MyPath = "C:\temp\"
> Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
>
>
> Set fsread = CreateObject("Scripting.FileSystemObject")
> Set fswrite = CreateObject("Scripting.FileSystemObject")
>
> ReadFileName = "longtext.txt"
> WriteFileName = "longtext.csv"
>
>
> 'open files
> ReadPathName = MyPath + ReadFileName
> Set fread = fsread.GetFile(ReadPathName)
> Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
>
> WritePathName = MyPath + WriteFileName
> fswrite.CreateTextFile WritePathName
> Set fwrite = fswrite.GetFile(WritePathName)
> Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)
>
>
> Do While tsread.atendofstream = False
>
> InputLine = tsread.ReadLine
>
>
> If (InStr(InputLine, "TEXT") > 0) Then
>
> If Len(OutPutLine) > 0 Then
> tswrite.WriteLine OutPutLine
> OutPutLine = ""
>
> End If
> Else
> If Len(OutPutLine) > 0 Then
> OutPutLine = OutPutLine + "," + InputLine
> Else
> OutPutLine = InputLine
> End If
> End If
>
> Loop
>
> tswrite.Close
> tsread.Close
>
> Exit Sub
> End Sub
>
>
> "(E-Mail Removed)" wrote:
>
> > Thanks so much for the helping hand;
> >
> > This is what the txt file looks like:
> >
> > _______________________________SHEET 7K294
> > 0,2949.41
> > 276.52,3104.61
> > TEXT
> > _______________________________7K294
> > 1.8
> > NORMAL
> > 271.02,32.11
> > TEXT
> > _______________________________TRANSPORTSYSTEM - A
> > 1.5
> > NORMAL
> > 193.62,32.91
> > TEXT
> > _______________________________CENTER SYSTEM
> > 1.5
> > NORMAL
> > 193.42,28.61
> > TEXT
> > _______________________________SFC STEP 4 - WAIT
> > 1.5
> > NORMAL
> > 193.42,24
> > TEXT
> > 1256345
> > 1.2
> > NORMAL
> > 223.55,20
> > TEXT
> > A
> > 1.2
> > NORMAL
> > 195.78,15
> > TEXT
> > 12-12-06
> > 1.2
> > NORMAL
> > 193.22,12.1
> > TEXT
> > SHEET 7K2946
> > etc
> >
> > ----------------------------------------
> >
> > To understand the "important" lines to collect I have put
> > _______________________________ in from of the data to make it more
> > understanable in this post.
> >
> > As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in
> > excel (see prev. post).
> >
> > Thanks so much!!!
> >
> > Best Regards
> >
> >

I like to help! It always easier to get the interface working correctly then
to try to fix the problems later on. Moving cells around in excel can become
complicated and is slower then modifying the text file which is the source of
your problem.

"(E-Mail Removed)" wrote:

> JOEL Thanks so much!!! This is fantastic. It's 100% what i was looking
> for! Can't i send you a present ?
>
> Thanks!!!
>
> J.Lemmens
>
>
>
> Joel wrote:
> > I didn't notice the comment at the bottom your posting that you added the
> > "____" to the file. I thought it was part of the data. This should work
> >
> >
> > Sub Gettext()
> >
> > Const ForReading = 1, ForWriting = 2, ForAppending = 3
> > Const MyPath = "C:\temp\"
> > Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
> >
> >
> > Set fsread = CreateObject("Scripting.FileSystemObject")
> > Set fswrite = CreateObject("Scripting.FileSystemObject")
> >
> > ReadFileName = "longtext.txt"
> > WriteFileName = "longtext.csv"
> >
> >
> > 'open files
> > ReadPathName = MyPath + ReadFileName
> > Set fread = fsread.GetFile(ReadPathName)
> > Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
> >
> > WritePathName = MyPath + WriteFileName
> > fswrite.CreateTextFile WritePathName
> > Set fwrite = fswrite.GetFile(WritePathName)
> > Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)
> >
> >
> > Do While tsread.atendofstream = False
> >
> > InputLine = tsread.ReadLine
> >
> >
> > If (InStr(InputLine, "TEXT") > 0) Then
> >
> > If Len(OutPutLine) > 0 Then
> > tswrite.WriteLine OutPutLine
> > OutPutLine = ""
> >
> > End If
> > Else
> > If Len(OutPutLine) > 0 Then
> > OutPutLine = OutPutLine + "," + InputLine
> > Else
> > OutPutLine = InputLine
> > End If
> > End If
> >
> > Loop
> >
> > tswrite.Close
> > tsread.Close
> >
> > Exit Sub
> > End Sub
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> > > Thanks so much for the helping hand;
> > >
> > > This is what the txt file looks like:
> > >
> > > _______________________________SHEET 7K294
> > > 0,2949.41
> > > 276.52,3104.61
> > > TEXT
> > > _______________________________7K294
> > > 1.8
> > > NORMAL
> > > 271.02,32.11
> > > TEXT
> > > _______________________________TRANSPORTSYSTEM - A
> > > 1.5
> > > NORMAL
> > > 193.62,32.91
> > > TEXT
> > > _______________________________CENTER SYSTEM
> > > 1.5
> > > NORMAL
> > > 193.42,28.61
> > > TEXT
> > > _______________________________SFC STEP 4 - WAIT
> > > 1.5
> > > NORMAL
> > > 193.42,24
> > > TEXT
> > > 1256345
> > > 1.2
> > > NORMAL
> > > 223.55,20
> > > TEXT
> > > A
> > > 1.2
> > > NORMAL
> > > 195.78,15
> > > TEXT
> > > 12-12-06
> > > 1.2
> > > NORMAL
> > > 193.22,12.1
> > > TEXT
> > > SHEET 7K2946
> > > etc
> > >
> > > ----------------------------------------
> > >
> > > To understand the "important" lines to collect I have put
> > > _______________________________ in from of the data to make it more
> > > understanable in this post.
> > >
> > > As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in
> > > excel (see prev. post).
> > >
> > > Thanks so much!!!
> > >
> > > Best Regards
> > >
> > >
>
>

It's fantastic how it works. If i didnt got this i had to do many
steps to get it right and that would cost alot of time wich can be
spent more wisely.

Thanks Joel! Again like to send you some flowers or something



Joel wrote:
> I like to help! It always easier to get the interface working correctly then
> to try to fix the problems later on. Moving cells around in excel can become
> complicated and is slower then modifying the text file which is the source of
> your problem.
>
> "(E-Mail Removed)" wrote:
>
> > JOEL Thanks so much!!! This is fantastic. It's 100% what i was looking
> > for! Can't i send you a present ?
> >
> > Thanks!!!
> >
> > J.Lemmens
> >
> >
> >
> > Joel wrote:
> > > I didn't notice the comment at the bottom your posting that you added the
> > > "____" to the file. I thought it was part of the data. This should work
> > >
> > >
> > > Sub Gettext()
> > >
> > > Const ForReading = 1, ForWriting = 2, ForAppending = 3
> > > Const MyPath = "C:\temp\"
> > > Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
> > >
> > >
> > > Set fsread = CreateObject("Scripting.FileSystemObject")
> > > Set fswrite = CreateObject("Scripting.FileSystemObject")
> > >
> > > ReadFileName = "longtext.txt"
> > > WriteFileName = "longtext.csv"
> > >
> > >
> > > 'open files
> > > ReadPathName = MyPath + ReadFileName
> > > Set fread = fsread.GetFile(ReadPathName)
> > > Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)
> > >
> > > WritePathName = MyPath + WriteFileName
> > > fswrite.CreateTextFile WritePathName
> > > Set fwrite = fswrite.GetFile(WritePathName)
> > > Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)
> > >
> > >
> > > Do While tsread.atendofstream = False
> > >
> > > InputLine = tsread.ReadLine
> > >
> > >
> > > If (InStr(InputLine, "TEXT") > 0) Then
> > >
> > > If Len(OutPutLine) > 0 Then
> > > tswrite.WriteLine OutPutLine
> > > OutPutLine = ""
> > >
> > > End If
> > > Else
> > > If Len(OutPutLine) > 0 Then
> > > OutPutLine = OutPutLine + "," + InputLine
> > > Else
> > > OutPutLine = InputLine
> > > End If
> > > End If
> > >
> > > Loop
> > >
> > > tswrite.Close
> > > tsread.Close
> > >
> > > Exit Sub
> > > End Sub
> > >
> > >
> > > "(E-Mail Removed)" wrote:
> > >
> > > > Thanks so much for the helping hand;
> > > >
> > > > This is what the txt file looks like:
> > > >
> > > > _______________________________SHEET 7K294
> > > > 0,2949.41
> > > > 276.52,3104.61
> > > > TEXT
> > > > _______________________________7K294
> > > > 1.8
> > > > NORMAL
> > > > 271.02,32.11
> > > > TEXT
> > > > _______________________________TRANSPORTSYSTEM - A
> > > > 1.5
> > > > NORMAL
> > > > 193.62,32.91
> > > > TEXT
> > > > _______________________________CENTER SYSTEM
> > > > 1.5
> > > > NORMAL
> > > > 193.42,28.61
> > > > TEXT
> > > > _______________________________SFC STEP 4 - WAIT
> > > > 1.5
> > > > NORMAL
> > > > 193.42,24
> > > > TEXT
> > > > 1256345
> > > > 1.2
> > > > NORMAL
> > > > 223.55,20
> > > > TEXT
> > > > A
> > > > 1.2
> > > > NORMAL
> > > > 195.78,15
> > > > TEXT
> > > > 12-12-06
> > > > 1.2
> > > > NORMAL
> > > > 193.22,12.1
> > > > TEXT
> > > > SHEET 7K2946
> > > > etc
> > > >
> > > > ----------------------------------------
> > > >
> > > > To understand the "important" lines to collect I have put
> > > > _______________________________ in from of the data to make it more
> > > > understanable in this post.
> > > >
> > > > As you can see i need line 4, 9,14,19 to copy to columns B,C,D,E in
> > > > excel (see prev. post).
> > > >
> > > > Thanks so much!!!
> > > >
> > > > Best Regards
> > > >
> > > >
> >
> >