Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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

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
seckel
Asked:
seckel
1 Solution
 
mmusanteCommented:
>>  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
 
dfiala13Commented:
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
 
seckelAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
EDDYKTCommented:
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
 
inthedarkCommented:
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
 
seckelAuthor Commented:
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
 
dfiala13Commented:
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
 
Anthony PerkinsCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now