乐筑天下

搜索
欢迎各位开发者和用户入驻本平台 尊重版权,从我做起,拒绝盗版,拒绝倒卖 签到、发布资源、邀请好友注册,可以获得银币 请注意保管好自己的密码,避免账户资金被盗
查看: 91|回复: 3

[编程交流] consolidate a text file down -

[复制链接]

12

主题

17

帖子

5

银币

初露锋芒

Rank: 3Rank: 3Rank: 3

铜币
60
发表于 2022-7-6 22:06:18 | 显示全部楼层 |阅读模式
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
回复

使用道具 举报

29

主题

519

帖子

477

银币

初露锋芒

Rank: 3Rank: 3Rank: 3

铜币
163
发表于 2022-7-6 22:45:32 | 显示全部楼层
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.
回复

使用道具 举报

29

主题

519

帖子

477

银币

初露锋芒

Rank: 3Rank: 3Rank: 3

铜币
163
发表于 2022-7-6 22:49:18 | 显示全部楼层
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.
 
  1. 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 #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
回复

使用道具 举报

106

主题

1万

帖子

101

银币

顶梁支柱

Rank: 50Rank: 50

铜币
1299
发表于 2022-7-6 23:11:53 | 显示全部楼层
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.
回复

使用道具 举报

发表回复

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

  • 微信公众平台

  • 扫描访问手机版

  • 点击图片下载手机App

QQ|关于我们|小黑屋|乐筑天下 繁体中文

GMT+8, 2025-6-29 02:27 , Processed in 2.382901 second(s), 71 queries .

© 2020-2025 乐筑天下

联系客服 关注微信 帮助中心 下载APP 返回顶部 返回列表