Function to read text file; find string; delete same before import

[EagleOne]

2003

Need a VBA function to read text file; find string; delete same before import.

The function should find all instances (there can be many) of:
SELECT TO_CHAR(NET
AMT|DOC_NUM|FIPC|

the last line of the file

REPORT COMPLETE

In short, just the data please

SELECT TO_CHAR(NET
AMT|DOC_NUM|FIPC|
407.44|N6887008TORHMS5|D|22|AA|08|
2500.00|N6887008MDNC018|D|36|AA|08|
-2500.00|N6887008MDNC013|D|36|AA|08|
2133.18|N6887008MDNC013|D|36|AA|08
-1004.93|N6887008WR82003|D|36|AA|08|
SELECT TO_CHAR(NET
AMT|DOC_NUM|FIPC|
407.44|N6887008TORHMS5|D|22|AA|08|
2500.00|N6887008MDNC018|D|36|AA|08|
-2500.00|N6887008MDNC013|D|36|AA|08|
2133.18|N6887008MDNC013|D|36|AA|08
-1004.93|N6887008WR82003|D|36|AA|08|
REPORT COMPLETE

TIA any and all help greatly appreciated

 

[Joel]

This is very simple. I don't know what you are refering to when you mean
FIND STRING since everything is a string in the TEXT file. It is also
important to know wher each line ends. Not sure if your code exactly
represents the text file. Open the file with Notepad and make sure WORD WRAP
(Format menu) is NOT checked. Then lok at text file and let me know where
the end of each line is located.

I have two methods of doing this task. I can import the file and then make
the changes in excel to delete the data you don't want. Or make the changes
before I put the data into spreadsheet.

If you want to try a quick experiment try to impor the dat manually.

From worksheet menue
1) Data - Import External Data - Import Data
2) Press Delimited - Next
3) Uncheck everything except Other and put the | in the box next to Other
4) Press finish and select where you want the data to go.

You can also turn on Record Macro before you do these operations to get a
macro.

 

[EagleOne]

Thank you for taking your time and for your knowledge.

I guess I should have been more clear.

The import itself is not the issue. What I was looking for is a function that would:
1) read an entire text file line by line
2) find each and every line with an instance of:
SELECT TO_CHAR(NET (and/or)
AMT|DOC_NUM|FIPC| (and/or)
REPORT COMPLETE (and/or)
3) delete the entire line(s)
4) leaving only the pure data
5) save the file
6) (then import into Excel about which I do not need assistance)

In short, I want the file to contain only:

407.44|N6887008TORHMS5|D|22|AA|08|
2500.00|N6887008MDNC018|D|36|AA|08|
-2500.00|N6887008MDNC013|D|36|AA|08|
2133.18|N6887008MDNC013|D|36|AA|08
-1004.93|N6887008WR82003|D|36|AA|08|
407.44|N6887008TORHMS5|D|22|AA|08|
2500.00|N6887008MDNC018|D|36|AA|08|
-2500.00|N6887008MDNC013|D|36|AA|08|
2133.18|N6887008MDNC013|D|36|AA|08

EagleOne

 

[EagleOne]

Another point:

The text file typically exceeds 65,000 lines. Therefore, I need to clear the file of non-data lines
outside of Excel.

I am not conversant in file/line/string manipulation in VBA (pre-import to) of Excel

 

[Dave Peterson]

Option Explicit
Sub testme01()

Dim TextLine As String
Dim KeepThisRecord As Boolean
Dim iRecCount As Long
Dim oRecCount As Long
Dim myStrings As Variant
Dim sCtr As Long

'just in case
Close #1
Close #2

'my test files
Open "c:\my documents\excel\book2.txt" For Input As #1
Open "c:\my documents\excel\book2.txt.out" For Output As #2

myStrings = Array("SELECT TO_CHAR(NET", _
"AMT|DOC_NUM|FIPC|", _
"REPORT COMPLETE")

iRecCount = 0
oRecCount = 0
Do While Not EOF(1)
Line Input #1, TextLine
iRecCount = iRecCount + 1
KeepThisRecord = True
For sCtr = LBound(myStrings) To UBound(myStrings)
If UCase(TextLine) Like "*" & UCase(myStrings(sCtr)) & "*" Then
'skip it
KeepThisRecord = False
Exit For
End If
Next sCtr
If KeepThisRecord = True Then
Print #2, TextLine
oRecCount = oRecCount + 1
End If
Loop

Close #1
Close #2

MsgBox "Recs In: " & iRecCount & vbLf & "Recs Out: " & oRecCount

End Sub

 

[EagleOne]

David, as usual, you win the day.

Your code can be modified by others too. Thanks for all.

ps. Does the Close statement save the file also?

 

[Rick Rothstein \(MVP - VB\)]

Your file is not too large to do with a macro in Excel (just change the path
and filename assignment to your own file's path and name)...

Sub GetDataOnly()
Dim FileNum As Long
Dim FileName As String
Dim TotalFile As String

FileName = "c:\Path\To\File\MyFile.txt"

FileNum = FreeFile
Open FileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
TotalFile = replace(TotalFile, "SELECT TO_CHAR(NET" & vbCrLf & _
"AMT|DOC_NUM|FIPC|" & vbCrLf, "")
TotalFile = replace(TotalFile, "REPORT COMPLETE", "")
FileNum = FreeFile
Open FileName For Output As #FileNum
Print #FileNum, TotalFile
Close #FileNum
End Sub

Rick

 

[Dave Peterson]

It makes sure that whatever the last bits that are supposed to be written to the
file are written to the file.

And for the files that were opened for input, it says you're done with them.

 

[Dave Peterson]

We also had some reports printed on white stock (11x17). These were called
"whitebar".

It always made me laugh <vbg>.

Dave Peterson wrote:

[snip nice code]

Looks like you have parsed a few greenbar reports in your day (^:

 

[Dick Kusleika]

The import itself is not the issue. What I was looking for is a function that would:
1) read an entire text file line by line
2) find each and every line with an instance of:
SELECT TO_CHAR(NET (and/or)
AMT|DOC_NUM|FIPC| (and/or)
REPORT COMPLETE (and/or)
3) delete the entire line(s)
4) leaving only the pure data
5) save the file
6) (then import into Excel about which I do not need assistance)

Sub DoStuff()

Dim sFileToImport As String

sFileToImport = GetCleanFileName("C:\Import.txt")

'do import stuff here

End Sub

Function GetCleanFileName(sInputFile As String) As String

Dim sCleanFileName As String
Dim lFnumIn As Long, lFnumClean As Long
Dim sLine As String

Const sDELA As String = "SELECT TO_CHAR(NET"
Const sDELB As String = "AMT|DOC_NUM|FIPC|"
Const sDELC As String = "REPORT COMPLETE"

sCleanFileName = "C:\ImportClean.txt"

lFnumIn = FreeFile
Open sInputFile For Input As lFnumIn

lFnumClean = FreeFile
Open sCleanFileName For Output As lFnumClean

Do While Not EOF(lFnumIn)
Line Input #lFnumIn, sLine
If InStr(1, sLine, sDELA) = 0 And _
InStr(1, sLine, sDELB) = 0 And _
InStr(1, sLine, sDELC) = 0 Then

Print #lFnumClean, sLine
End If
Loop

Close lFnumIn
Close lFnumClean

GetCleanFileName = sCleanFileName

End Function

 

[Dave Peterson]

I remember having trouble with VBA's Replace for longgggggg strings--and since
the OP said that there were over 65k lines of data, I wonder if VBA's replace
would work in this case.

But regex.replace seems to work with large/huge files.

Sub UpDateTxtFile2()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String

Dim myStrings As Variant
Dim sCtr As Long

myInFileName = "C:\my documents\excel\book2.txt"
myOutFileName = "C:\my documents\excel\book2.txt.out"

myStrings = Array("SELECT TO_CHAR(NET", _
"AMT|DOC_NUM|FIPC|", _
"REPORT COMPLETE")

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
For sCtr = LBound(myStrings) To UBound(myStrings)
With RegEx
myStrings(sCtr) = Replace(myStrings(sCtr), "_", "\_")
myStrings(sCtr) = Replace(myStrings(sCtr), "(", "\(")
myStrings(sCtr) = Replace(myStrings(sCtr), "|", "\|")

.Global = True
.IgnoreCase = True
.Pattern = myStrings(sCtr) & vbCrLf
myContents = .Replace(myContents, "")
End With
Next sCtr

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

Or using those 3 lines as one string:

Option Explicit
Sub UpDateTxtFile3()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String
Dim myString As String

myInFileName = "C:\my documents\excel\book2.txt"
myOutFileName = "C:\my documents\excel\book2.txt.out"

myString = "SELECT TO_CHAR(NET" & vbCrLf _
& "AMT|DOC_NUM|FIPC|" & vbCrLf _
& "REPORT COMPLETE" & vbCrLf

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
myString = Replace(myString, "_", "\_")
myString = Replace(myString, "(", "\(")
myString = Replace(myString, "|", "\|")

.Global = True
.IgnoreCase = True
.Pattern = myString
myContents = .Replace(myContents, "")
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

 

[EagleOne]

Rick, having solutions bith inside and outside is Excel excellent!

I looked up FreeFile and found it as a Function. Is FreeFile a function available by reference to
the "typical" Excel and/or typical-install Visual Basic Libraries or should one grab the FreeFall
function and place it in one's VBA module?

 

[EagleOne]

Dave,

You must have had a lot of coffee last night! Green Bar! What a concept.

BTW, if our boneheaded Green Bar-trained Main-frame IT gurus did their job 15 years ago, I would not
have to deal with crap laced through the data file. I wish I could tell you how many MEGA millions
were spent on the s/w, as updated ,which was written by the #1 big s/w company.

I had no idea that you are a Regex person. Ron Rosenfeld (I think an MVP) who was/is excellent in
RegEx who would miraculously show up when file/string issues would pop up.

EagleOne

 

[EagleOne]

Dick,

The Function solution has arrived, Thanks.

What are your thoughts as to the below solution as opposed to the Regex?

My point being, that RegEx is probably the best for HUUUUGH files but begs the next issue - RegEx
Reference libraries.

EagleOne

 

[Rick Rothstein \(MVP - VB\)]

FreeFile is a function built into VB, so you do not have to do anything
special to use it... it is automatically available. By the way, the code I
posted will work, as posted, within Excel as a macro... if you have the
stand-alone (compiled) Visual Basic program available (Version 6 only
because of the use of the Replace function), then the subroutine I posted
can be run from inside a VB6 program exactly as written and it will work in
that environment too.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I remember having trouble with VBA's Replace for longgggggg strings--and

since
the OP said that there were over 65k lines of data, I wonder if VBA's
replace
would work in this case.

Based on the sample data the OP posted, I estimate his data file is around 2
to 2.5 Megs in size... that should not be too large for the Replace function
to be able to do its "thing" with. Maybe if the file were around 15 to 20
Megs or more, I would be reluctant to recommend the solution I posted, but I
feel it will work comfortably with files up to 10 Megs without any problems.

Rick

 

[EagleOne]

Rick,

Is it necessary (the only way) to VB6-compile a DLL to call from a macro to "secure" the code?

If so, I'll purchase VB6 just for that purpose.

I assume the following steps?:

1) write the VBA procedure(s)
2) load VB6
3) perform 1,2,3,4 steps in VB6
4) out pops a DLL.
5) refer to the DLL in my macro
6) (whatever I missed)

Would you BRIEFLY (being sensitive to your time) expand upon #3 & #5?

EagleOne

 

[EagleOne]

In reality, the files have gone to 25MB; average about 8MB.

All of the solutions have their place. In advance of my posting, I did do Google Searches - both
the Add in and straight through. I was surprised that I did find much directly
applicable/easily-adaptable to either Excel and/or Access.

All of the responses are excellent for this forum to synergyze OPs on this issue. Thanks to all for
your time and knowledge.

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure I understand what your "'secure' the code" concerns are... what
part of the process is it that you are worried about, what part do you think
needs to be secured?

Rick

 

[Rick Rothstein \(MVP - VB\)]

If your files are really that big, I would not suggest your using the
solution I posted... 25 Megs would probably push the code too hard (I still
think the code would work, but it would be much, much slower, exponentially
so, than for a 2 to 3 Meg file).

Rick