Solved

How do I convert a variable record length into a fix record length reading sequentially?

Posted on 2004-04-08
8
220 Views
Last Modified: 2012-06-27
I have an input file with variable length records less than 90.  I want to convert this into a fixed length 111 record.

1. I need to read sequentially the entire record into one string variable that will contain the entire record.
2. I need to strip off the ending CrLf.
3. I need to change the variable length records into a fixed length UDT of 111.
4. I need the difference of the variable length and the fixed length to be spaces.

Here is the code I tried --> Failed, because the name field of the input record had a comma in them an the input statement stopped at the first comma.  Is there a way to read sequentially the entire record ignoring the comma delimitation?

    Type CnvCHSEFTRec
      Detail                          As String * 111
      EndOfLine                       As String * 2
    End Type

    Dim gCnvCHSEFTRec As CnvCHSEFTRec
    Dim strInCHSEFTFileName As String
    Dim strOutCHSEFTFileName As String
    Dim lngCnvCHSEFTRecLen As Long
    Dim strInRec As String
    Dim strInRec2 As String
    Dim strOutRec As String
    Dim strSpacesRec As String
    Dim intInRecLen As Integer
    Dim intStartPtr As Integer
    Dim intRemainLen As Integer
    Const gIN_CHS_EFT_FILE_NBR As Integer = 1
    Const gOUT_CHS_EFT_FILE_NBR As Integer = 2
   
    strInCHSEFTFileName = "\\IN\EFTFILE.TXT"
    strOutCHSEFTFileName = "\\OUT\EFTFILE.TXT"
    Open strInCHSEFTFileName For Input As gIN_CHS_EFT_FILE_NBR
    Open strOutCHSEFTFileName For Output As gOUT_CHS_EFT_FILE_NBR
       
    Do Until EOF(gIN_CHS_EFT_FILE_NBR)
        Input #gIN_CHS_EFT_FILE_NBR, strInRec
        intInRecLen = Len(strInRec)
        Mid(strInRec, intInRecLen - 2, 2) = ""
        intStartPtr = intInRecLen + 1
        gCnvCHSEFTRec.Detail = ""
        strSpacesRec = gCnvCHSEFTRec.Detail
        intRemainLen = Len(gCnvCHSEFTRec.Detail) - intInRecLen
        gCnvCHSEFTRec.Detail = Mid(strInRec, 1, intInRecLen) + Mid(strSpacesRec, intStartPtr, intRemainLen)
        gCnvCHSEFTRec.EndOfLine = vbCrLf
        strOutRec = gCnvCHSEFTRec.Detail + gCnvCHSEFTRec.EndOfLine
        Write #gOUT_CHS_EFT_FILE_NBR, strOutRec
    Loop
       
    Close #gIN_CHS_EFT_FILE_NBR
    Close #gOUT_CHS_EFT_FILE_NBR

Please show me what I need to do to correct my logic...
0
Comment
Question by:seckel
8 Comments
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
>>  Is there a way to read sequentially the entire record ignoring the comma delimitation?

Try using :
strInRec = Input(lof(Input #gIN_CHS_EFT_FILE_NBR) ,#gIN_CHS_EFT_FILE_NBR)

this will read the full file in a string ... caution a string can handle only 64KB if your file is bigger you have to read it in smaller chunks
0
 
LVL 12

Accepted Solution

by:
dfiala13 earned 500 total points
Comment Utility
Check out the TextStream objects in the scripting runtime library (which is probably installed on your machine). the dll is scrrun.dll.

It is much more adept at working with files and can read lines without worrying about embedded characters.  This should get you close...

Dim fso As FileSystemObject
Dim tsIn As TextStream
Dim tsOut As TextStream
Dim strInRec As String
Dim gCnvCHSEFTRec As CnvCHSEFTRec
Dim strInCHSEFTFileName As String
Dim strOutCHSEFTFileName As String
Dim strSpacesRec As String
Dim intInRecLen As Integer
Dim intStartPtr As Integer

    Set fso = New FileSystemObject
    Set tsIn = fso.OpenTextFile(strInCHSEFTFileName, ForReading)
    Set tsOut = fso.OpenTextFile(strOutCHSEFTFileName, ForWriting, True)
    Do Until tsIn.AtEndOfStream
        'this also strips the CRlf
        strInRec = tsIn.ReadLine
        intStartPtr = intInRecLen + 1
        gCnvCHSEFTRec.Detail = ""
        strSpacesRec = gCnvCHSEFTRec.Detail
        intRemainLen = Len(gCnvCHSEFTRec.Detail) - intInRecLen
        gCnvCHSEFTRec.Detail = Mid(strInRec, 1, intInRecLen) + Mid(strSpacesRec, intStartPtr, intRemainLen)
        'this adds the Crlf
        tsOut.WriteLine gCnvCHSEFTRec.Detail
    Loop
    tsIn.Close
    tsOut.Close
0
 

Author Comment

by:seckel
Comment Utility
mmusante

Being limited to 64K is problem for the entire file, but not the entire record.
I don't want to read in the entire file, just the entire record of each record.  Do you know of a way to do this?
0
 
LVL 26

Expert Comment

by:EDDYKT
Comment Utility
Try this

Dim ff As Integer, s As String
Dim arr
Dim gCnvCHSEFTRec As CnvCHSEFTRec

'I need to read sequentially the entire record into one string variable that will contain the entire record.
ff = FreeFile
Open Filename For Binary Access Read Lock Read Write As #ff
s = Space$(LOF(ff))
Get #ff, , s
Close ff

'I need to strip off the ending CrLf.
arr = Split(s, vbCrLf)

' open file
For i = 0 To UBound(arr)
gCnvCHSEFTRec.Detail = Space(111)
Mid(gCnvCHSEFTRec.Detail, 1, Len(arr(i))) = arr(i)
'write to file
Next
'close file
Erase arr
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 17

Expert Comment

by:inthedark
Comment Utility
May I  propose a small enhancement to EDDYKT's solution....

At this point:

' open file

Change below to:


For i = 0 To UBound(arr)
    gCnvCHSEFTRec.Detail = arr(i) ' this will pad spaces to end when written
    gCnvCHSEFTRec.EndOfLIne = vbCRLF ' add end of line character
    'write to file, calculate byte position for record using UDT length
    Put #ff, I * len(gCnvCHSEFTRec) + 1,  gCnvCHSEFTRec
Next

Close #ff

Erase arr
0
 

Author Comment

by:seckel
Comment Utility
EDDYKT  and inthedark:

I tried both suggestions, but I haven't got this working yet.  I'm just a bit confused.  I've never used a binary file before.  I'm used to reading in a file and writing out to a new file, but here it looks like I'm reading and writing to the same file.  Is that a correct assumption?
0
 
LVL 12

Expert Comment

by:dfiala13
Comment Utility
Why you continue you to attempt to solve this using the very rudimentary native file access capabilities afforded by VB while the MS scripting library (objects built to undertake such tasks) are left unused is beyond me.

The text streams can be read line-by-line no matter what characters they contain
the readline method automatically strips the crlf from the end of lines
the writeline method automatically adds the crlf to the end of lines when writing to a new file.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Try this simple function:

Private Sub Convert(ByVal InFileName As String, ByVal OutFileName As String)
Dim InHandle As Integer
Dim OutHandle As Integer
Dim InBuffer As String
Dim OutBuffer As String

InHandle = FreeFile
Open InFileName For Input As InHandle

OutHandle = FreeFile
Open OutFileName For Output As OutHandle
OutBuffer = Space$(111)

Do While Not EOF(InHandle)
   Line Input #InHandle, InBuffer
   LSet OutBuffer = InBuffer
   Print #OutHandle, OutBuffer
Loop
Close #OutHandle
Close #InHandle

End Sub

There is no need to code for CRLF's:
the Line Input # statement automatically strips the crlf from the end of lines
the Print # statement automatically adds the crlf to the end of lines when writing to a new file.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now