PowerShell - Unique text values

Posted on 2013-06-24
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..

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

one to: 987654321
and contains the reports for:

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:

Open in new window

In VBS, we are using..:

schema.ini contains as we use a SELECT DISTINCT..:
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)
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"
				If InStr(FLName, TSTStr) > 0 Then
					FLList = FLList & TempFolder & FLName & ","
				End If
				Subject = TSTStr
			End If
		End If
	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 = ""

Open in new window


Question by:Kent Dyer
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
LVL 40

Accepted Solution

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

LVL 40

Expert Comment

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.
LVL 17

Author Closing Comment

by:Kent Dyer
ID: 39272194
Thank you!  Awesome..
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..



Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

This article will help you understand what HashTables are and how to use them in PowerShell.
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

724 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