PowerShell Script to Bulk-Change Excel File Formats

Today I had to find a way to change file formats for a lot of files. Here at Jelastic we use JIRA for bug-report tracking. Every week I am getting tons of automated reports from JIRA to send around to our partners and so on. The problem is that JIRA exports reports in some weird html format with xls extention, and although Windows Excel can open it (after displaying ‘the file format and extension do not match’ warnings) – Macs cannot.

Opening and resaving lots of files manually was not an option – PowerShell was.

[UPDATE] I’ve made a few minor changes per feedback from Stanley – now properly managing file extansions and formats.

Here’s the script that you can reuse if you have a similar problem to solve:

############################
# PowerShell script to open all Excel files in a folder
# and re-save them in proper format
# (c) Dmitry Sotnikov
############################

# create COM object to use Excel
$objExcel = new-object -comobject excel.application

$objExcel.Visible = $True

# open the files to re-save one by one
dir D:\myfolder\*.xls | ForEach-Object {
$doc = $objExcel.WorkBooks.Open($_.FullName)

# Save in new format and with new extension
# Format codes can be found here:
# http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
$doc.SaveAs(“$($_.Directory)\$($_.BaseName).xlsx”,
[Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
$doc.close()
}

$objExcel.Quit()
$objExcel = $null

Just Script It!

Here’s Sean’s newest crazy PowerShell video in which he managed to feature your’s truly doing moonwalk (or trying to )

As you can see from the video – Microsoft MVP Summits are a lot of fun with amazingly smart and crazy people around.

No more one off IT management. Just Script It!

PowerShell script to set Skype status text to latest blog or twitter update

In my current company (Jelastic) we have something happening to us all the time: latest blog posts, awards, media mentions, etc. We are doing a decent job pushing these to various social media, but I also wanted to get these to my contacts in Skype (Skype gives you the ability to set your status text in your profile).

Below is the PowerShell v3 script that I wrote today to do that!

It takes the latest item from my blog and twitter feed, sees which of them is fresher, and (unless the tweet is just my reply to someone) pushes that to Skype (the property is called MoodText).

Here’s the script:

###################################################
# Set-SkypeStatusText.ps1
# Gets latest post from RSS feed (e.g. blog) and Twitter
# Picks whichever is the latest and sets it as status text (MoodText) in Skype
# (unless the latest tweet is a reply)
#
# NOTE: On x64 boxes, use PowerShell x86 (for Skype compat)
#
##################################################
# (c) 2012 - Dmitry Sotnikov
##################################################

# Customize these for yourself
$myblog = "http://blog.jelastic.com/feed/"
$myTwitterHandle = "DSotnikov"

# Get the blog feed
$blogFeed = Invoke-RestMethod $myblog

# Get the twitter feed
$twitterFeed = Invoke-RestMethod `
"https://api.twitter.com/1/statuses/user_timeline.rss?screen_name=$myTwitterHandle"


# If twitter is more recent and not a reply (does not start with @) use it
if (($twitterFeed[0].pubDate -gt $blogFeed[0].pubDate) -and
($twitterFeed[0].description[$myTwitterHandle.Length+2] -ne "@")){
$latestPost = $twitterFeed[0].description.Substring($myTwitterHandle.Length+2)
} else {
$latestPost = "$($blogFeed.Item(0).title): $($blogFeed.Item(0).link)"
}

# Set the status in twitter
$skype = New-Object -ComObject Skype4Com.Skype
$skype.CurrentUserProfile.MoodText = $latestPost

Now if you want to have this happen automatically you can just schedule it using Windows Task Scheduler.

Important:

• Make sure that you use 32-bit (x86) version of PowerShell if you are on 64-bit Windows – otherwise Skype object will not get found (so the filepath for the Windows task on x64 Windows will likely be %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe)
• Either sign your script or set ExecutionPolicy to RemoteSigned – otherwise the script execution will fail.

New in PowerShell 3: Parse HTML without IE object (unless a local file)

Remember how in PowerShell v1 and v2 we used to have to create Internet Explorer object each time we wanted to parse HTML page? This kind of works but has a few inconveniences such as having to insert Start-Sleep every now and then because IE can be busy and fail if you request too much from it too quickly.

In PowerShell v3, for web pages, things become much easier. Just do:

$p = Invoke-WebRequest "http://dmitrysotnikov.wordpress.com"

And $p.ParsedHtml.body will let you iterate though all web page elements!

However, there is a scenario in which you will have to revert to the old IE ways – local files. If the HTML file is on your local disk, $p will not have the ParsedHtml property. And you will have to use the IE COM object like you did in earlier versions of PowerShell:

$ie = new-object -com "InternetExplorer.Application"
# The easiest way to accomodate for slowness of IE
Start-Sleep -Seconds 1
$ie.Navigate("D:\SavedPage.htm")
# The easiest way to accomodate for slowness of IE
Start-Sleep -Seconds 1
$ParsedHtml = $ie.Document

Happy scripting!

Video: Brandon Shell – Module Design for IT Pro

Here’s another great recording from previous PowerShell Deep Dive – Brandon‘s session on module design. Brandon has experience designing PowerShell modules for Splunk and other companies – so there’s a lot to learn from him!

In this session we will deep dive into the thought process behind production module design. The presenter will explain the reason for choices made for the Splunk Module and his own BSonPosh module.

This is a live recording from European TEC 2011 PowerShell Deep Dive conference. See more PowerShell Deep Dive recordings here.

By the way, TEC US is just around the corner – April 29 – May 2, 2012 in San Diego. The agenda has already been published and is absolutely fantastic.

Register now - this is the best PowerShell event you can find!

Parsing LinkedIn html pages with PowerShell

A couple of weeks ago we posted a job opening on LinkedIn (were looking for a person to be in charge of our Jelastic‘s professional services), and it turned out that while LinkedIn jobs attract a lot of applications, the site itself does not make it easy to process them afterwards. You get CVs in email, and they also post a list of applicants with email addresses, phone numbers, titles, etc. – but there is no way to export the list to, say, Excel. In our case, we really wanted to have the data exported, so we could jointly work on a shared spreadsheet and everyone involved could grade each applicant and add notes to the table.

Being a PowerShell guy, I wrote the script below that does the scraping for me. Basically, I just saved the page with the list of applicants to my local disk and found that in their html, each applicant information is contained in vcard element, which has class name with LinkedIn URL and the actual name, and then elements with email and phone number:

So all my script has to do is: create an IE object and then use it to find the corresponding fields, then create custom objects from them, add them to the collection, and export it to CSV. Here’s the code – hope it helps you solve similar tasks when other sites do not provide good export capabilities:

$ie = new-object -com "InternetExplorer.Application"

# The easiest way to accomodate for slowness of IE
Start-Sleep -Seconds 1

$ie.Navigate("D:\Temp\LinkedIn.htm")

# The easiest way to accomodate for slowness of IE
Start-Sleep -Seconds 1

$doc = $ie.Document

# Get a collection of vcard elements
$cards = $doc.body.getElementsByClassName("vcard")

# This will be our collection of parsed objects
$processesCards = @()

# Iterate through the collection
for ($i=0; $i -lt $cards.length; $i++) {

 $itm = $cards.item($i)

 # Get the 'name' element that has the applicant name and URL
 $name = $itm.getElementsByClassName("name").item(0).
                       getElementsByTagName("a").item(0)

 # If you want you can output the name to the screen 
 # so you know where you are
 $name.outerText

 # Get the phone number and email address
 $phone = $itm.getElementsByClassName("phone").item(0)
 $email = `
   $itm.getElementsByClassName("trk-applicant-email").item(0)

 # Below is PowerShell v3 notation. 
 # In v2, replace '[pscustomobject]' with 
 # 'new-object psobject -Property' 
 $obj = [pscustomobject] @{"name"=$name.outerText; 
                           "url"=$name.href; 
                           "email"=$email.outerText; 
                           "phone"= $phone.outerText }

 $processesCards += $obj

}

# Export to CSV - which you can open in Excel
$processesCards | Export-Csv D:\Temp\linkedin.csv 

Video: Aleksandar Nikolic – Delegation with Remoting

Here’s recording of @alexandair – PowerShell MVP, constrained runspaces guru and editor of PowerShell Magazine – talking about delegation in PowerShell remoting. I bet not that many of you tried this feature before – so check out this talk that Aleksandar did at the last PowerShell Deep Dive in Frankfurt!

In this session you will learn how to set up a fan-in PowerShell endpoint, and then use it to assign specific administrative tasks to the appropriate users and groups without changing the membership of local Administrators group. By using just the IIS configuration files and PowerShell scripts we will enable dynamic creation of customized automation environments.

This is a live recording from European TEC 2011 PowerShell Deep Dive conference. See more PowerShell Deep Dive recordings here.

By the way, TEC US is just around the corner – April 29 – May 2, 2012 in San Diego. The agenda has already been published and is absolutely fantastic.

Register now - this is the best PowerShell event you can find!