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: Extract email addresses from a 'spam report'
Topic Summary:
Created On: 03/15/2012 01:44 PM
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.
Answer This question was answered by RobertCox, on Tuesday, March 20, 2012 2:15 PM

Answer:
Hi Jean

Many thanks, I have this working now!
 03/15/2012 01:44 PM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi

I need to extract just the email email addresses contained in a 'Spam Report CSV file'.

Any assistance will be greatly appreciated...

The file usually looks like this (but can be bigger or smaller depending on spam complaints);



"User","Domain","Complaint Date","Complaint Source","Message Type","Date Sent","From","To","Subject","Message-ID","Sendmail ID","Server Outbound IP"
"enqsys@luxhv.com","luxhv.com","March 13, 2012 05:36:01 P.M.","Hotmail","Outbound SMTP: Processed","March 12, 2012 10:07:27 A.M.","sales@the-cruise-specialists.co.uk","washingtonsue@hotmail.com","From £589pp - Getaway with Royal Caribbean
","<201203121603.q2CG39Uh032429@rs85.luxhv.com>","q2CG3qMM032612","66.135.32.36"
"enqsys@luxhv.com","luxhv.com","March 13, 2012 05:12:07 P.M.","Hotmail","Outbound SMTP: Processed","March 13, 2012 08:21:29 A.M.","sales@the-cruise-specialists.co.uk","kloxtoby@hotmail.co.uk","£699 P&O Caribbean Cruise Getaway
","<201203131303.q2DD37w5002149@rs85.luxhv.com>","q2DD3veo002498","66.135.32.36"
"enqsys@luxhv.com","luxhv.com","March 13, 2012 04:07:38 P.M.","Hotmail","Outbound SMTP: Processed","March 13, 2012 08:22:27 A.M.","sales@the-cruise-specialists.co.uk","mary_ward40@hotmail.com","£699 P&O Caribbean Cruise Getaway
","<201203131312.q2DDC61U007447@rs85.luxhv.com>","q2DDCvVp007686","66.135.32.36"
"enqsys@luxhv.com","luxhv.com","March 13, 2012 02:47:04 P.M.","Hotmail","Outbound SMTP: Processed","March 13, 2012 08:16:51 A.M.","sales@the-cruise-specialists.co.uk","desanddi@hotmail.com","£699 P&O Caribbean Cruise Getaway
","<201203131236.q2DCaLgG018627@rs85.luxhv.com>","q2DCavCA018884","66.135.32.36"

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista
 03/16/2012 12:19 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Robert,
here also SQL is the answer.
Sample below, with your data
All the "To" values are in the resulting dataset "ds"

 Category Survey
AutoMate 6 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
 03/16/2012 02:53 AM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi Jean

I used your code as per;




Your test message box displays as per attached screenshot.

Fails on last step due to "Dataset Cannot Be Populated"

Thanks



 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista



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


Click for full image
Click for larger image
Click for full image
Click for larger image

 03/16/2012 03:18 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

I think you can use your file directly in its folder

 Category Survey
AutoMate 6 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
 03/16/2012 04:02 AM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi

I tried this and it failed on last step with message:

Microsoft JET Database Engine:Syntax error in FROM clause



 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista
 03/16/2012 05:25 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

This may be due to the dash (-) in the filename.
Try the step below as SQLQUERY where square brackets allow more flexibility in the filename

 Category Survey
AutoMate 6 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
 03/16/2012 10:03 AM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi

I tried your latest SQLQUERY step and got the message;

"Dataset Cannot Be Populated".

I also tried changing the filename to remove the dash and still got the same message.

I have attached a sample of the file itself.

Thanks

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista



 03/16/2012 11:01 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

The file is a utf-8 encoded file, that begins with the three utf-8 identification bytes Hexadecimal "ef bb ff"
Those three first bytes need being removed before querying. I do it and create a complaint-report2.csv file that is without those header bytes.

In addition, your csv is comma delimited. I have difficulties testing with it, since I have French locales, where csv files cannot be comma delimited. For my testing on your file I have to replace the commas (,) by semicolons (;), which won't work for you.
I attach my task, where I have commented out those specific steps of my testing which are not relevant to you.

I have tested with the commented steps enabled, and it works for me, in my French environment.

With the commented steps disabled (below) it should then work for you. Hopefully.

 Category Survey
AutoMate 6 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
 03/16/2012 01:58 PM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi Jean

I tried your new code (as below) but am still getting the "Dataset Cannot Be Populated" message?

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 03/16/2012 02:27 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Assuming the files schema.ini and complaint-report2.csv look nice in the folder (please check that with notepad)
Can you run alone in a task builder the step below, and tell me what it returns ?
Normally it is exactly the last step of the previous sequence, but with no reference to any variable.

 Category Survey
AutoMate 6 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
 03/20/2012 02:36 AM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi, I tried your suggestion and still got "Dataset Cannot be Populated".

The files schema.ini and complaint-report2.csv look ok to me but I have attached them for you to see (schema renamed for upload).


 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista


complaint-report2.csv
complaint-report2.csv  (2 KB)
schema.txt
schema.txt  (0 KB)

 03/20/2012 02:15 PM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Answer Answer
Hi Jean

Many thanks, I have this working now!

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista
 03/20/2012 09:52 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Happy you got it, Robert.

 Category Survey
AutoMate 6 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 994 guests browsing this forum, which makes a total of 994 users using this forum.

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

Sitemap Network Automation Software Blog