processing txt files

[dshibaev]

hey guys,

Assume a complete novice here (well kind of) who is looking for an efficient way to process .txt files. Actually a few days ago I decided to port all my reports that query to external databases to access and work from there.
Currently stuck with quite a simples problem on importing and processing some data that I will later use.

In essence I need to open a file in VBA, read every single line, remove blanks lines and some other ones that match some string and then save it as a processed file (also .txt)

Given that I have limited experience in vba I have so far arrived at the following, after some googling:

Sub ProcessReportFiles()

Dim inFile As Integer
Dim outFile As Integer
inFile = FreeFile
Open "C:\test.txt" For Input As inFile
lngChars = LOF(inFile)
strImport = Input(lngChars, inFile)


.....................


End Sub

And here I am stuck. Basically I do not even know how to visualise the lines that were imported. Other than that how could I
- loop through every line
- check if a line matches a certain string
- export to file

Any help would come in handy.

Thanks

 

[JBinQLD]

G'day fella,

Try this:

Sub ProcessReportFiles()

Dim inFile As Integer
dim outfile as integer
Dim outFile As Integer
dim txtLineIn as string
dim txtLineOut as string

inFile = FreeFile
outfile=freefile
Open "C:\test.txt" For Input As inFile
open "C:\result.txt" for output as outfile
    
While Not EOF(infile)
   ' Read line into variable.
   LineInput #inFile, txtLineIn 
   'do something with the line
   txtLineOut=DoReplacements(txtLineIn)
   ' put line into an output file
   print #outfile, txtLineOut
End While
'tidy up
Close #inFile
close #outfile

of course you'll also need

function DoReplcaements(txtToProcess as string) as string

'do stuff to txtToProcess
...

DoReplacements=txtToProcess
 
end function

It's been a while but that should be close-ish

JB

 

[Remou]

You may wish to consider DoCmd.TransferText, which will import the file, it can then be cleaned Access-side - this is often quicker and easier.

 

[PHV]

JB
inFile = FreeFile
outfile=freefile
Open "C:\test.txt" For Input As inFile
outfile=freefile
open "C:\result.txt" for output as outfile

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dshibaev]

Excellent!

With just a few minor corrections (took about 5 min) the code was up and running!
Here is the corrected version (of course all the credit goes to JbinQLD) that simply prints the results to the debug window

Sub ProcessReportFiles()

Dim inFile As Integer
Dim outFile As Integer
Dim txtLineIn As String
Dim txtLineOut As String

inFile = FreeFile
Open "C:\test.txt" For Input As inFile
outFile = FreeFile
Open "C:\result.txt" For Output As outFile
    
While Not EOF(inFile)
   ' Read line into variable.
   Line Input #inFile, txtLineIn
   'do something with the line
   txtLineOut = DoReplacements(txtLineIn)
   ' put line into an output file
   Print #outFile, txtLineOut
Wend

'tidy up
Close #inFile
Close #outFile

End Sub

Function DoReplacements(txtToProcess As String) As String

'do stuff to txtToProcess

DoReplacements = txtToProcess
Debug.Print DoReplacements
 
End Function