?
Solved

file

Posted on 2005-05-03
14
Medium Priority
?
183 Views
Last Modified: 2010-05-02
I have 4 text files in a directory. These are fixed length files and they have fields, which start at the same position. I mean the column names and their starting / ending position is the same , but the values will differ. All fields are either numeric (tight justified, left zero filles) or alphanumeric(left justified and right blank filled). I have given column names – just for clarity sake. In reality, these files do not display the  column names, but we can find that out since I know that the second column starts at positon 10.. it is sorted by id in every txt file.


Example of first txt file:

Id       name           number

01      tim              BY000

example for second txt file

id       name          number
   
13      donny         123GH


My aim is to write a function in VB 6.0 to take the contents of the 2nd , 3rd and 4th txt file and copy  it into  the  first txt file and then sort by id again… Kindly help.
0
Comment
Question by:Sara_j_11
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 7

Expert Comment

by:TheMCSE
ID: 13922117
Are all of the IDs globally unique (or the entire line)?  Do you want to copy the data from the 2nd, 3rd, and 4th files, or move it (if this process were to run again, there would be duplicates).  Based on the information you provided, I don't think that the other fields would be relevant to crafting this solution.  Are you opposed to using COM?  Realistically this could be created in VBS relatively easily, but the code should still work in VB.
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 13922339
Actually, you can do this is a batch file with only two commands if you'd prefer...

filename = combine.bat,  contents as follows:

   copy  file1.txt + file2.txt + file3.txt  tempfile.txt
   sort /r+1 tempfile.txt  /oNewFile.txt

This sill combine the 3 input files into a new one named "tempfile.txt" then sort it starting with column 1 creating a final output file named "NewFIle.txt"

You can add a third line to the batch file "del tempfile.txt" for cleanup if desired.
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 13922359
4 files... statement should have been:  copy file1.txt + file2.txt + file3.txt + file4.txt  tempfile.txt

Note, you can also build this batch file dynamically from within VB and shell.exec it for coding simplicity... I think you probably have to sort it that way... I'm not aware of any built-in filesort capabilities within VB or FSO.

-- Lynn
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!

 
LVL 7

Expert Comment

by:TheMCSE
ID: 13922578
The simplicity of batch is a beautiful thing :)  If you still want to do with VB(S), you could use something like this:


'begin
Option Explicit

Dim arrFile1, intCount, intCount2, objFSO, objFile1, strFile2, strFile3, strFile4, strLine, strTemp

Set objFSo = CreateObject("Scripting.FileSystemObject")
Set objFile1 = objFSO.OpenTextFile("file1.txt", 8)
strFile2 = objFSO.OpenTextFile("file2.txt").ReadAll
strFile3 = objFSO.OpenTextFile("file3.txt").ReadAll
strFile4 = objFSO.OpenTextFile("file4.txt").ReadAll

objFile1.Write vbCrLf & strFile2 & vbCrLf & strFile3 & vbCrLf & strFile4
objFile1.Close

arrFile1 = Split(objFSO.OpenTextFile("file1.txt").ReadAll, vbCrLf)

For intCount = (UBound(arrFile1) - 1) To 0 Step -1
    For intCount2 = 0 To intCount
        If UCase(arrFile1(intCount2)) > UCase(arrFile1(intCount2 + 1)) Then
            strTemp = arrFile1(intCount2 + 1)
            arrFile1(intCount2 + 1) = arrFile1(intCount2)
            arrFile1(intCount2) = strTemp
        End If
    Next
Next

Set objFile1 = objFSO.CreateTextFile("file1.txt", , True)
For Each strLine In arrFile1
    objFile1.WriteLine strLine
Next

MsgBox "Completed combining and sorting files.", vbInformation, "Execution completed"
'end

As JesterToo alluded to, there isn't a built-in sort functionality (none that I am aware of), but the good old-fashioned bubble sort should suffice.  This takes the datas from files 2, 3, and 4 (very original naming, no?) and writes it all to file 1; once this is complete, it reads all of file 1, sorts it, and rewrites it all to file 1.  Good luck!
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 13923640
Unless you know the record count to be sorted is very low (like less than 10,000 records) you don't want to use the bubble sort algorithm... this is the least efficient of all the sorting algorithms.  Choose a "shell, quick, or o-sort" algorithm instead.  Here's a quick comparison of the three:  I don't have discrete measurements for o-sort on these examples because it varies considerably based on how "sorted" the data already is.

# rows      Bubble            Shell              Quick
--------      --------           ------              ------
25                45 µSec        50 µSec           55 µSec
300           6000 µSec     1500 µSec       1000 µSec
5000           1.4 Sec        .035 Sec            .02 Sec
70000           10 Min         1.3 Sec              .5 Sec

You can see that the times for the Bubble sort rapidly increase with the number of rows being sorted.  There is another little known sort algorithm, the O-Sort (Oosterval Sort) which is only about 1.5 times as long as the Quick sort when the  data is fairly random.  However, if the data is already mostly in order then the sort time is about half as long as Quick sort.  Be advised that all these sorts are for "in memory" data... i.e. arrays.  It would require a masive undertaking to use these algorithms on data held in files if that data consisted large numbers of records and/or large record sizes.
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 13925732
with the caveats that JesterToo was refering to regarding massive files, you could  read the file into a 2d string array. In this PAQ, I have vb code that sorts a 2d array using the quicksort algorithm:

http://www.experts-exchange.com/AH_1945296.html
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 13925744
0
 

Author Comment

by:Sara_j_11
ID: 13927202
Can you please elaborate more on how to include the batch file logic in my program. Not familiar wit this process.
Kindly elaborate on how to do this.
Note, you can also build this batch file dynamically from within VB and shell.exec it for coding simplicity...
how do I acheive this:
copy  file1.txt + file2.txt + file3.txt  tempfile.txt
   sort /r+1 tempfile.txt  /oNewFile.txt
Sorry if it is a newbie question , but I am just a beginer..
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 13933323
Ok, here is a fairly complete VB6 program... lots of code (a bit over 200 lines) but it seems to work ok for me...

Attribute VB_Name = "ConcatSort"

'*****************************************************************************************
' ConcatSort.bas
'
'   Reads the command line for the names of the files to concatenate together and the
'   name of the file to create.  The last named file is the file to create.
'
' Sample usage:
'    ConcatSort /iFile1.txt /iFile2.txt /iFile3.txt /iFile4.txt /ttempfile.txt /oalltext.txt /c2 /bsorttest.bat
'
' Note:  Filenames with embedded spaces need to be enclosed in either single or double quotes.
'    ConcatSort "/iFile1 is here.txt" /iFile2.txt /iFile3.txt /iFile4.txt /ttempfile.txt /oalltext.txt /c2 '/bsorted data.bat'
'
' Switches can be specified on any order and any case...
'
'    /I     -    specifies Input file name... can have as many as can fit on command line.  Each file requires another switch.
'    /O    -    specifies Output filename (final sorted file)... should only be one of these.
'    /T    -    specifies temporary intermediate filename to contain the combined files prior to sorting
'    /B    -    specifies batch filename to generate
'    /C    -    specifies the column name in the combined file to begin the sorting operation
'    /D    -    specifies Ascending or Descending sort... values are A or D
'
'   Only the /I switch can legally exist multiple times, the /D switch defaults to Ascending, the /C switch defaults to 1.  The
'   batch file and the temporary file are both deleted when finished.
'*****************************************************************************************

Option Explicit

Dim sParam      As String
Dim sCmdLine    As String
Dim nCmdLineLen As Integer
Dim nPtr        As Integer
Dim oFSO        As Object

Sub Main()
Dim InpFile()   As String
Dim sSwitch     As String
Dim sCurSwitch  As String
Dim sSwitchVal  As String
Dim sTempFile   As String
Dim sOutFile    As String
Dim nSortColumn As Integer
Dim sSortDirect As String
Dim sBatFile    As String
Dim sString     As String
Dim n           As Integer
Dim nCount      As Integer
Dim oBatFile    As Object


   sCmdLine = Command()
   nCmdLineLen = Len(sCmdLine)
   nPtr = 1

   '-- find out how many input files there are...
   Do While nPtr < nCmdLineLen
      sParam = GetNextParam()
      If UCase$(Left$(sParam, 2)) = "/I" Then
         nCount = nCount + 1
      End If
   Loop

   If nCount > 0 Then
      ReDim InpFile(nCount - 1)
      nCount = 0
   Else
      Debug.Print "No input files specified... terminating now."
      End
   End If

   Set oFSO = CreateObject("Scripting.FileSystemObject")
   nPtr = 1

   Do While nPtr < nCmdLineLen
      sParam = GetNextParam()
      If LenB(sParam) > 0 Then
         sSwitch = UCase$(Left$(sParam, 2))
         sSwitchVal = Trim$(Mid$(sParam, 3))
         If Left$(sSwitchVal, 1) = "'" Or Left$(sSwitchVal, 1) = Chr$(34) Then
            sSwitchVal = Trim$(Mid$(sSwitchVal, 2, Len(sSwitchVal) - 1))
         End If
         Select Case sSwitch
            Case "/I"
               If LenB(sSwitchVal) > 0 Then
                  InpFile(nCount) = FixUp(sSwitchVal, True)
               End If
               nCount = nCount + 1
            Case "/T"
               If LenB(sSwitchVal) > 0 Then
                  If LenB(sTempFile) > 0 And UCase$(sOutFile) <> UCase$(sSwitchVal) Then
                     Debug.Print "Multiple temp filenames specified... last one will be used"
                  End If
                  sTempFile = FixUp(sSwitchVal)
               End If
            Case "/O"
               If LenB(sSwitchVal) > 0 Then
                  If LenB(sOutFile) > 0 And UCase$(sOutFile) <> UCase$(sSwitchVal) Then
                     Debug.Print "Multiple output filenames specified... last one will be used"
                  End If
                  sOutFile = FixUp(sSwitchVal)
               End If
            Case "/C"
               If LenB(sSwitchVal) > 0 Then
                  If nSortColumn <> 0 Then
                     nSortColumn = CInt(sSwitchVal)
                  ElseIf nSortColumn <> CInt(sSwitchVal) _
                     And nSortColumn <> 0 Then
                     Debug.Print "Multiple sort columns specified... last one will be used"
                     nSortColumn = CInt(sSwitchVal)
                  End If
               End If
            Case "/D"
               sSwitchVal = UCase$(Left$(sSwitchVal, 1))
               If LenB(sSwitchVal) > 0 Then
                  If LenB(sSortDirect) > 0 Then
                     sSortDirect = sSwitchVal
                  ElseIf sSwitchVal = "A" Or sSwitchVal = "D" Then
                     If sSortDirect <> sSwitchVal Then
                        Debug.Print "Sort direction specified multiple times... last one will be used"
                        sSortDirect = sSwitchVal
                     Else
                        Debug.Print "Invalid sort direction specified... defaulting to A"
                        sSortDirect = "A"
                     End If
                  End If
               End If
            Case "/B"
               If LenB(sSwitchVal) > 0 Then
                  If LenB(sBatFile) > 0 And UCase$(sBatFile) <> UCase$(sSwitchVal) Then
                     Debug.Print "Multiple BAT filenames specified... last one will be used"
                  End If
                  sBatFile = FixUp(sSwitchVal)
               End If
             Case Else
                  Debug.Print "Unknown command line parameter... ignoring."
         End Select
      End If
   Loop

   Set oBatFile = oFSO.CreateTextFile(sBatFile)

   oBatFile.WriteLine "@echo off"
   sString = "COPY /Y "
   For n = 0 To UBound(InpFile)
      If LenB(InpFile(n)) > 0 Then
         sString = sString & InpFile(n) & " + "
      End If
   Next
   sString = Left$(sString, Len(sString) - 2)

   oBatFile.WriteLine sString & sTempFile

   sString = "SORT "
   If nSortColumn <> 0 Then
      sString = sString & "/+" & CStr(nSortColumn) & " "
   End If
   If sSortDirect = "D" Then
      sString = sString & "/R" & " "
   End If
   sString = sString & sTempFile & " /O " & sOutFile
   oBatFile.WriteLine sString
   oBatFile.WriteLine "DEL " & sTempFile
   oBatFile.Close

   CreateObject("Wscript.Shell").Run sBatFile
'   Kill (sBatFile)

   Set oBatFile = Nothing
   Set oFSO = Nothing

End Sub


Function FixUp(sFileName, Optional bCheckExist)

   If Not IsMissing(bCheckExist) Then
      If bCheckExist Then
         If Not oFSO.FileExists(sFileName) Then
            Debug.Print "File " & sFileName & " doesn't exist... aborting!"
            End
         End If
      End If
   End If

   If InStr(sFileName, " ") Then
      sFileName = Chr(34) & sFileName & Chr(34)
   ElseIf InStr(sFileName, Chr(34)) Then
      sFileName = "'" & sFileName & "'"
   ElseIf InStr(sFileName, "'") Then
      sFileName = Chr(34) & sFileName & Chr(34)
   End If

   FixUp = sFileName
End Function


Function GetNextParam()
Dim sChar  As String
Dim bQuote As Boolean
Dim sParam As String

   Do While True
      If nPtr > nCmdLineLen Then
         GetNextParam = sParam
         Exit Function
      End If
      sChar = Mid$(sCmdLine, nPtr, 1)
      nPtr = nPtr + 1
      Select Case sChar
         Case "'", Chr$(34)
            If bQuote Then
               GetNextParam = sParam
               Exit Function
            Else
               bQuote = True
            End If
         Case " "
            If bQuote Then
               sParam = sParam & sChar
            Else
               GetNextParam = sParam
               Exit Function
            End If
         Case Else
            sParam = sParam & sChar
      End Select
   Loop

End Function


HTH,
Lynn
0
 
LVL 22

Accepted Solution

by:
JesterToo earned 2000 total points
ID: 13933336
I just saw that I left a testing line in... uncomment the line that reads     '  Kill(sBatFile)
if you want it to delete the batch file.

Also, the first two lines of commentary in the "documentation block" at the top of the program aren't correct... That was written before I decided to use switches to identify the parameters.

-- Lynn
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 14510378
sweet
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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 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…
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

850 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