Converting CSV file or files into an Excel workbook

Update 07SEPT2010: Updated script based on an excellent comment to use the Paste() method for converting CSV files into an excel workbook. Thanks Luc!

While going through the Technet Powershell Forums and Scripting Guys Forums, I came across a question by an individual who was looking for the best way to convert a .csv file into an excel file. As it stood, the individual only need to convert one csv file and that made for a fairly easy solution.

$xl = new-object -comobject excel.application
$xl.visible = $true
$Workbook = $xl.workbooks.open(“$loglocation\errors_$server.csv”)
$Worksheets = $Workbooks.worksheets
$Workbook.SaveAs(“$loglocation\errors_$server.xls”,1)
$Workbook.Saved = $True
$xl.Quit()

Simple enough. Just open up the CSV file using a created excel com object in Powershell and then save that file as an excel spreadsheet.

This got me to thinking about what if I wanted to save multiple csv files into an excel workbook? How would that be done? And of course, can this all be automated to auto-fit the columns and provide a name to each worksheet that was relevant to each csv file that was being converted?

The short answer, is…Yes! This can be done with Powershell and accomplish all of the requirements.

Below is the script that I wrote that takes multiple csv files and adds them all into excel.  It also auto-fits each column in excel so when you open it up, you already have access to all of the information without having to manually re-size each column. Plus, for each csv that you submit, a separate worksheet is created with the name of the csv file submitted, minus the extension.  Depending on the size of the csv, the time it takes for the conversion will vary.

As shown in the picture below, I have two csv files that are being converted into one excel workbook and saved to whatever filename I want using the .xlsx format.

When I open up the excel file, I now see both of my CSV’s there to view.

Simple enough!

You can find this script below or at this link on the Technet Gallery:

ConvertCSV-ToExcel.ps1

Script Below

Function Release-Ref ($ref)
{
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}

Function ConvertCSV-ToExcel
{
<#
.SYNOPSIS
Converts one or more CSV files into an excel file.

.DESCRIPTION
Converts one or more CSV files into an excel file. Each CSV file is imported into its own worksheet with the name of the
file being the name of the worksheet.

.PARAMETER inputfile
Name of the CSV file being converted

.PARAMETER output
Name of the converted excel file

.EXAMPLE
Get-ChildItem *.csv | ConvertCSV-ToExcel -output ‘report.xlsx’

.EXAMPLE
ConvertCSV-ToExcel -inputfile ‘file.csv’ -output ‘report.xlsx’

.EXAMPLE
ConvertCSV-ToExcel -inputfile @(“test1.csv”,”test2.csv”) -output ‘report.xlsx’

.NOTES
Author: Boe Prox
Date Created: 01SEPT210
Last Modified:

#>

#Requires -version 2.0
[CmdletBinding(
SupportsShouldProcess = $True,
ConfirmImpact = ‘low’,
DefaultParameterSetName = ‘file’
)]
Param (
[Parameter(
ValueFromPipeline=$True,
Position=0,
Mandatory=$True,
HelpMessage=”Name of CSV/s to import”)]
[ValidateNotNullOrEmpty()]
[array]$inputfile,
[Parameter(
ValueFromPipeline=$False,
Position=1,
Mandatory=$True,
HelpMessage=”Name of excel file output”)]
[ValidateNotNullOrEmpty()]
[string]$output
)

Begin {
#Configure regular expression to match full path of each file
[regex]$regex = “^\w\:\\”

#Find the number of CSVs being imported
$count = ($inputfile.count -1)

#Create Excel Com Object
$excel = new-object -com excel.application

#Disable alerts
$excel.DisplayAlerts = $False

#Show Excel application
$excel.Visible = $False

#Add workbook
$workbook = $excel.workbooks.Add()

#Remove other worksheets
$workbook.worksheets.Item(2).delete()
#After the first worksheet is removed,the next one takes its place
$workbook.worksheets.Item(2).delete()

#Define initial worksheet number
$i = 1
}

Process {
ForEach ($input in $inputfile) {
#If more than one file, create another worksheet for each file
If ($i -gt 1) {
$workbook.worksheets.Add() | Out-Null
}
#Use the first worksheet in the workbook (also the newest created worksheet is always 1)
$worksheet = $workbook.worksheets.Item(1)
#Add name of CSV as worksheet name
$worksheet.name = “$((GCI $input).basename)”

#Open the CSV file in Excel, must be converted into complete path if no already done
If ($regex.ismatch($input)) {
$tempcsv = $excel.Workbooks.Open($input)
}
ElseIf ($regex.ismatch(“$($input.fullname)”)) {
$tempcsv = $excel.Workbooks.Open(“$($input.fullname)”)
}
Else {
$tempcsv = $excel.Workbooks.Open(“$($pwd)\$input”)
}
$tempsheet = $tempcsv.Worksheets.Item(1)
#Copy contents of the CSV file
$tempSheet.UsedRange.Copy() | Out-Null
#Paste contents of CSV into existing workbook
$worksheet.Paste()

#Close temp workbook
$tempcsv.close()

#Select all used cells
$range = $worksheet.UsedRange

#Autofit the columns
$range.EntireColumn.Autofit() | out-null
$i++
}
}

End {
#Save spreadsheet
$workbook.saveas(“$pwd\$output”)

Write-Host -Fore Green “File saved to $pwd\$output”

#Close Excel
$excel.quit()

#Release processes for Excel
$a = Release-Ref($range)
}
}

Posted in powershell, scripts | Tagged , , | 34 Comments

Convert bytes to highest available unit

Unfortunately, I had something else in mind to post this weekend, but life and some other things prevented me from doing so.  Maybe next weekend…

Because I wanted to get at least something out this weekend, I am going to use an advanced function I wrote during the 2010 Scripting Games that takes a size in bytes that would normally get if you do a WMI query against something like the Win32_LogicalDisk class for the size of free space and total space on each drive. You would get something like this:

PS C:\temp> gwmi win32_logicaldisk | Select DeviceID,FreeSpace, SizeDeviceID                                                              FreeSpace                                    Size
——–                                                              ———                                    —-
C:                                                                  88777068544                            309185212416
D:                                                                   4628267008                             10737414144
E:

Now this is fine and all but it leaves little to be desired when it comes to readability. Given, can do something like the following to get a specific size, such as GB:

PS C:\temp> gwmi win32_logicaldisk | Select DeviceID,@{Expression={$_.Freespace /1GB};Label=”FreeSpaceGB”}, @{Expression=
$_.Size / 1GB};Label=”SizeGB”}
DeviceID                                                              FreeSpaceGB                                    SizeGB

——–                                                              ———                                    —-

C:                                                              82.679988861084                        287.951168060303

D:                                                             4.31040954589844                        9.99999618530273

E:                                                                            0                                       0

Better, as long as you label somewhere what the default size is, someone who views this report will know what the size is of each hard drive.

What I wrote will take the size in bytes that will the given size and convert it to the highest possible size up to Petabyte. Not only that, but it will add the appropriate size after the integer so exactly what the size is.

PS C:\Users\boe\Downloads> gwmi win32_logicaldisk | Select DeviceID,@{Expression={Convert-BytesToSize $_.Freespace};Labe
l=”FreeSpace”}, @{Expression={Convert-BytesToSize $_.Size};Label=”Size”}

DeviceID                                FreeSpace                               Size
——–                                ———                               —-
C:                                      82.68GB                                 287.95GB
D:                                      4.31GB                                  10GB
E:                                      0Bytes                                  0Bytes


As you can see, the size has been converted to GB and beside each size is the corresponding GB that lets the user know exactly what the size is.  Below are some examples of each size having been converted from its initial bytes size.

PS C:\Users\boe\Downloads> Convert-BytesToSize 235
235Bytes
PS C:\Users\boe\Downloads> Convert-BytesToSize 2352323
2.24MB
PS C:\Users\boe\Downloads> Convert-BytesToSize 23523
22.97KB
PS C:\Users\boe\Downloads> Convert-BytesToSize 235232323
224.34MB
PS C:\Users\boe\Downloads> Convert-BytesToSize 23523232323
21.91GB
PS C:\Users\boe\Downloads> Convert-BytesToSize 2352323232323
2.14TB
PS C:\Users\boe\Downloads> Convert-BytesToSize 235232323232323
213.94TB
PS C:\Users\boe\Downloads> Convert-BytesToSize 23523232323232323
20.89PB

Script below:

Function Convert-BytesToSize
{
<#
.SYNOPSIS
Converts any integer size given to a user friendly size.

.DESCRIPTION


Converts any integer size given to a user friendly size.

.PARAMETER size


Used to convert into a more readable format.
Required Parameter

.EXAMPLE


ConvertSize -size 134217728
Converts size to show 128MB

#>

#Requires -version 2.0


[CmdletBinding()]
Param
(
[parameter(Mandatory=$False,Position=0)][int64]$Size

)


#Decide what is the type of size
Switch ($Size)
{
{$Size -gt 1PB}
{
Write-Verbose “Convert to PB”
$NewSize = “$([math]::Round(($Size / 1PB),2))PB”
Break
}
{$Size -gt 1TB}
{
Write-Verbose “Convert to TB”
$NewSize = “$([math]::Round(($Size / 1TB),2))TB”
Break
}
{$Size -gt 1GB}
{
Write-Verbose “Convert to GB”
$NewSize = “$([math]::Round(($Size / 1GB),2))GB”
Break
}
{$Size -gt 1MB}
{
Write-Verbose “Convert to MB”
$NewSize = “$([math]::Round(($Size / 1MB),2))MB”
Break
}
{$Size -gt 1KB}
{
Write-Verbose “Convert to KB”
$NewSize = “$([math]::Round(($Size / 1KB),2))KB”
Break
}
Default
{
Write-Verbose “Convert to Bytes”
$NewSize = “$([math]::Round($Size,2))Bytes”
Break
}
}
Return $NewSize

}

Posted in powershell, scripts | Tagged , , , | 3 Comments

Balloon Notifications with Powershell

Updates Balloon Pop-up

You’ve probably seen those balloon pop-ups at your computer at some point in time.  Most likely it was saying something about having updates that need to be installed on your computer.  They pop-up and say something and you can either click on it to bring up another window, close it out or let it disappear and continue on your merry way. One of the many things that Powershell can do is create the same type of pop-up using whatever icons and messages you want to display.

The first thing to do is to load the required assembly System.Windows.Forms to gain access to the notification capabilities and then create the notification object.

[void] [System.Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”)
$notification = New-Object System.Windows.Forms.NotifyIcon

With this, you are then able to define what kind of title and message that your popup will display and show.

BalloonTipText   :
BalloonTipIcon   : None
BalloonTipTitle  :
ContextMenu      :
ContextMenuStrip :
Icon             :
Text             :
Visible          : False
Tag              :
Site             :
Container        :

BalloonTipText is the message that you will show and BalloonTipTitle is, of course, the title that the balloon will display. You will also want to change the Visible property to True so the balloon will show up when you call it with the  ShowBalloonTip method.

You will noticed that there is a property for an icon,not to be confused with the BalloonTipIcon property that shows the icon of the actual balloon pop-up.  This dictates what icon will show up on the system tray when the notification is called.  You can either supply your own icon or use one that is already available. In my case, I am using the Information icon via the SystemIcons namespace.

[System.Drawing.SystemIcons]::Information

Listed below are the available icons from that namespace.

Application     Property
Asterisk        Property
Error           Property
Exclamation     Property
Hand            Property
Information     Property
Question        Property
Shield          Property
Warning         Property
WinLogo         Property

When you look at the methods and properties using the Get-Member cmdlet, you will also see some events that can be used with some event monitoring to run specific actions based on if the balloon tip is closed or you wait for it to closed after the timeout or if you click on the balloon.  There are also events that work with actions from the mouse but I will not be working with those.

Name                      MemberType
—-                      ———-
BalloonTipClicked         Event
BalloonTipClosed          Event
BalloonTipShown           Event
Click                     Event
Disposed                  Event
DoubleClick               Event
MouseClick                Event
MouseDoubleClick          Event
MouseDown                 Event
MouseMove                 Event
MouseUp                   Event
CreateObjRef              Method
Dispose                   Method
Equals                    Method
GetHashCode               Method
GetLifetimeService        Method
GetType                   Method
InitializeLifetimeService Method
ShowBalloonTip            Method
ToString                  Method

Using the Register-ObjectEvent will allow you to leverage those events and have the perform specific actions based on how the event is handled. For instance, closing the balloon could allow the system tray icon to close or clicking on the balloon could allow another window to pop-up with information related to the pop-up.  In my instance, I display messages that state whether the balloon was closed or clicked on.

The last thing you need to do is to call the notification which requires you to determine the timeout (measured in milliseconds) for how long the notification will remain in the system tray.

$notification.ShowBalloonTip(600)

Putting everything together, I was able to generate a balloon pop-up that depending on whether you click it, close it or wait for it to close, a windows message will be generated that will tell you what action took place.

Balloon Tip Pop-up

Balloon pop-up clicked

As you can see just by the examples, there is quite a bit you can do with the notifications and the actions that are associated with the notifications.  Below is the code I wrote to perform the balloon pop-ups.  Feel free to use and test with other things for reporting or configuration changes.

For more information regarding Balloon Notification alerts, check out this site:

http://msdn.microsoft.com/en-us/library/system.windows.forms.notifyicon.aspx

#Load the required assemblies
[void] [System.Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”)
#Remove any registered events related to notifications
Remove-Event BalloonClicked_event -ea SilentlyContinue
Unregister-Event -SourceIdentifier BalloonClicked_event -ea silentlycontinue
Remove-Event BalloonClosed_event -ea SilentlyContinue
Unregister-Event -SourceIdentifier BalloonClosed_event -ea silentlycontinue
#Create the notification object
$notification = New-Object System.Windows.Forms.NotifyIcon
 

#Define the icon for the system tray
$notification.Icon = [System.Drawing.SystemIcons]::Information

#Display title of balloon window
$notification.BalloonTipTitle = “This is a Balloon Title”

#Type of balloon icon
$notification.BalloonTipIcon = “Info”

#Notification message
$title = “This is the message in the balloon tip.”
$notification.BalloonTipText = $title

#Make balloon tip visible when called
$notification.Visible = $True

## Register a click event with action to take based on event
#Balloon message clicked
register-objectevent $notification BalloonTipClicked BalloonClicked_event `
-Action {[System.Windows.Forms.MessageBox]::Show(“Balloon message clicked”,”Information”);$notification.Visible = $False} | Out-Null

#Balloon message closed
register-objectevent $notification BalloonTipClosed BalloonClosed_event `
-Action {[System.Windows.Forms.MessageBox]::Show(“Balloon message closed”,”Information”);$notification.Visible = $False} | Out-Null

#Call the balloon notification
$notification.ShowBalloonTip(600)

 


Posted in powershell | Tagged , | 7 Comments

Powershell, Regular Expressions And You!

“^(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$”

Pretty scary, isn’t it? While most of you will never have to build something this long and complex, it still is a nice idea to learn how to use Regular Expressions in your scripts when you need to look for patterns in the contents of a log file, enforce a specific pattern for something like a phone number or login name.  Whatever the case may be, Regular Expressions can save you a lot of time in either of these situations.  The only problem is…Where do you begin?  Hopefully the my posts on this subject will give an idea on where this can take you.  Please understand that I am no expert on this subject and this is by no means an advanced take on the topic.  My only goal is to get you started on using Regular Expressions and showing some potential “Real World Examples” that will hopefully help you out.

By the way, the expression shown at the beginning is used to verify IPv4 addresses.

Let’s just start off with something more simple, such as a phone number. For instance, you want to enforce in your script to build user accounts in Active Directory that the phone numbers need to match the following: “555-5555”. Seems simple enough, right? Looking at this number, we see three things that we need to enforce:

  1. Prior to the “-” we need exactly 3 digits
  2. We need to make sure that after the 3 digits, there is a “-” before the next set of digits.
  3. Lastly, after the “-” we need exactly 4 digits

How we would do that is setting the Regular Expression as such: “^\d{3}-\d{4}$”

Lets break this down to understand what each part in this plays.

  1. First, we encase the expression in quotes “” so it knows to where the expression begins and ends.
  2. The “^” (carrot) tells the expression that the beginning part of the string to be matched must match the beginning of the expression. In this case, the beginning must match 3 digits.
  3. Next is the “\d” which is used to match a digit, usually something like “\d[0-9]” or “\d{2}”
  4. The “{3} after the digit tells the expression that it must be 3 digits in length, no more and no less.
  5. The “-” tells the expression that there must be a dash after the first 3 digits and before the following 4 digits:
  6. Again, we have the “\d{4}” which tells us that there must be exactly 4 digits after the “-“.
  7. Lastly, we have the “$” which tells us that it must end with the 4 digits, otherwise it will be considered invalid.

Let’s put this into a more “Real World Example”. Lets assume this piece of code is in a script to build an Active Directory account:

[regex]$phone = “^\d{3}-\d{4}$”

Do {
Write-host -ForegroundColor Cyan “Please Enter a Phone Number that matches the following: 555-5555”
$number = Read-Host
If (!($number -match $phone)) {
Write-Host -ForegroundColor Red “Phone Number: $($number) is in incorrect format! `nPlease try again!”
}
}
Until ($number -match $phone)
Write-Host -ForegroundColor Green “Phone Number: $($number) is in proper format”

Using this code, you can see when running the script that the user cannot continue on with the build until they put in an acceptable phone number.

As you can see, only a phone number with the correct format is acceptable.

This ends my first part in a multi-part series on using Regular Expressions with Powershell.  Next time I will dive into using letters and ranges of numbers and letters for validating content.

Posted in powershell | Tagged , , | 5 Comments

Retrieve computer status with IP Address

Here is a simple script that I posted both in the Powershell forums and on the technet script repository.  Basically all it does is take either a provided computername or reads from a host list and proceeds to “ping” each computer to test for connectivity and if reachable, also grabs the IP address of the machine and adds to an array.  Machines that are not reachable are added to the list but without the IP address.


<#
.SYNOPSIS
Tests computer for connection and lists status with IP address.
.DESCRIPTION
Tests computer for connection and lists status with IP address.
.PARAMETER server
Name of server to test connection to.
.PARAMETER file
Name of host file to test connection to.
.PARAMETER credential
Allows the use of alternate credentials, if required.
.NOTES
Name: Get-ComputerIP.ps1
Author: Boe Prox
DateCreated: 05Aug2010

.LINK
http://
.EXAMPLE
Get-ComputerIP

#>
[cmdletbinding(
SupportsShouldProcess = $True,
DefaultParameterSetName = ‘computer’,
ConfirmImpact = ‘low’
)]
param(
[Parameter(
Mandatory = $False,
ParameterSetName = ‘computer’,
ValueFromPipeline = $True)]
[string]$computer,
[Parameter(
Mandatory = $False,
ParameterSetName = ‘file’)]
[string]$file,
[Parameter(
Mandatory = $False,
ParameterSetName = ”)]
[switch]$credential
)
If ($file) {
$computers = Get-Content $file
}
Else {
$computers = $computer
}
If ($credential) {
$cred = Get-Credential
}
$report = @()
ForEach ($computer in $Computers) {
Try {
$tempreport = New-Object PSObject
If ($credential) {
$IP = ((Test-Connection -ea stop -Count 1 -comp $computer -credential $cred).IPV4Address).IPAddresstoString
}
Else {
$IP = ((Test-Connection -ea stop -Count 1 -comp $computer).IPV4Address).IPAddresstoString
}
$tempreport | Add-Member NoteProperty Computer $computer
$tempreport | Add-Member NoteProperty Status “Up”
$tempreport | Add-Member NoteProperty IP $ip
$report += $tempreport
}
Catch {
$tempreport = New-Object PSObject
$tempreport | Add-Member NoteProperty Computer $computer
$tempreport | Add-Member NoteProperty Status “Down”
}
}
$report


Posted in powershell, scripts | Tagged , , | 2 Comments