open flat file for output to another file 3

[awelch]

Alright. My simple question is.. How do you open one flat file in this first format then output to another file in another format. I need to loop through the records in the file and at every space write a line(record) to the second file. My files are formatted like this...

abc##12 ab ac ad ae af ag ah ai aj
abc##13 ab ac ad ae af ag ah ai aj

I need to read this in and output to another file formatted like...

abc##12
ab
ac
ad
ae
af
ag
ah
ai
aj
abc##13
ab
ac
ad
ae
af
ag
ah
ai
aj

Any help would be greatly appreciated.

Andrea

 

[SonOfEmidec1100]

Ive just thrown this together so untested but should work barring typos,

Dim infilenum As Long
Dim outfilenum As Long
Dim c As Long

Dim buff As String
Dim myarray

infilenum = FreeFile()
Open "c:\myfile.txt" For Input As infilenum

outfilenum = FreeFile()
Open "c:\myfileout.txt" For Output As outfilenum

Do Until EOF(infilenum)
Line Input #infilenum, buff
myarray = Split(buff, " ", -1)
For c = 0 To UBound(myarray)
Print #outfilenum, myarray(c)
Next
Loop

Close infilenum
Close outfilenum

 

[awelch]

Thanks for your quick response. I added the code and am getting a compile error:expected array. The debugger is highlighting UBound on this line:

For c = 0 To UBound(myarray)

Any more suggestions....??

Andrea

 

[awelch]

DOH!!!

I had Dim myarray as string instead of myarray() as string.

The For loop is not stopping at the spaces. It is sending the whole line to the output file.


Here is my current version of the code:

Private Sub cmdProcess_Click()
'declare variables
   Dim lngCount As Long
   Dim buff, tmpText, myarray() As String
   
'open files
   Open "C:\inputfile.txt" For Input As #1
   Open "C:\outputfile.txt" For Output As #2
   
'process file
   Do While Not EOF(1)
     Line Input #1, buff
     myarray = Split(buff, " ", -1)
     For lngCount = 0 To UBound(myarray)
     
       Print #2, myarray(lngCount)
     Next
   Loop
   
'close records
   Close #2
   Close #1
   
'notify when process is complete
   MsgBox "Finished Processing Files", vbOKOnly
   
   
End Sub

Thanks in advance for any additional insight.

Andrea

 

[awelch]

Here is the latest version of the code. I just need the codee that will force a line feed whenever it comes across a space. I have that area commented for the needed code.

Private Sub cmdProcess_Click()
Dim strInputByte, strOutputByte As String

'Path and file name to open
    Open Text1.Text For Input As #1
    
'Path and file name of output file
    Open Text2.Text For Output As #2

    Do While Not EOF(1)
        strInputByte = Input$(1, #1)
        strOutputByte = strInputByte
        
        If strInputByte = " " Then
 'need code to move to next line here
        Else
          strOutputByte = strInputByte
        Print #2, strOutputByte;
        End If
    Loop
    
Close #2
Close #1

 MsgBox "Parse Complete", vbInformation
 
End Sub

Thanks for the help.

Andrea

 

[awelch]

OOOOKAAAAY!

figured out how to force line feed. vb101......vbCrLf .

The code is working. It parses out to the output file. Only one problem. It is not in the correct format. It looks like this:

T
T
#
#
0
0

B
E

A
A

A
B


Pretty sure the following line of code is the culprit:
strInputByte = Input$(1, #1)

If I modify by saying strInputByte = Input$(6, #1) then the first 6 characters are printed properly but the rest are not.

So, my newest question is.... how to get the output file in the proper format??

I keep dancing around the solution, so any help would be wonderful.

Thanks again,

Andrea

 

[johnwm]

Your version 2 (starting 'DOH') looks OK so I copied and pasted it.

It seems to work as described for me - what problem are you having with it? The two potential problem areas that I see:

[tt]Dim buff, tmpText, myarray() As String[/tt]

which Dims buff and tmpText as variants, and:

[tt] Open "C:\inputfile.txt" For Input As #1
Open "C:\outputfile.txt" For Output As #2
[/tt]
rather than using the FreeFile function as suggested by SonOfEmidec1100. This will fail if #1 or #2 are in use anywhere else in the system at the same time.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[SonOfEmidec1100]

Just got back, tested my original version works like a dream without any changes - and so does yours under DOH.


You second version works with a minor change

Do While Not EOF(1)
strInputByte = Input$(1, #1)
strOutputByte = strInputByte

If strInputByte = " " Then
Print #2, '<<force crlf
Else
strOutputByte = strInputByte
Print #2, strOutputByte;
End If
Loop
Print #2, 'ditto

 

[MichaelRed]

While the porevioous soloutions are adequate for the sample and other small files, they will become quite inefficient if used on larger files. &quot;I/O&quot; is a slow process and should generally be minimized, thus reading and writing a line / record at a time should be avoided if possible. The following (two) routines combine to read the input in a single operqtion and write the output in one additionl I/O step. While the difference in execution time for the sample (and other small files) is probably not even measureable, as the source file grows the difference would become significant.

Public Function basGrabFile(FilIn As String) As String

    'Michael Red    3/3/2003
    'Sample Usage:  ? basGrabFile("C:\MsAccess\DrawArcsInVB.Txt")
    'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
     and the entire text is returned to the caller [prog | procedure]

    Dim MyFil As Integer

    Dim MyTxt As String
    Dim MyPrts() As String
    Dim MyPrtRec() As String

    'Just grab the Stuff
    MyFil = FreeFile

    Open FilIn For Binary As #MyFil

    MyTxt = String(LOF(MyFil), " ")
    Get #MyFil, 1, MyTxt

    Close #MyFil

    basGrabFile = MyTxt

End Function

Public Function basSer2Par(FilIn As String, Filout As String) As Boolean

    'Michael Red    8/4/2003
    'Read Text File.  Replace Space with LF. Write Results to Different Text File

    Dim strIn As String
    Dim strOut As String
    Dim lgFilOut As Long

    strIn = basGrabFile(FilIn)
    strOut = Replace(strIn, Space(1), vbCrLf)

    lgFilOut = FreeFile
    Open lgFilOut For Random As #lgFilOut Len = Len(strOut)
    Put #lgFilOut, 1, Filout
    Close lgFilOut

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[awelch]

Thanks johnwm, SonOfEmidec1100, MichaelRed for your reply's. I am sure they would work if I had provided the right file format.

Just found out that the text is delimited by a TAB not spaces. What do I need to change now?

Andrea

 

[MichaelRed]

Public Function basGrabFile(FilIn As String) As String

'Michael Red 3/3/2003
'Sample Usage: ? basGrabFile(&quot;C:\MsAccess\DrawArcsInVB.Txt&quot
'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
and the entire text is returned to the caller [prog | procedure]

Dim MyFil As Integer

Dim MyTxt As String
Dim MyPrts() As String
Dim MyPrtRec() As String

'Just grab the Stuff
MyFil = FreeFile

Open FilIn For Binary As #MyFil

MyTxt = String(LOF(MyFil), &quot; &quot
Get #MyFil, 1, MyTxt

Close #MyFil

basGrabFile = MyTxt

End Function
Public Function basSer2Par(FilIn As String, Filout As String) As Boolean

'Michael Red 8/4/2003
'Read Text File. Replace Space with LF. Write Results to Different Text File

Dim strIn As String
Dim strOut As String
Dim lgFilOut As Long

strIn = basGrabFile(FilIn)
strOut = Replace(strIn, vbTab, vbCr)

lgFilOut = FreeFile
Open Filout For Binary As #lgFilOut Len = Len(strOut)
Put #lgFilOut, 1, strOut
Close lgFilOut

End Function


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[strongm]

Just for fun, how about (you'll need to add a reference to the Microsoft Scripting Runtime):
[tt]
Private Sub Reformat(strFileSource As String, strFileDest As String, Optional strDelimiter As String = &quot; &quot
With New FileSystemObject
.OpenTextFile(strFileDest, ForWriting, True, TristateUseDefault).Write Join(Split(.OpenTextFile(strFileSource, , , TristateUseDefault).ReadAll, strDelimiter), vbCrLf)
End With
End Sub[/tt]
This can then be called as follows:
Reformat &quot;c:\test1.txt&quot;, &quot;c:\test2.txt&quot;, vbTab

 

[awelch]

Got it working! Thanks for all the help johnwm and SonOfEmidec1100. I used combination of both ideas/tips. I did notice, however that there are at times pages of blank lines.

Not all the records are the same length. Some of the records have tabs to fill in....

I have tried various ways to stop a line feed where there are two or more Tabs but no luck.

Any more ideas?????
How about a good book that deals with parsing flat files?

Strongm, and MichaelRed...i haven't tried your methods yet, but thanks for the advice. I may try them once done with this. I apparently have many different files that need to be parsed this way.

 

[MichaelRed]

The additional (blank) lines are from the multiple instances of the seperator character (&quot;Tab&quot; / Chr(9) / vbTab) present in your file. They exist in the sample file between the first and second &quot;words&quot; in each line. The following functions will 'correct' this -at least in the particular instance. Since you appear to have limited capability in VBA, I would not suggest that you attempt to generally implement the soloution. as it lacks any error checking and will, almost surely, behave badly in any situation other than strict adherance to the 'tab delimited' file format. On the other hand, I know of no soloution which would be much simpler for your use in the transform.

Public Function basGrabFile(FilIn As String) As String

    'Michael Red    3/3/2003
    'Sample Usage:  ? basGrabFile("C:\MsAccess\DrawArcsInVB.Txt")
    'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
     and the entire text is returned to the caller [prog | procedure]

    Dim MyFil As Integer

    Dim MyTxt As String
    Dim MyPrts() As String
    Dim MyPrtRec() As String

    'Just grab the Stuff
    MyFil = FreeFile

    Open FilIn For Binary As #MyFil

    MyTxt = String(LOF(MyFil), " ")
    Get #MyFil, 1, MyTxt

    Close #MyFil

    basGrabFile = MyTxt

End Function

Public Function basSer2Par(FilIn As String, Filout As String) As Boolean

    'Michael Red    8/4/2003
    'Read Text File.  Replace Space with LF. Write Results to Different Text File

    Dim strIn As String
    Dim strOut As String
    Dim strMid As String
    Dim lgFilOut As Long
    Dim lngStrLen As Long

    strIn = basGrabFile(FilIn)

    strMid = strIn & Space(2)
    lngStrLen = Len(strIn)
    While lngStrLen <> Len(strMid)
        strMid = Replace(strMid, Chr(9) & Chr(9), Chr(9))
        lngStrLen = Len(strMid)
    Wend

    strOut = Replace(strMid, Chr(9), vbCrLf)

    lgFilOut = FreeFile
    Open Filout For Binary As #lgFilOut Len = Len(strOut)
    Put #lgFilOut, 1, strOut
    Close lgFilOut

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[awelch]

MichaelRed,

I copied your code into a new Project, adjusted for file locations and am receiving the following :

Run-Time Error 6. Overflow

debugger highlights this line:

Open Filout For Binary As #lgFilOut Len = Len(strOut)

the Len(strOut) = 81446

Thanks for your help

Andrea

 

[awelch]

strongm,

Tried your solution and it does output to the file in the correct format, but again there are many blank lines as stated in previous posts. Know what is causing the problem now just looking for the solution. (like always)

Thanks for you input. I really appreciate it.

I will be looking through books tonight. Think I saw something called SuperTrim that may help!

 

[strongm]

In that case, add a reference to the Microsoft VBScript Regular Expressions library, and use this fractionally longer version:
[tt]
Private Sub Reformat(strFileSource As String, strFileDest As String, Optional strDelimiter As String = &quot; &quot
Dim re As New RegExp
re.Global = True
re.Pattern = &quot;(&quot; + strDelimiter + &quot+&quot;
With New FileSystemObject
.OpenTextFile(strFileDest, ForWriting, True, TristateUseDefault).Write re.Replace(.OpenTextFile(strFileSource, , , TristateUseDefault).ReadAll, vbCrLf)
End With
End Sub

 

[DrJavaJoe]

strongm, I bet you wash your car with Regular Expressions.

 

[strongm]

I'm impressed that I managed to resist suggesting them until my second post...

 

[awelch]

strongm, you are a vb GOD!!!!!!!!! You ROCK, as my kids would say!!!

Thank you so much!

Where can I get some good info on processing delimited files??

Looks like I am going to be doing a lot of it, and I was just getting comfortable with sql server200 db's. Oh well..

Thanks again.

Andrea