consolidate a text file down -
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 IntegerDim sQuant As StringDim sCode As StringDim ParseLine As VariantDim sTemp As StringDim iTemp As IntegerDim index As IntegerDim c As IntegerDim sLine As StringDim sOutLine As StringDim MyList() As VariantDim sOrgFile As StringDim sNewFile As StringDim iFileNum As IntegerDim bFound As Boolean' initialise variablesindex = 0c = 0sLine = ""sOutLine = ""sTemp = ""iTemp = 0sOrgFile = "\Consolidate file\original.txt"sNewFile = "\Consolidate file\new.txt"iFileNum = FreeFilebFound = False' open original file to read it inOpen (sOrgFile) For Input As #iFileNumDo 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 LoopClose #iFileNum' open new file to output to itiFileNum = FreeFileOpen (sNewFile) For Output As #iFileNumiQuant = 0: sQuant = "": sCode = "": sOutLine = ""' loop through array and write to fileFor c = 0 To UBound(MyList) + 1 iQuant = MyList(1, c) sQuant = CStr(iQuant) sCode = MyList(0, c) sOutLine = sQuant & "No. " & sCode Print #iFileNum, sOutLineNextClose #iFileNumYou 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.
页:
[1]