Feeds:
Posts

## Powershell to copy data from CSV to Excel

I had a request to take data from a CSV file that was being automatically downloaded and copy a section of the data into an existing spreadsheet. The spreadsheet had 3 tabs and the copied data had to go to the tab labeled “Raw Data”. Then one of the other tabs would perform calculations and analysis on that raw data. I know I could manually copy the data but this process is already partially automated and the number of rows and columns involved were huge. There are at least 60 columns and over 10,000 rows of data that needed to be copied. So, to Powershell I go!!

I created a Powershell Script that would open both files and then select the data from the csv file and copy it.  Then the script would go to the Excel file and to the required tab before pasting the info.

Here is the final script.

$pathxls = “\\fileserver\data\myshare\sheets\workbook.xls”$pathcsv = “\\fileserver\data\myshare\sheets\Overview.csv”
$Excel = New-Object -ComObject excel.application$Excel.visible = $false$Workbook = $excel.Workbooks.open($pathcsv)
$Workbook2 =$excel.Workbooks.open($pathxls)$Worksheet = $Workbook.WorkSheets.item(“Overview”)$Worksheet.activate()
$range =$WorkSheet.Range(“A2:BC2”).CurrentRegion
$range.Copy() | out-null$Worksheet2 = $Workbook2.Worksheets.item(“RAW DATA”)$worksheet2.activate()
$range2 =$Worksheet2.Range(“A3:A3”)
$Worksheet2.Paste($range2)
$workbook2.Save()$workbook.close($false)$Excel.Quit()
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Now let’s dissect it a bit.

First we declare the variables for the two files $pathxls and$pathcsv and assign the path and file to each variable.

Then we declare a new com object which is an excel application.

We tell it that we don’t want Excel to be visible during the run of the script.

And we then set two workbook variables for the two files.

In the first file (the CSV) we tell it to go to the worksheet labled “Overview” and then make it the activate sheet.  Once there we select a range and copy it.

Then we jump over to the second worksheet and select the tab called “Raw Data” and make it the active sheet.

Once that is done we can paste in the data we’ve copied and then we can save the Excel workbook.  We close the csv file and tell it NOT to save since we don’t need to and then we can quit Excel.

Rather simple and to the point.  It gets the job done.  Now I can either run this manually or set it as a scheduled task.

## Out-printer cmdlet

Recently I was asked if you could use PowerShell to print to a specific printer.  I have written posts on how to print to your default printer but had never tried printing to a specific printer.  So I decided I should see if this is possible.

And what do you know there is a built in cmdlet called Out-Printer that accepts the -Name parameter.  Using this optional parameter allows you to specify an alternate printer.

OK so now that I’ve got that lets try it out.  I have a printer here in my office which is an HP 2600 color laserjet printer.  It is NOT my default printer so I would like to test printing to it using that cmdlet.

First lets just test the cmdlet without the optional parameter.

get-process | Out-Printer

Sure enough that prints the get-process output directly to my default printer.

Now lets try

get-process | Out-Printer -name \\Svr1\it-2600n

Sure enough this printed out to the HP 2600 color laser.  PERFECT!!!

Now there are a few problems with this cmdlet, the first being that if you specify the -name parameter and you are trying to print to a shared printer you need to use the UNC name of that printer.

The second problem is that this is a cmdlet that prints output.  It does not print the CONTENT of a file.  So for example if you wanted to print a txt document and you wrote Out-Printer “c:\test.txt” it would actually print out a page with c:\test.txt on it.

You could use the Get-Content cmdlet and pipe that into the Out-Printer cmdlet like this Get-Content “c:\test.txt” | Out-Printer

This would give you a printout of the actual contents of the test.txt file.  However, if you wanted to print a word document or a pdf document this would not work.  It will give you tons of garbage and not the actual file contents.

So you are back to the drawing board again.  To print a word or pdf document you need to use this command.

Start-Process -FilePath “C:\Test.Docx” -Verb Print

Which brings you back to the original issue that was made, how do I print to a printer that isn’t the default one?

And for the moment I have to say I don’t see a way.  I think you need to set the default printer first, then you can use the Start-Process cmdlet and print the document.

## Powershell function to get remote disk space

I was working on a project and needed to remotely check a servers available disk space so I started trying to come up with a simple reusable function to check the disk space.  The following is what I came up with.

Function GetDiskSpace

{
<#
.SYNOPSIS
GetDiskSpace displays the Total Size and Total Free Space of a Remote Computer Drive
.DESCRIPTION
See synopsis
.EXAMPLE
To run – enter GetDiskSpace
#>
$computer = Read-host "Please enter the computer name"$drive = Read-host "Please enter the drive letter including the colon (c:)"
$disk = Get-WmiObject Win32_LogicalDisk -ComputerName$computer -Filter "DeviceID=’$drive’" | Select-Object Size,FreeSpace "Remote computer:$computer drive letter $drive has {0:#.0} GB free of {1:#.0} GB Total" -f ($disk.FreeSpace/1GB),($disk.Size/1GB) | write-output } Set-alias GDS GetDiskSpace Read Full Post » ## Using PowerShell to query Active Directory I recently needed to find all disabled computers in my Active Directory Installation. So, rather than open up Active Directory Users and Computers and manually search through multiple OU’s and create a list I decided to use PowerShell. So, I started playing around with the command Search-ADAccount Using Get-Help Search-ADAccount I am able to determine that the syntax for a query is fairly simple. There are several switches for the command all of which are self explanatory. -AccountDisabled -AccountExpired -AccountExpiring -AccountInactive -LockedOut So, my first reaction was to run the command Search-ADAccount –AccountDisabled Which returned a long list of items with more information than I needed and in a format that was very hard to read. AccountExpirationDate : DistinguishedName : CN=Guest,CN=Users,DC=DOMAIN,DC=com Enabled : False LastLogonDate : LockedOut : False Name : Guest ObjectClass : user ObjectGUID : 1e4955ce-df97-4ecc-8a46-f4356ba2e6cc PasswordExpired : False PasswordNeverExpires : True SamAccountName : Guest SID : S-1-5-21-1058032114-1936565697-1108674531-501 UserPrincipalName : guest@DOMAIN.COM My next addition to the command was to pipe the results to Format-Table to make it easier to read. Search-ADAccount –AccountDisabled | Format-Table –AutoSize This was much better, now I could see that my list not only included computers but users as well. I didn’t want the users so I needed to look for another switch for the Search-ADAccount command. Tada! I found the switch –ComputersOnly which gives me exactly what I needed. But now I only want to see some of the columns in my table so lets modify the command a little more. Search-ADAccount –AccountDisabled -ComputersOnly | Format-Table –AutoSize Name, LastLogonDate, DistinguishedName This is perfect, now the results are readable and I can easily determine what computers in my environment are Disabled.  Name LastLogonDate DistinguishedName AUX-2B CN=AUX-2B,OU=OLD Computers,DC=DOMAIN,DC=com TECH-GC 10/5/2010 4:11:36 PM CN=TECH-GC,OU=OLD Computers,DC=DOMAIN,DC=com TS-2 8/19/2010 5:40:54 PM CN=TS-2,OU=OLD Computers,DC=DOMAIN,DC=com WPIX CN=WPIX,OU=Newark,DC=DOMAIN,DC=com CITRIX 5/5/2010 9:46:26 PM CN=CITRIX,OU=OLD Computers,DC=DOMAIN,DC=com SERVER-TEST 6/3/2010 7:16:24 PM CN=SERVER-TEST,OU=OLD Computers,DC=DOMAIN,DC=com ARCHIVE2 9/1/2010 11:36:09 AM CN=ARCHIVE2,OU=OLD Computers,DC=DOMAIN,DC=com ACCT-USR2 CN=ACCT-USR2,OU=OLD Computers,DC=DOMAIN,DC=com TECH-GC7 6/2/2011 12:24:32 AM CN=TECH-GC7,OU=OLD Computers,DC=DOMAIN,DC=com TECH7-GC 4/9/2012 8:35:46 PM CN=TECH7-GC,OU=OLD Computers,DC=DOMAIN,DC=com At this point I now have all the computer accounts in my Active Directory that are disabled. And it took me all of about a minute to type out the command and hit return. Much more efficient than going through AD manually and looking. At this point I could manually go into AD and remove the accounts but since I am already in PowerShell all I need to do is change the command slightly and I could remove those accounts in a flash. To do this I need to pipe this to another command and remove them Remove-ADComputer By piping the results to Remove-ADComputer PS will automatically remove the computers from Active Directory. Search-ADAccount –AccountDisabled -ComputersOnly | Remove-ADComputer –WhatIf My advice is ALWAYS, ALWAYS run your command with the –WhatIF switch first so you are sure that the command will do what you expect it to. You wouldn’t want to run this and find out that half of your AD has been unintentionally obliterated by the command. This same command can be changed slightly to look for AD Users as well. Search-ADAccount –AccountDisabled –UsersOnly | Format-Table –AutoSize Name, LastLogonDate, DistinguishedName Read Full Post » ## 2012 Scripting Games I successfully participated in the 2012 Scripting Games and am awaiting the results of the scoring. The game consisted of a Beginner and Advanced level, and there were 10 individual events that you had to create a powershell script for. I have to say I really had a great time and learned a lot. There is nothing like having a defined ‘need’ for a script to help spur the creative juices. So far my score isn’t bad, but honestly I didn’t participate to get a high score. I did it to learn more about PowerShell. When allowed I will try to post up some of the answers that I created. Read Full Post » ## Techstravaganza 2012 Today I am getting the opportunity to attend the Techstravaganza 2012 event http://www.techstravaganza.com at Microsoft’s office in NYC. I hope to pick up a lot of good information on PowerShell and SharePoint. Read Full Post » ## List Sever Reboot Times I was recently working on a Windows Server in my office when I noticed it had not been rebooted in a while. That got me to thinking, how many other servers in my environment had gotten in under the radar and were in need of a reboot. So, I decided to try my hand at writing another PowerShell Script. I started with a function called Get-RebootTime which is part of a module called BSonPosh. This module was created by Brandon Shell who is a MVP and who has his own blog at http://bsonposh.com. What I was looking to do was to have a list of the servers I wanted to check on a regular basis and have it automatically check each server and write the results to a csv file. That way I could review the file at my leisure and sort it by oldest reboot date. So, I set about creating my script. First I created a variable to contain my list of servers.$Servers = "DC1", "FS1", "RPTS1", "SQL1"

Then I wrote a ForEach statement to run a cmd for each item in the $Servers list. ForEach ($Computer in $Servers) { Get-RebootTime -ComputerName$Computer -Last }

When I ran this in my PowerShell editor it produced the desired results but in a simple list.

 RebootTime ComputerName 7/27/2011 1:39:12 PM DC1 2/10/2012 9:16:28 AM FS1 2/11/2012 11:40:59 AM RPTS1

This was great but the output was on the screen and not in a file that I could look at later and it wasn’t in a format I could manipulate. So, next step was to look into outputting the info into a file.

I tried using the Export-Csv cmdlet like this

ForEach ($Computer in$Servers) { Get-RebootTime -ComputerName $ComputerLast | Export-Csv ‘C:\Greg\boottimes.csv’ -notype} This worked, or so I thought, but in reality all it did was each time overwrite the same file with an entry. So that when I viewed the file it only showed the LAST server that the script queried. That wouldn’t do… So, I started looking into how to get the results of each Get-RebootTime cmd into an array so I could later output the array into csv. What I came up with was this: First I created an empty array called$results to hold all my output.

$results = @() I also created a variable for the path and name of the output file.$outputFile = ‘C:\Greg\boottimes.csv’

Then I changed my script to use New-Object cmdlet to create a new object called $result and using the Add-Member cmdlet I was able to add members to my object in the form of the values of the server name and the reboot time each time I looped through. Then I would take the object and add it to my$results array. When the loop was completed I could then use the Export-Csv cmdlet to output $results all at once into a csv file. Here is what the finished script looked like: # ServerBootTimes.ps1 # Written by Greg Caporale Febrary 28, 2012 # # Determines the last date a list of computers was rebooted # Exports the results to a csv file. # # Adjust the server list accordingly # Adjust the path and file name for the output file to your needs$results = @()
$Servers = "DC1", "FS1", "RPTS1", "SQL1"$outputFile = ‘C:\Greg\boottimes.csv’
ForEach ($Computer in$Servers) {
$result = New-Object psObject$result | Add-Member -MemberType NoteProperty -Name ‘Computer Name’ -Value $Computer$rbttime = Get-RebootTime -ComputerName $Computer -Last | select -ExpandProperty RebootTime$result | Add-Member -MemberType NoteProperty -Name ‘Last Reboot’ -Value $rbttime$results += $result }$results | Export-Csv \$outputFile -notype

SUCCESS!!  I had my csv file and was able to see all the servers and their reboot times.  This was a great exercise for me and it also gave me a nice little utility script that I could have handy or even schedule to run automatically so I always know how long its been between reboots of my servers.