Solved

PowerShell - Unique text values

Posted on 2013-06-24
4
483 Views
Last Modified: 2013-06-24
We have reports daily that are delivered via an e-mail that is configured for both as examples: 123456789 and 987654321

I have set this up in VBS previously and works well..
unit1_123456789
unit2_123456789
unit1_987654321
unit2_987654321

So, the results of this would be two e-mails:
one to: 123456789
and contains the reports for:
unit1_123456789
unit2_123456789

one to: 987654321
and contains the reports for:
unit1_987654321
unit2_987654321

Now, I am trying to get this to work under power and I have played with -unique and select (there are no headers in the mapping.txt file either)..

$lists=Get-content D:\WORK\ps\Mapping.txt| Select $companyName,$EMail|OutData -NoTypeInformation -unique

##$lists=Get-content D:\WORK\ps\Mapping.txt| Select $lists.substring($lists.length -9, 9) -unique
foreach ($list in $lists)
{
    write-host $companyName
    write-host $EMail
    }

Open in new window


Mapping.txt contains:
unit1_123456789,recipient@company.com
unit2_123456789,recipient@company.com
unit1_987654321,recipient@company2.com
unit2_987654321,recipient@company2.com

Open in new window


In VBS, we are using..:

schema.ini contains as we use a SELECT DISTINCT..:
[Mapping.txt]
Format=CSVDelimited
Col1=companyName Text
Col2=eMail Text

Open in new window


VBS has:
' set objects
Set fso = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

' set some variables
ScriptDir = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
BkupFolder = ScriptDir & "ReportDownload\BkUp\"
ReportFileFolder = ScriptDir & "ReportDownload\"
TempFolder = ReportFileFolder & "Temp\"
ParentORG = "Business"

YYYY = Year(Date)
MM = Right(100 + Month(Date), 2)
DD = Right(100 + Day(Date), 2)
HH = Right(100 + Hour(Date), 2)
MN = Right(100 + Minute(Date), 2)
SS = Right(100 + Second(Date), 2)
DTSTR = YYYY & MM & DD & HH & MN & SS
MailResult = ScriptDir & "AppLog\" & DTSTR & "_MailResult.txt"
'(8) Read the Mapping.txt
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ScriptDir & ";" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited"""
objRecordset.Open "SELECT Distinct Right(companyName,9) as companyName,eMail FROM Mapping.txt", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
c= objRecordset.RecordCount
Logger "Number of companies found in the list: " & c
CNT = 0
Do Until objRecordset.EOF
	TSTStr = objRecordset.Fields.Item("companyName")
	Set objFolder = fso.GetFolder(TempFolder)
	Set colFiles = objFolder.Files
	For Each objFile In colFiles
		'FLPath = objFile.Path
		FLName = objFile.Name
		FULL_Name = Left(FLName, Len(FLName) - 4)
		FolderName = Right(FULL_Name, Len(FULL_Name) - Len(Split(FULL_Name, "_") (0)) - 1)
		If  Not fso.fileexists(BkupFolder & FolderName & "\" & FLName) Then
			If InStr(TSTStr, "Admin") > 0 Then
				For i = 0 To 1
					TSTStr = ParentORG & "Admin_" & i
					' -- This is required as files for Admin_1 and Admin_0
					' -- Will also get unit1_012345678 files for example
					' -- as it also contains Admin_0 in the ORG Name
					' -- Same with Admin_123456789
					If InStr(FLName, TSTStr & ".zip") > 0 Then
						FLList = FLList & TempFolder & FLName & ","
					End If
					Subject = ParentORG & "Admin"
				Next
			Else
				If InStr(FLName, TSTStr) > 0 Then
					FLList = FLList & TempFolder & FLName & ","
				End If
				Subject = TSTStr
			End If
		End If
	Next
	If Len(FLList) > 0 Then
		EMail = objRecordset.Fields.Item("eMail")
		If InStr(EMail, ";") Then
			EMail = Replace(EMail, ";", ",")
		End If
		Mailer EMail, Left(FLList, Len(FLList) - 1), Subject
		Logger Subject & " " & CNT & vbCrlf
		CNT = CNT + 1
	End If
	FLList = ""
	objRecordset.MoveNext
Loop

Open in new window



Thanks,

Kent
0
Comment
Question by:Kent Dyer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 39272162
Try some thing like..

$lists = Import-Csv D:\WORK\ps\Mapping.txt -Header companyName,Email | Select @{N="UniqueID";E={($_.companyName -Split "_")[1]}},companyName,Email

$lists | Group UniqueID | % {
Write-Host "`nUniqueID : $($_.Name)`n"
$_.Group | Select -ExpandProperty companyName
}

Open in new window

0
 
LVL 40

Expert Comment

by:footech
ID: 39272165
I'm not totally sure what you're after here.  Here's one way of looking at the mapping file, the results of which could be used to determine where an email is sent.  The Select statement is not needed unless there are duplicate lines in the mapping.txt file which you would like treated as one.
Import-Csv mapping.txt -Header CompanyName,Email | Select * -Unique

Open in new window


I'm not sure I would recommend converting VBScript to PowerShell just for conversion's sake.  If you're trying to learn, that's one thing, but otherwise I don't think it gets you much.
0
 
LVL 17

Author Closing Comment

by:Kent Dyer
ID: 39272194
Thank you!  Awesome..
0
 
LVL 17

Author Comment

by:Kent Dyer
ID: 39273794
@footech -
I'm not sure I would recommend converting VBScript to PowerShell just for conversion's sake.  If you're trying to learn, that's one thing, but otherwise I don't think it gets you much.

The problem is that vbs (VbScript) is a very old technology.

We are also being mandated by Executives to block scripts, run-times, etc.  For whatever reason they chose not to block PowerShell..

HTH,

Kent
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Synchronize a new Active Directory domain with an existing Office 365 tenant
This article will help you understand what HashTables are and how to use them in PowerShell.
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question