PowerShell - Unique text values

Posted on 2013-06-24
Medium Priority
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 2000 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

Bringing Advanced Authentication to the SMB Market

WatchGuard announces the acquisition of advanced authentication provider, Datablink, with one mission – to bring secure authentication to SMB, mid-market, and distributed enterprises with a cloud-based solution, ideal for resale via their established channel & MSSP community.

Question has a verified solution.

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

A brief introduction to what I consider to be the best editor for 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…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

800 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