PowerShell working with Comma Separate File Data

Test File

 

Read the complete CSV file contents into a string:

Code:

$CSV2String  = Get-Content -path "C:\Temp\Test-File.csv"

write-host "[$CSV2String]"

 

Output:

[User001, DFdP, FmpD User002, weKC, v85F User003, va2W, gwkE User004, fBBn, dhey User005, cUmC, 9vpK User006, xESN, FDTc User007, FL4p, EpgS User008, 9EV9, GBTc User009, NtPA, NGDN User010, FKcY, 1NCL]

 

 

Read the CSV file line by line.  Print each of the CSV file line in its CSV format

Code:

$CSV2String  = Get-Content -path "C:\Temp\Test-File.csv"

foreach ($line in $CSV2String) {
    write-host "[$line]"

}

 

Output:

[User001, DFdP, FmpD]
[User002, weKC, v85F]
[User003, va2W, gwkE]
…

 

Remove any spaces in CSV data

Code:

$CSV2String  = Get-Content -path "C:\Temp\Test-File.csv"

foreach ($line in $CSV2String) {
    # use below code if you need to replace any spaces in the data of csv file
    $line_text = $line -replace " ", ""
    write-host "[$line_text]"

}

 

Output:

[User001,DFdP,FmpD]
[User002,weKC,v85F]
[User003,va2W,gwkE]
…

 

Print each of the data in CSV file line after removing spaces:

Code:

$CSV2String  = Get-Content -path "C:\Temp\Test-File.csv"
  
foreach ($line in $CSV2String) {
    # use below code if you need to replace any spaces in the data of csv file
    $line_text = $line -replace " ", ""
    $text      = $line_text -split ","
    foreach($string in $text) {
        Write-Host "[$string]"
    }
}

 

Output:

[User001]
[DFdP]
[FmpD]
[User002]
[weKC]
…

 

 

Operate on Data in CSV fields

Code:

foreach ($line in $CSV2String) {
    # use below code if you need to replace any spaces in the data of csv file
    $line_text = $line -replace " ", ""
    $text      = $line_text -split ","
    foreach($string in $text) {
        $Username    = $text[0]
        $Data1       = $text[1]
        $Data2       = $text[2]
        if ($Username -eq ‘User001′) {
            Write-Host "Processing [$Username]: User Data is: [$Data1] and [$Data2] "
        }
    }
}

 

Output:

Processing [User001]: User Data is: [DFdP] and [FmpD]
Processing [User001]: User Data is: [DFdP] and [FmpD]
Processing [User001]: User Data is: [DFdP] and [FmpD]

Filter Numbers and Text/Non-Numbers in Excel List

 

Formulas:

 

=IF(ISNUMBER(A1),A1,”")

=IF(ISTEXT(A1),A1,”")

 

Sample Data Set:

Sample Data List	IsNumber?	IsText?
Test1		Test1
5884578457	5884578457
Text1		Text1
54545046	54545046
String1		String1
656893450	656893450
String1		String1
5.64768E+13	56476804932048

 

 

Sample data Excel Sheet:

Excel-Filter-IsNumber-or-IsText.xlsx

DevGuru VBScript Method: FileSystemObject.OpenTextFile

METHOD: FileSystemObject.OpenTextFile

---

Implemented in version 2.0
object.OpenTextFile (filename [, iomode[, create[, format]]])
This method is used to open a text file and returns a TextStreamObject that can then be used to write to, append to, and read from the file.
The optional iomode argument can have one of the following Constants as its value:

CONSTANT	VALUE	DESCRIPTION
ForReading	1	Opens a file for reading only
ForWriting	2	Opens a file for writing. If the file already exists, the contents are overwritten.
ForAppending	8	Opens a file and starts writing at the end (appends). Contents are not overwritten.

The optional create argument can be either True, which will create the specified file if it does not exist, or False, which won’t.
The optional format argument uses one of the following Tristate values to specify in which format the file is opened. If not set, this defaults to TristateFalse, and the file will be opened in ASCII format.

CONSTANT	VALUE	DESCRIPTION
TristateTrue	-1	Opens the file as Unicode
TristateFalse	0	Opens the file as ASCII
TristateUseDefault	-2	Use default system setting

The following example will open the file, "c:\somefile.txt" (or create it if it does not exist), and append the specified text to it.
Code:
<%
dim filesys, filetxt
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set filesys = CreateObject("Scripting.FileSystemObject")
Set filetxt = filesys.OpenTextFile("c:\somefile.txt", ForAppending, True)
filetxt.WriteLine("Your text goes here.")
filetxt.Close
%>

Source: DevGuru VBScript Method: FileSystemObject.OpenTextFile

Fix: Input past end of file

 

Error: Input past end of file

 

 

Error Causing Code:

 

<script type="text/javascript">
    function readfile(){
        var line
        var fso = new ActiveXObject("Scripting.FileSystemObject");
        file = fso.OpenTextFile("C:\\Temp\\File1.txt", 1);
        var User = getuserName();
        while (!file.AtEndOfStream) {
            line = file.ReadLine();
            return line
        }
        file.Close();
    }

    function getuserName() {
        var line
        var fso = new ActiveXObject("Scripting.FileSystemObject");
        file = fso.OpenTextFile("C:\\Temp\\users.txt", 1, false);
        line = file.ReadLine();
        return line
        file.Close();
    }
</script>

 

Fixed Code:

 

<script type="text/javascript">
    function readfile(){
        var line
       var User = getuserName();
        var fso = new ActiveXObject("Scripting.FileSystemObject");
        file = fso.OpenTextFile("C:\\Temp\\File1.txt", 1);
        while (!file.AtEndOfStream) {
            line = file.ReadLine();
            return line
        }
        file.Close();
    }

    function getuserName() {
        var line
        var fso = new ActiveXObject("Scripting.FileSystemObject");
        file = fso.OpenTextFile("C:\\Temp\\users.txt", 1, false);
        line = file.ReadLine();
        return line
        file.Close();
    }
</script>

 

 

Explanation:

In  the first code example: you have Scripting.FileSystemObject open for file read, and before closing that file handle attempting to open another Scripting.FileSystemObject though in a different function which causes the file read buffer issues.  Once you finish up file operations only then attempt for instantiating another instance.

 

References:

1. Input past end of file
2. AtEndOfStream Property (FileSystemObject)

PowerShell Script to Auto generate Random Simple String Passwords

Script:

cls

$Private:OFS = ""

$PasswordLength = 8

$InclChars = ‘abcdefghkmnprstuvwxyzABCDEFGHKLMNPRSTUVWXYZ123456789′

$RandomNums = 1..$PasswordLength | ForEach-Object { Get-Random -Maximum $InclChars.length }

$RandomPassowrd = [String]$InclChars[$RandomNums]

Write-Host $RandomPassowrd

 

 

Output:

cAbfE1GN

Ea5kMabu

spx3sFW7

 

 

Script:

PowerShell Output Field Separator ($OFS variable)

$OFS which is a special variable in Powershell. OFS stands for Output Field Separator. It exposes the .Net’s Systems.String object. as shown below

 

You would use this to separate objects in your array

For example:

If you run the below code to display objects in $array you’ll see the objects displayed one in each new line as that is the default object separator:

CODE:

$array = (1,3,5,7)
$array

 

OUTPUT:

1
3
5
7

 

 

By using $OFS variable, you’ll be able to specify your own separator character as shown below:

Note: as the OFS operates on strings, you need to convert the objects in array into String by using the type casting as in below example

CODE:

$array = (1,3,5,7)
$OFS =’^';[string]$array

OUTPUT:

1^3^5^7

 

 

An alternate to specify your string objects separator at any point in time of your code is by referring the OFS variable as private.

CODE:

$private:ofs=">"
$array = (1,3,5,7)
[string]$array

OUTPUT:

1^3^5^7

Windows PowerShell 2.0 – The Get-Random Cmdlet

 

The information in this article was written against the second Community Technology Preview (CTP2) of Windows PowerShell 2.0. This information is subject to change in future releases of Windows PowerShell 2.0.

Randomize Your Results with the Get-Random Cmdlet

No doubt when it came time for the 2008 Winter Scripting Games many of you thought, “Gee, I’d love to enter the Scripting Games, except for one thing: the solutions to the Scripting Games often require you to use random numbers, and Windows PowerShell doesn’t have a cmdlet that makes it a snap to generate random numbers. If PowerShell had a cmdlet for generating random numbers, why, I’d enter the Scripting Games in a heartbeat!” Well, we’ve got good news for those of you who’ve been waiting for a random number generator to be added to Windows PowerShell: just wait until you see Get-Random, a new cmdlet added to the Windows PowerShell 2.0 of PowerShell.

Looks like we’ll be seeing you at the 2009 Winter Scripting Games, eh?

Of course, Get-Random does have uses beyond just helping you rack up points in the Winter Scripting Games. Before we discuss those uses, however, let’s take a moment to explain how Get-Random actually works. In its simplest form, you can call Get-Random without any additional parameters; in turn, Get-Random will generate a random integer between 1 and 2,147,483,647. In other words:

Copy

PS C:\Scripts> Get-Random

965837031

And there you have it.

Being able to generate a random number between 1 and 2,147,483,647 is useful for the Scripting Guys; after all, that helps them keep track of the money in their checking account. More often than not, however, you’ll want to place slightly-stricter limits on the random numbers that get generated; for example, you might want to choose a random number between 1 and 100. That’s fine; Get-Random lets you do just that, provided you include the –minimum and –maximum parameters:

Copy

PS C:\Scripts> Get-Random -minimum 1 -maximum 101

And yes, -maximum must always be set to 1 more than the high number in the range. That means that, to select a random number between 1 and 100, we must set –maximum to 101; if we set –maximum to 100 then we’d actually select a random number between 1 and 99.

By contrast, -minimum is always set to the actual low end of the range. What if we wanted to select a random number between 200 and 299? Then we’d use this command:

Copy

PS C:\Scripts> Get-Random -minimum 200 -maximum 300

And so on.

But here’s the really cool thing about Get-Random: it can do more than just generate random numbers. For example, you can use Get-Random to randomly select from a list of names:

Copy

($a = "Dasher","Dancer","Prancer","Vixen","Comet","Cupid","Donder","Blitzen" ) | Get-Random

All we’ve done here is create an array named $a and then pipe that array to Get-Random; in turn, Get-Random will randomly select one of the items in the array:

Copy

Vixen

Incidentally, we could perform this same task by using the –input parameter rather than by creating an array:

Copy

Get-Random -input "Dasher","Dancer","Prancer","Vixen","Comet","Cupid","Donder","Blitzen"

Oh, and here’s a cool thing. What if you wanted to randomly select three reindeer? That’s easy; just add the –count parameter, like so:

Copy

($a = "Dasher","Dancer","Prancer","Vixen","Comet","Cupid","Donder","Blitzen" ) | Get-Random -count 3

That’s going to give us output similar to this:

Copy

Prancer

Donder

Dancer

You say that you don’t have an array of reindeer names, instead you have all the reindeer names in a text file like this one:

Copy

Dasher

Dancer

Prancer

Vixen

Comet

Cupid

Donder

Blitzen

That’s fine; just use the Get-Content cmdlet to read in the contents of the text file, then pipe that information to Get-Random:

Copy

Get-Content C:\Scripts\Test.txt | Get-Random

In this case Get-Random will randomly select a line from the text file:

Copy

Cupid

As far as we know, Get-Random handles pretty much any data you throw at it. For example, suppose, for testing purposes, you need to randomly select three files from the folder C:\Scripts. This command should do the trick:

Copy

Get-ChildItem C:\Scripts | Get-Random -count 3

In other words:

Copy

Mode LastWriteTime Length Name

—- ————- —— —-

-a— 7/30/2007 11:33 AM 977 test1.vbs

-a— 10/24/2007 2:42 PM 32124 new_cmdlets.txt

-a— 7/24/2007 9:54 AM 273 identify_winrm.vbs

Etc., etc.

Top of page

Related Links

• Scripting with Windows PowerShell Home

Source: Windows PowerShell 2.0 – The Get-Random Cmdlet

JavaScript case insensitive string match

Code:

SELECT * FROM [SessionProfile].[dbo].[ReadableSessions] (nolock) where application like ‘%gmmev9%’

select u.userprincipalname, sp.stagename, sp.tmst from StageProgress sp (nolock)
inner join ServerProfile servp (nolock) on sp.SessionId = servp.ProfileId
inner join [User] u (nolock) on u.id = servp.UserId
where sp.tmst > DATEADD("MINUTE", -5, getdate()) and u.userprincipalname like ‘%gmmob002%’
order by sp.tmst desc, u.userprincipalname

 

 

Output:

JavaScript String Operations

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
    <body>
        <script type="text/javascript">
            var wshShell = new ActiveXObject("WScript.Shell")
            var sComputerName = wshShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
            var text_match = "GOVARDHAN"
           
            if(sComputerName.match(text_match))
            //if(sComputerName.match("govardhan"))
            {
                document.write("[" + sComputerName + "] String matched criteria: [" + text_match + "]");
            } else {
                document.write("String Match failed");
                //document.write("<br /><br />Would you like to try again?<br /><br />");
            }
        </script>
    </body>
</html>

 

 

Output:

HTA script to display computer name with compare operations

Code:

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <HTA:APPLICATION ID="A1" ICON="" APPLICATIONNAME="Test-HTA" sysMenu="no" navigable="yes" scroll="no" border="none" showintaskbar="yes" singleinstance="yes" innerborder="no">
    <title>Test-HTA</title>
</head>

<body>
    <script type="text/javascript">
        window.resizeTo((screen.width), (screen.height – 20))
        self.moveTo(0,0)
    </script>
   
    <script type="text/javascript">
        function computername()
        {
            var wshShell = new ActiveXObject("WScript.Shell")
            var sComputerName = wshShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
            if(sComputerName.toLowerCase() == "govardhan-pc".toLowerCase())
            {
                return "You are logged on to " + sComputerName + " system";
            }
            else
            {
                return "You are logged on to a different system";
            }

        }
    </script>
   
    <strong>
        <script type="text/javascript"> document.write(computername());</script>
    </strong>
</body>
</html>