Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Is there a way to consolidate this information?
Topic Summary: Consolidating orders for inventory
Created On: 03/31/2016 10:42 AM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
 03/31/2016 10:42 AM
User is offline View Users Profile Print this message

Author Icon
Journeyman (50-99)

Posts: 92
Joined: 05/20/2005

I have thousands of lines of text that I need to get a total quantity and total price for and input them all to one line. The total price shown is based on the quantity for that line. I have attached a text file (which is the same as below) because I don't recall the rules about that. Example

Starting File

Qty Item# Description TotalPrice
2 BM-00900T27Z Item1 $23.98
3 BM-00900T27Z Item1 $35.97
6 BM-00900T27Z Item1 $69.78
6 BM-00900T27Z Item1 $69.78
2 BM-00900T27Z Item1 $23.98
21 BM-00900T27Z Item1 $239.19
3 BM-00900T27Z Item1 $35.97
3 BM-00900T27Z Item1 $35.97
3 BM-00900T27Z Item1 $35.97
4 BM-00900T27Z Item1 $47.96
5 BM-00900T27Z Item1 $59.95
3 71003 Item2 $29.97
4 BLQ-029B Item3 $11.96
2 BLQ-029B Item3 $5.98
2 BLQ-029B Item3 $5.98
3 BLQ-029B Item3 $8.97
2 BLQ-029 Item4 $5.98
3 BLQ-029 Item4 $8.97
3 BLQ-029 Item4 $8.97
3 BLQ-029 Item4 $8.97

Resulting File

Qty Item# Description Total Price
58 BM-00900T27Z Item1 $678.50
3 71003 Item2 $29.97
11 BLQ-029B Item3 $33.89
11 BLQ-029 Item4 $32.88

 Category Survey

temp.txt  (1 KB)

 03/31/2016 04:17 PM
User is offline View Users Profile Print this message

Author Icon
Devin Cannon

Posts: 398
Joined: 03/17/2016

Hi Darin,

This can definitely be accomplished but will take some building.  I can hopefully give you some ideas to set you in the right direction.

First, I would suggest importing the temp file into Excel.  Once in Excel, we can use the "Excel: Cells to Dataset" AutoMate action to pull all the data from the file to a dataset inside AutoMate.  It may be worth mentioning, that AutoMate 10 has a "CSV to Dataset" action that would make this much easier.

Now that the data is stored inside a dataset, we can use the "Loop Dataset" action.  This is where the task gets more complicated.  I suggest creating a new Excel workbook to keep a running total of the quantity and price as the loop goes through the dataset.  The logic would be something like this:

Loop through the dataset from the CSV file.

Check to see if the item exists in the new Excel file already.  If it does, add the quantity to the existing quantity and add the (quantity x price) to the existing price for that item.  If the item does not exist in Excel already, create it and add the quantity and price.

Once Excel is populated with all the totals, it could be saved back to .txt or .csv if needed.


Please let me know if there's more I can assist you with.

 Category Survey
AutoMate 6 version:
Windows version:


Devin Cannon | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1037 guests browsing this forum, which makes a total of 1037 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog