consolidate a text file down - VBA

Hi, I currently have a text file that contains a list of materials first part of the line is the quantity the next 7 characters is the material code. It looks like this:

1000No. Red Brk

10No. Oak Dor

30No. Flr Brd

2100No. Red Brk

15No. Oak Dor

45No. Flr Brd

900No. Red Brk

5No. Oak Dor

40No. Flr Brd

I would like to be able to be able to consolidate the list down adding all of the quantities together so that the list would turn into.

4000No. Red Brk

30No. Oak Dor

115No. Flr Brd

I originally tried looping through the file line by line checking a material code then when it gets to the end of the file it goes back to the start and checks for another material code , this worked but was very slow as it was looping through the file a couple of hundred times!

anyone got any pointers for a more efficient process in VBA?

Dan

Try creating a dynamic array and then read in your file line by line. Parse out the quantity and code and store in two variables, check if the code already exists in your array, if so add the quantity to that already in the array, if not add a new item to the array using "Redim Preserve" and insert the quantity. When you've done reading the file write the data from the array into a new file. This way you will only loop through your original file once.

Hi Dan,

Have you had time to try my suggestion out yet? I had a little bit of time this afternoon and cobbled an example together for you. It's basic with no bells and whistles and no error trapping but it works. You will need to put in the path to your files or incorporate file opening and saving dialogs.

Dim iQuant As Integer
Dim sQuant As String
Dim sCode As String
Dim ParseLine As Variant
Dim sTemp As String
Dim iTemp As Integer
Dim index As Integer
Dim c As Integer
Dim sLine As String
Dim sOutLine As String
Dim MyList() As Variant
Dim sOrgFile As String
Dim sNewFile As String
Dim iFileNum As Integer
Dim bFound As Boolean

' initialise variables
index = 0
c = 0
sLine = ""
sOutLine = ""
sTemp = ""
iTemp = 0
sOrgFile = "<PATH>\Consolidate file\original.txt"
sNewFile = "<PATH>\Consolidate file\new.txt"
iFileNum = FreeFile
bFound = False

' open original file to read it in
Open (sOrgFile) For Input As #iFileNum

Do While Not EOF(iFileNum)
   Line Input #iFileNum, sLine
   sLine = Trim(sLine)
   ParseLine = Split(sLine, ".", -1, vbTextCompare)
   sQuant = CStr(ParseLine(0))
   sQuant = Mid(sQuant, 1, Len(sQuant) - 2)
   iQuant = CStr(sQuant)
   sCode = CStr(ParseLine(1))
   sCode = Trim(sCode)
   
   ' for first line read in
   If index = 0 Then
       ReDim Preserve MyList(1, index)
       ' add first entry to list
       MyList(0, index) = sCode
       MyList(1, index) = iQuant
       bFound = True
       index = index + 1
   Else
       ' loop to see if code already exists
       For c = 0 To index - 1
           sTemp = MyList(0, c)
           If sTemp = sCode Then
               ' add quantity
               iTemp = MyList(1, c)
               iQuant = iQuant + iTemp
               MyList(1, c) = iQuant
               bFound = True
               Exit For
           End If
       Next
   End If
   
   If bFound = False Then
       
       ReDim Preserve MyList(1, index)
       ' add a new entry to list
       MyList(0, index) = sCode
       MyList(1, index) = iQuant
       index = index + 1
   End If
   
   bFound = False
   
Loop

Close #iFileNum

' open new file to output to it
iFileNum = FreeFile
Open (sNewFile) For Output As #iFileNum

iQuant = 0: sQuant = "": sCode = "": sOutLine = ""

' loop through array and write to file
For c = 0 To UBound(MyList) + 1
   iQuant = MyList(1, c)
   sQuant = CStr(iQuant)
   sCode = MyList(0, c)
   sOutLine = sQuant & "No. " & sCode
   Print #iFileNum, sOutLine
Next

Close #iFileNum

You don't need to implement this in AutoCAD VBA you could write a standalone app as it's pure data handling.

You will need to change the integer variables to type long to work with larger quantities. I just ran it on a 400 item original list and it completed in less than one second.

HTH

Maybe an alternative swap the qty and name then just do a sort on the data this way it will still be line by line but you just read a line adding up as you go and once name changes its total for that group.

FlrBrd 30No.

FlrBrd 45No.

FlrBrd 40No.

OakDor 10No.

OakDor 15No.

OakDor 5No.

RedBrk 1000No.

RedBrk 2100No.

RedBrk900No.