Solved

PowerShell - Unique text values

Posted on 2013-06-24
4
478 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
  • 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 39

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
Synchronize a new Active Directory domain with an existing Office 365 tenant
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

910 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now