Go Premium for a chance to win a PS4. Enter to Win


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
  • 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 41

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

Lessons on Wi-Fi & Recommendations on KRACK

Simplicity and security can be a difficult  balance for any business to tackle. Join us on December 6th for a look at your company's biggest security gap. We will also address the most recent attack, "KRACK" and provide recommendations on how to secure your Wi-Fi network today!

Question has a verified solution.

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

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses

773 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