I have a routine (see below) which I wrote to extract emails addresses
from a large text file (233MB - approximately 7677083 rows). The
routine is reading each row to determine if three conditions are met.
If the three conditions are met the routine will write the information
(email address, email address row number, bankruptcy info, and
bankruptcy line number) to the active worksheet. I am using Excel
2003 and have a Quad core 3.2Ghz CPU in my computer. I have let the
routine run overnight and it short of dies. In other words, the
computer thinks the routine is not running. After several attempts
the routine never finishes and I have to close Excel to get anything
to happen. Should it take this long to run? Any suggestions would be
appreciated on how to get the routine to complete in a timely manner.

Sub ReadStraightTextFile5()
' This sub will read a text file line by line
' it will look for the word email in the beginning of the
' line and if it finds it the email address and finds the word
' "criminal" in the line of text in the next 5 rows it will be
extracted
' and written to a worksheet

' If the row number is the worksheet
' exceeds 65000 the next column will be used for the email
' addresses.

' Criteria = Bankruptcy, email, Fl, GA, SC, Texas

Dim sStr As String
Dim sStrEmail As String 'email address
Dim sStrVar2 As String
Dim emailCondition As Boolean
Dim stateCondition As Boolean
Dim bankCondition As Boolean
Dim LineofText As String
Dim rw As Long
Dim Email As String
Dim DoNothing As Integer
Dim Counter As Long
Dim MyColumn As Long
Dim FileName As String
Dim LineNum As Long
Dim EmailLineNum As Long 'Line number of the email address
Dim StateLineNum As Long
Dim BankLineNum As Long
Dim ResetNow As Boolean 'Reset the variable if true
Dim Bankinfo As String



''FileName = InputBox("Please enter the Text File's name, e.g.
test.txt")

Application.ScreenUpdating = False

' Set up Variables
Counter = 0
MyColumn = 1
rw = 1
LineNum = 0
sStr = ""

' Specify the file to open
Open "E:\AA Temp Files\Combined1.txt" For Input As #1
''Open FileName For Input As #1

' Loop through the text file one line at a time
Do While Not EOF(1)
LineNum = LineNum + 1 'Count the Lines
'Start new column if row exceeds 65000
If Counter = 65000 Then
rw = 1
MyColumn = MyColumn + 4 'move over 4 columns
Counter = 1
End If

' Show the line number in the immediate window
Debug.Print LineNum

Line Input #1, LineofText
sStr = sStr & LineofText

' Check for a New Entry, start variable over since the 3 conditions
were not met.
If Left(LineofText, 4) = "ISLN" Then
sStrEmail = ""
stateCondition = False
emailCondition = False
bankCondition = False
EmailLineNum = 0
StateLineNum = 0
BankLineNum = 0
Else
DoNothing = 0
End If

' Check for Correct State
If InStr(LineofText, " FL ") Or InStr(LineofText, " FL") Or _
InStr(LineofText, " GA ") Or InStr(LineofText, " GA") Or _
InStr(LineofText, " TX ") Or InStr(LineofText, " TX") Or _
InStr(LineofText, " SC ") Or InStr(LineofText, " SC") Then
stateCondition = True
StateLineNum = LineNum
Else
DoNothing = 0
End If


' Check for an Email Address
If Left(LineofText, 5) = "Email" Then
sStrEmail = Right(LineofText, Len(LineofText) - 7)
emailCondition = True
EmailLineNum = LineNum
Else
DoNothing = 0
End If

' Check for the word "Bankruptcy"
If InStr(LineofText, "Bankruptcy") Then
bankCondition = True
BankLineNum = LineNum
Bankinfo = LineofText
Else
DoNothing = 0
End If

' If all three conditions are true post the information to the
active Worksheet
If emailCondition = True And stateCondition = True And
bankCondition = True Then
Cells(rw, MyColumn).Value = sStrEmail
Cells(rw, MyColumn + 1).Value = EmailLineNum
Cells(rw, MyColumn + 2).Value = Bankinfo
Cells(rw, MyColumn + 3).Value = BankLineNum
rw = rw + 1
Counter = Counter + 1
emailCondition = False
stateCondition = False
bankCondition = False
Else
DoNothing = 0
End If



Loop


'Close the file
If Len(sStr) 0 Then
DoNothing = 0
'Cells(rw, 1).Value = sStr
End If
Close #1

Application.ScreenUpdating = True
MsgBox ("All Done")
End Sub

Thanks,
Jim

I've only had a quick look, but I can't see a movenext(1) in your loop - this
means you'll never get out of the loop starting "Do While Not EOF(1)"

Sam

"Jim" wrote:

I have a routine (see below) which I wrote to extract emails addresses
from a large text file (233MB - approximately 7677083 rows). The
routine is reading each row to determine if three conditions are met.
If the three conditions are met the routine will write the information
(email address, email address row number, bankruptcy info, and
bankruptcy line number) to the active worksheet. I am using Excel
2003 and have a Quad core 3.2Ghz CPU in my computer. I have let the
routine run overnight and it short of dies. In other words, the
computer thinks the routine is not running. After several attempts
the routine never finishes and I have to close Excel to get anything
to happen. Should it take this long to run? Any suggestions would be
appreciated on how to get the routine to complete in a timely manner.

Sub ReadStraightTextFile5()
' This sub will read a text file line by line
' it will look for the word email in the beginning of the
' line and if it finds it the email address and finds the word
' "criminal" in the line of text in the next 5 rows it will be
extracted
' and written to a worksheet

' If the row number is the worksheet
' exceeds 65000 the next column will be used for the email
' addresses.

' Criteria = Bankruptcy, email, Fl, GA, SC, Texas

Dim sStr As String
Dim sStrEmail As String 'email address
Dim sStrVar2 As String
Dim emailCondition As Boolean
Dim stateCondition As Boolean
Dim bankCondition As Boolean
Dim LineofText As String
Dim rw As Long
Dim Email As String
Dim DoNothing As Integer
Dim Counter As Long
Dim MyColumn As Long
Dim FileName As String
Dim LineNum As Long
Dim EmailLineNum As Long 'Line number of the email address
Dim StateLineNum As Long
Dim BankLineNum As Long
Dim ResetNow As Boolean 'Reset the variable if true
Dim Bankinfo As String



''FileName = InputBox("Please enter the Text File's name, e.g.
test.txt")

Application.ScreenUpdating = False

' Set up Variables
Counter = 0
MyColumn = 1
rw = 1
LineNum = 0
sStr = ""

' Specify the file to open
Open "E:\AA Temp Files\Combined1.txt" For Input As #1
''Open FileName For Input As #1

' Loop through the text file one line at a time
Do While Not EOF(1)
LineNum = LineNum + 1 'Count the Lines
'Start new column if row exceeds 65000
If Counter = 65000 Then
rw = 1
MyColumn = MyColumn + 4 'move over 4 columns
Counter = 1
End If

' Show the line number in the immediate window
Debug.Print LineNum

Line Input #1, LineofText
sStr = sStr & LineofText

' Check for a New Entry, start variable over since the 3 conditions
were not met.
If Left(LineofText, 4) = "ISLN" Then
sStrEmail = ""
stateCondition = False
emailCondition = False
bankCondition = False
EmailLineNum = 0
StateLineNum = 0
BankLineNum = 0
Else
DoNothing = 0
End If

' Check for Correct State
If InStr(LineofText, " FL ") Or InStr(LineofText, " FL") Or _
InStr(LineofText, " GA ") Or InStr(LineofText, " GA") Or _
InStr(LineofText, " TX ") Or InStr(LineofText, " TX") Or _
InStr(LineofText, " SC ") Or InStr(LineofText, " SC") Then
stateCondition = True
StateLineNum = LineNum
Else
DoNothing = 0
End If


' Check for an Email Address
If Left(LineofText, 5) = "Email" Then
sStrEmail = Right(LineofText, Len(LineofText) - 7)
emailCondition = True
EmailLineNum = LineNum
Else
DoNothing = 0
End If

' Check for the word "Bankruptcy"
If InStr(LineofText, "Bankruptcy") Then
bankCondition = True
BankLineNum = LineNum
Bankinfo = LineofText
Else
DoNothing = 0
End If

' If all three conditions are true post the information to the
active Worksheet
If emailCondition = True And stateCondition = True And
bankCondition = True Then
Cells(rw, MyColumn).Value = sStrEmail
Cells(rw, MyColumn + 1).Value = EmailLineNum
Cells(rw, MyColumn + 2).Value = Bankinfo
Cells(rw, MyColumn + 3).Value = BankLineNum
rw = rw + 1
Counter = Counter + 1
emailCondition = False
stateCondition = False
bankCondition = False
Else
DoNothing = 0
End If



Loop


'Close the file
If Len(sStr) 0 Then
DoNothing = 0
'Cells(rw, 1).Value = sStr
End If
Close #1

Application.ScreenUpdating = True
MsgBox ("All Done")
End Sub

Thanks,
Jim

Hi Sam,

Thanks for the advice. When I use my code on a smaller
file it has not problems getting the data and closing the
open file. So this means the loop completes.

Jim

On Fri, 30 May 2008 04:44:00 -0700, Sam Wilson
wrote:

I've only had a quick look, but I can't see a movenext(1) in your loop - this
means you'll never get out of the loop starting "Do While Not EOF(1)"

Sam

"Jim" wrote:

I have a routine (see below) which I wrote to extract emails addresses
from a large text file (233MB - approximately 7677083 rows). The
routine is reading each row to determine if three conditions are met.
If the three conditions are met the routine will write the information
(email address, email address row number, bankruptcy info, and
bankruptcy line number) to the active worksheet. I am using Excel
2003 and have a Quad core 3.2Ghz CPU in my computer. I have let the
routine run overnight and it short of dies. In other words, the
computer thinks the routine is not running. After several attempts
the routine never finishes and I have to close Excel to get anything
to happen. Should it take this long to run? Any suggestions would be
appreciated on how to get the routine to complete in a timely manner.

Sub ReadStraightTextFile5()
' This sub will read a text file line by line
' it will look for the word email in the beginning of the
' line and if it finds it the email address and finds the word
' "criminal" in the line of text in the next 5 rows it will be
extracted
' and written to a worksheet

' If the row number is the worksheet
' exceeds 65000 the next column will be used for the email
' addresses.

' Criteria = Bankruptcy, email, Fl, GA, SC, Texas

Dim sStr As String
Dim sStrEmail As String 'email address
Dim sStrVar2 As String
Dim emailCondition As Boolean
Dim stateCondition As Boolean
Dim bankCondition As Boolean
Dim LineofText As String
Dim rw As Long
Dim Email As String
Dim DoNothing As Integer
Dim Counter As Long
Dim MyColumn As Long
Dim FileName As String
Dim LineNum As Long
Dim EmailLineNum As Long 'Line number of the email address
Dim StateLineNum As Long
Dim BankLineNum As Long
Dim ResetNow As Boolean 'Reset the variable if true
Dim Bankinfo As String



''FileName = InputBox("Please enter the Text File's name, e.g.
test.txt")

Application.ScreenUpdating = False

' Set up Variables
Counter = 0
MyColumn = 1
rw = 1
LineNum = 0
sStr = ""

' Specify the file to open
Open "E:\AA Temp Files\Combined1.txt" For Input As #1
''Open FileName For Input As #1

' Loop through the text file one line at a time
Do While Not EOF(1)
LineNum = LineNum + 1 'Count the Lines
'Start new column if row exceeds 65000
If Counter = 65000 Then
rw = 1
MyColumn = MyColumn + 4 'move over 4 columns
Counter = 1
End If

' Show the line number in the immediate window
Debug.Print LineNum

Line Input #1, LineofText
sStr = sStr & LineofText

' Check for a New Entry, start variable over since the 3 conditions
were not met.
If Left(LineofText, 4) = "ISLN" Then
sStrEmail = ""
stateCondition = False
emailCondition = False
bankCondition = False
EmailLineNum = 0
StateLineNum = 0
BankLineNum = 0
Else
DoNothing = 0
End If

' Check for Correct State
If InStr(LineofText, " FL ") Or InStr(LineofText, " FL") Or _
InStr(LineofText, " GA ") Or InStr(LineofText, " GA") Or _
InStr(LineofText, " TX ") Or InStr(LineofText, " TX") Or _
InStr(LineofText, " SC ") Or InStr(LineofText, " SC") Then
stateCondition = True
StateLineNum = LineNum
Else
DoNothing = 0
End If


' Check for an Email Address
If Left(LineofText, 5) = "Email" Then
sStrEmail = Right(LineofText, Len(LineofText) - 7)
emailCondition = True
EmailLineNum = LineNum
Else
DoNothing = 0
End If

' Check for the word "Bankruptcy"
If InStr(LineofText, "Bankruptcy") Then
bankCondition = True
BankLineNum = LineNum
Bankinfo = LineofText
Else
DoNothing = 0
End If

' If all three conditions are true post the information to the
active Worksheet
If emailCondition = True And stateCondition = True And
bankCondition = True Then
Cells(rw, MyColumn).Value = sStrEmail
Cells(rw, MyColumn + 1).Value = EmailLineNum
Cells(rw, MyColumn + 2).Value = Bankinfo
Cells(rw, MyColumn + 3).Value = BankLineNum
rw = rw + 1
Counter = Counter + 1
emailCondition = False
stateCondition = False
bankCondition = False
Else
DoNothing = 0
End If



Loop


'Close the file
If Len(sStr) 0 Then
DoNothing = 0
'Cells(rw, 1).Value = sStr
End If
Close #1

Application.ScreenUpdating = True
MsgBox ("All Done")
End Sub

Thanks,
Jim