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

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 

New in PowerGUI 2.1 – Custom welcome pages

Another great new feature for PowerPack creators is the ability to define a custom HTML or MHT page to be displayed for any node or folder in the PowerGUI Admin Console.

This feature can be incredibly powerful. Not only it lets you establish your own PowerPack branding and with your logos, links to additional resources and so on. It also lets you display your help and other relevant information right inside PowerGUI – you can even add links invoking nodes and actions right from that page!

Check out this quick video which Darin put together:

This feature is part of our PowerPack functionality which allows you to easily create and share administrative UIs on top of PowerShell. Download PowerGUI 2.1, give it a try and let us know what you think!

Subversion (SVN) Source-Control for PowerShell Scripts

I’ve already blogged about the use of PowerGUI Pro Script Editor with Team Foundation Server (TFS), now it’s time to check out integration with another popular source control solution – Subversion.

As with any other revision control system, to integrate PowerGUI needs you to install an MSSCCI provider for the system (this is the API which Microsoft Visual Studio is using). For Subversion there is a bunch of clients available – so use whichever you like as long as MSSCCI is one of its features.

For this post I used TamTam SVN SCC. It seems to be a great client however you would need to pay the author $14.99 unless you enjoy seeing their “Buy now” prompts every few minutes.

(If you really want to save the $14.99 there is also a free SCC client for Subversion: TortoiseSVN – however, you would have to spend a few minutes to make it work because the SCC provider is only supported by a fairly old version of the client – 1.3.5. If you want to go that route, download version 1.3.5 of the client here, and install it along with the TortoiseSVNSCC provider found in Documents & Files section here. You might also have to apply some registry keys on Vista and later.)

Anyway, I went the easy way:

1. Downloaded and installed Subversion v1.4.3 and TamTam SVN SCC .

2. In PowerGUI Pro Script Editor, went to Tools / Options / Version Control, selected TamTam as the Current provider and browsed to the svn.exe file.

3. Now I can add files to Subversion, check out, check in, and so on by using the Version Control menu in the editor:

Don running a survey

Don Jones – one of the most well-known PowerShell trainers, speakers, evangelists and community members is running a survey on his ConcentratedTech site. They have a bunch of $100-$500 certificates to give away, but more importantly this should help them optimize their site for better user experience.

So if you have a few minutes, here’s the survey link.

Vegas and So Cal next week

Next week I will be in Vegas for VMworld Monday-Wednesday, and in Southern California Thursday and Friday. Drop me a note if you are around and we can get together for a few beers and a PowerShell/PowerGUI/AD cmdlets/making the world better chat.

AD cmdlets 1.1 RTM!

Quest has just posted the RTM version of AD cmdlets 1.1 (1.1.1 to be exact, 1.1.0 was a beta version out for the last couple of months).

Besides the wonderful new stuff which was there in the beta (security management is my personal favorite), there are other great improvements ranging from performance advances and bugfixes to a number of other scenarios for account manipulation, provisioning, and copying added (some re-introduced) in this latest drop. Also, AD cmdlets are now available both in 32- and 64-bit versions.

I will blog about them in details next week (when I am back from vacation) but for now you can look at the documentation, built-in help, and the changes txt document.

Go get them here!

Scotty, get well soon!

Not the kind of news I enjoy blogging about. Alex Binotto has just told me that Scotty McLeod is in the hospital. Last night while going home he was hit by a train and is now in critical conditions fighting for his life.

Scotty was one of the guys evangelizing PowerShell in its early Monad days. He helped Richard found the first PowerShell Usergroup. Later he became the leader of the UK Windows Server usergroup.

Scotty is absolutely amazing: funny, geeky, and extremely knowledgeable and insightful.

We all hope that Scotty gets well soon!

[Update] Thanks to Keith and Adam here’s some updated info:

He’s in the Neurology Intensive Care ward of St Georges Hospital.

• His condition has improved since yesterday
• He’s on a ventilator to breath.
• Hes not conscious.
• He’s stable.

Not much to go on, but I was told he’s allowed to have visitors.

[Update 2: Jan 25] Thanks to Gordon:

The situation seems less dire than at first. He cannot be fully assessed until they start to wake him up, which will not be until after his jaw is fixed. The doctors are planning to take Scotty to surgery to fix the broken jaw – a good sign indeed.

Scotty’s family is with him. Obviously it is not the right time to call or visit him, but sending a card to demonstrate support to Scotty and his family might indeed be a good idea.

[Update 3: Jan 28] From Richard:

The jaw reconstruction went well and they have taken him off sedation but he hasn’t woken up yet. They will be doing a CT scan soon.

It looks like Scotty could either regain consciousness very soon or its going to be a long haul.

[Update 4: Jan 29] Richard quoting someone who visited Scotty:

His condition is currently described as stable but he is no longer on sedatives and has not woken up yet. The doctors say that it’s a waiting game now for the next couple of weeks to see if he comes out of the coma.

There is nothing medically stopping him from reviving at the moment but they are going to carry out some further tests to rule some things out.

[Update 5: Jan 31] From Mark:

I went to see Scotty this afternoon. It was the first time I’d seen him since the accident but his family tell me that there’s been a big improvement – the swelling and bruising is starting to go down, he’s off the ventilator and may even be moved from Intensive Care to a High Dependency Unit once a bed is available.

The big news was that whilst I was there he opened one eye and appeared to be watching me – probably thinking bugger off and stop blathering nonsense!

There’s a long way to go yet though – he’s still unconscious (I’m told he can hear visitors though and I’m pretty sure he was watching me) but his family certainly appreciate the visits from friends, colleagues and associates.

[Update 6: Feb 02] Please the comment Jan recently left on this blog. Also, here’s what Richard is forwarding from Scotty’s brother:

He has been moved from the Neurological ICU to the High Dependency Ward. Its called Brady or Brodie Ward. Its in the same building on the same floor as the ICU just at other end of corridor.

He is still unconscious but is now off the ventilator. He is showing more and more eye movement and more coordinated movement, especially of his arms. This is taken to be good signs and they are looking for the progress to continue.

Visiting times are 11-1 and 3-8. Visitors are more than welcome.

[Update 7: Feb 08] See the comment by Scotty’s brother below. There’s apparently great progress with Scotty now opening his eyes and moving his limbs. Visits are greatly encouraged and appreciated. See the comment for more details.

[Update 8: Feb 12] Update from Steve Lamb:.

I saw Scotty sat in a chair (beside his bed) with his eyes open and apparently awake with an old friend of his (a former lodger) talking to him. He commented that he could see improvements in Scotty’s awareness over the previous day and a half.

Scotty seemed to be aware of some of the things going on around him though his interactions were limited to hand squeezing and periodically pointing to his right ear – I learned that this is a request to have his ear rubbed which seemed to give him comfort.

He seemed to squeeze my hand more as I told him of our plans for the Windows Server launch, particularly about James’ Powershell session.

Scotty was also exploring his Mum’s watch and rings plus the intravenous trip with his fingers.

It’s great that Scotty is becoming more and more aware of the World around him.

Scotty’s Mum, Dad and brother (Murdo) expressed their wish for as many of Scotty’s friends as possible to visit him and talk about anything he might relate to. The family each expressed their amazement at the number of people who know their Son and the online support you have given.

See comments to this post for contact information and please pay Scotty a visit if you are in the area.

[Update 9: Feb 22]: Scotty is being moved to Sycamore Ward, on Level 4 of the Kingston Surgical Centre, visiting times 15:00-20:00.  The nearest railway station is Norbiton and this is literally across the road and down the hill from the hospital.  If you need maps the hospital website is http://www.kingstonhospital.nhs.uk

He is getting better and better every day. Interacting with people, smiling, moving, and so on. Please subscribe to the comments feed for this post to get the updates posted there.

As mentioned multiple times here: visits are highly encouraged (make sure you use his full name – Malcolm McLeod – when inquiring at the reception) and very important for Scotty’s recovery.

[Update: Feb 2009] Actually, this whole post and all the comments below are now a little bit of history – or a fairy tale with a very happy ending. Scotty recovered and is even blogging now at http://scotty-mcleod.spaces.live.com, he and Jan got married and are living together happy ever after. I wish all stories in this world went like that.

Kirk the Poshoholic becomes MVP

Kirk “Poshoholic” Munro has just got his MVP award! This is a great news (especially for the PowerGUI team ) and a well deserved award considering Kirk’s everyday participation in all PowerShell newsgroups and forums out there, his PowerShell talks in Canadian usergroups, and his willingness to use every opportunity to promote this technology. (Hey, frankly, they should have given him the award just for becoming the first self-acknowledged Poshoholic out there! )

Congratulations!

Who else got the award yesterday?

Happy New Year!

Just wanted to wish you all a very Merry Christmas and an amazingly Happy New Year! 2007 has been absolutely fantastic for us with both AD cmdlets and PowerGUI skyrocketing from first basic betas to featurerichness, great reviews and becoming tools used in real enterprise environments.

Thank you all for your participation in the community and for helping us build this great software! See you in 2008.

(You’ve got the Christmas/New Year wallpaper from our team already, right?)