Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-04-08
8
Medium Priority
?
225 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 6

Expert Comment

by:mmusante
ID: 10782941
>>  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 2000 total points
ID: 10783127
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
ID: 10783364
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:EDDYKT
ID: 10783401
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
 
LVL 17

Expert Comment

by:inthedark
ID: 10789837
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
ID: 10790586
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
ID: 10791202
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
ID: 10803344
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

670 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