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: Loop two files, compare, extract, and report
Topic Summary: Loop two files, compare, extract, and report
Created On: 06/17/2014 10:38 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.
 06/17/2014 10:38 AM
User is offline View Users Profile Print this message

Author Icon
mctimoth
Journeyman (50-99)

Posts: 66
Joined: 05/04/2007

I need to read from 2 files. The first one called PRODUCTS is part numbers, and email addresses, with comma separated fields and new-line separated records, with at least one record and will grow to many records. The other is called ORDERS, with column delimited fields and new-line separated records with thousands of records. I need to take the part number in PRODUCTS and find its match, possibly many, in ORDERS, find the order number and product name associated with that record and email that information to the email addresses listed in PRODUCTS. I am using an existing task that already successfully parses a file and reports on the contents. This time I need to be able to search the ORDERS file for every part number listed in PRODUCTS. I have successfully parsed both files such that the task takes the first part number from the first record and finds that part number once or more times in ORDERS but then the task has to reparse the whole ORDERS files for each of the next records in PRODUCTS. I need to be able to aggregate all of the possible product numbers from PRODUCTS and then parse each record from ORDERS for all the part numbers at once. Here's the task list that parses the ORDERS file sequentially:



 Category Survey
AutoMate 7 version: 7.1.1
Windows version: Windows 2003



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 06/17/2014 04:24 PM
User is offline View Users Profile Print this message

Author Icon
Alex Escalante
HelpSystems

Posts: 871
Joined: 02/26/2014

Hello McTimoth,

What kind of files are you reading from? If, for example, they happen to be excel files, you can use a "get value" to get the part numbers for that column....

 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Alex Escalante | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
 06/17/2014 07:04 PM
User is offline View Users Profile Print this message

Author Icon
mctimoth
Journeyman (50-99)

Posts: 66
Joined: 05/04/2007

Both files are text files. My problem is how to aggregate all the part numbers from the PRODUCTS file and search for them in the ORDERS file during one pass.

 Category Survey
AutoMate 7 version: 7.1.1
Windows version: Windows 2003
 06/18/2014 03:27 PM
User is offline View Users Profile Print this message

Author Icon
Alex Escalante
HelpSystems

Posts: 871
Joined: 02/26/2014

Hello McTimoth,

Do the part numbers from the product file start with the same number(s) or letter(s)? IF so, we can use a "if text contains" to look for the specific part number. For example, if your part numbers always start with certain letters, you can search for those letters. For example if your part numbers look something like: xx2435-235, xx145-19818, xx13-12351 etc... then you can use the action "if text contains: xx* to look for the numbers. You can then store that information into a variable and use it to add to or compare against the second file.

Hope this information is helpful.

 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Alex Escalante | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
 06/19/2014 11:31 AM
User is offline View Users Profile Print this message

Author Icon
mctimoth
Journeyman (50-99)

Posts: 66
Joined: 05/04/2007

That is already what I am doing. If you'll check the associated task you can decipher that I pull the part number from the beginning of the line and store it and then parse the ORDERS file's thousands of lines for matches and when found I send and email then I go back to the PORDUCTS file and find the next part number on the next line and store it and parse the ORDERS file for matches again and again and again for as many part numbers as may be in the PRODUCTS file. That takes a long time. I want to get all the part numbers together and then parse the ORDERS file once. Maybe it's not possible.

Tim

 Category Survey
AutoMate 7 version: 7.1.1
Windows version: Windows 2003
 06/19/2014 11:40 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Perhaps an easier and quicker solution would be to loop through the products file to get the part number and then use a SQL query against the orders file so that it will return the results based on the part number instead of then looping the orders file as well. This would reduce the loop to only the products file. There is a video tutorial on using a SQL query against a .csv file here:

http://www.networkautomation.c...AA4-13EB4E34CB9724C8/



 Category Survey
AutoMate 7 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 06/19/2014 11:43 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Tim,
looping files in an embedded way is very cumbersome and slow, as you noticed.
Since your files are comma separated files , they could be queried using SQL. And SQL could take care of the relationships between both files.
Are you familiar with SQL ?

 Category Survey
AutoMate 7 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 06/19/2014 11:49 AM
User is offline View Users Profile Print this message

Author Icon
mctimoth
Journeyman (50-99)

Posts: 66
Joined: 05/04/2007

Familiar enough with SQL to get me in lots of trouble! I guess my question would be how to aggregate the part numbers from the PRODUCTS file. I imagine they would go into an array. But as the PRODUCTS file has more lines added the array would have to grow too so i would need a line count and set the array accordingly beforehand. I am not exactly sure of what that process would be.

 Category Survey
AutoMate 7 version: 7.1.1
Windows version: Windows 2003
 06/19/2014 01:34 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Tim,
could you post a sample of both files to see what they look like ?
And also from that sample what sort of email body you want to compose ?

 Category Survey
AutoMate 7 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 06/19/2014 01:56 PM
User is offline View Users Profile Print this message

Author Icon
mctimoth
Journeyman (50-99)

Posts: 66
Joined: 05/04/2007

Jean,

Sure,

Here's a little clarification. The PRODUCTS file has varPRTNUM_01 and the email addresses. The ORDERS file has varOrdersPartNumber and varOrdersOrderNumber. I match varPRTNUM_01 string to any matching string in varOrders.

The PRODUCTS first line looks like this:

35-000543-02,tim.g@company.com;first.last@company.com

It is comma separated so the part number (the part number could really be anything) is to the left of the comma and the email To list is to the right of the comma.

The ORDERS first line looks like this:

BO0005886375000009016 2013011520130115CL000000250000000025EVAL-ADXL345Z-DB 35-000543-02 201301152013011520130115++ZINTTAN CP0001TFR104HPI 0300000005133NSH0001000006ZVAC WNAS0002000129ETCRD20130115

It is column delimited and so you can see from the associated task list that the order number, varOrdersOrderNumber, starts at column 6 and is 7 characters long and the part number, varOrdersPartNumber starts at column 59 and could be 21 characters long and so I rtrim it. The next field happens to be the one that gets matched to varPRTNUM_01.

Hopefully I didn't screw something up.
Tim

 Category Survey
AutoMate 7 version: 7.1.1
Windows version: Windows 2003
 06/19/2014 11:27 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

OK, Tim.
I am missing what the output would look like in the email.
And what about when an order (say 5886375) contains several products (like say 35-000543-02 and 35-000544-03) ?


 Category Survey
AutoMate 7 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 06/20/2014 07:59 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Tim,
to give you the flavor of an SQL solution, I have made a sample that one can enrich at will.

 Category Survey
AutoMate 7 version:
Windows version:



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 06/23/2014 08:17 AM
User is offline View Users Profile Print this message

Author Icon
mctimoth
Journeyman (50-99)

Posts: 66
Joined: 05/04/2007

The email task is listed as attached. Do you need more?

 Category Survey
AutoMate 7 version: 7.1.1
Windows version: Windows 2003



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


Edited: 06/23/2014 at 08:46 AM by mctimoth
 06/23/2014 11:53 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Tim,
could you succeed in exercising by yourself the SQL task I outlined with using your own files ?
If you can, it will be easy for you to replace the message box with the send email.
If I missed something, tell me.


 Category Survey
AutoMate 7 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
Statistics
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 1564 guests browsing this forum, which makes a total of 1564 users using this forum.

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

Sitemap Network Automation Software Blog