« PowerShell and XML | Main | Two day seminar on Active Directory – 14th & 15th March 2007 (London) »
Excel, PowerShell, and the Import-CSV Cmdlet
By Adam Bell | March 6, 2007
I got caught out the other day. I reverted back to old (read: bad) VBScript habbits. It wasn’t deliberate, I was writing a function in PowerShell to take Active Directory information in CSV format and create a bunch of groups from it.
Okay, let’s actually start at the beginning. The data started out in Excel format but after a nightmare half hour trying to get Excel to play nicely with PS, I gave up and used SaveAs.
The Scripting Guys have a sample script, which unfortunately doesn’t work for me.
There appears to be some issues with Excel automation. Microsoft have KB 320369 on the subject, and there’s this thread on the PowerShell news group about it.
So I saved the data into CSV format, and moved on to the next challenge. The data was formatted: Group Name, Description, GroupType, DN. Now where I fell over was in my thinking of just breaking up the line using a delimiter into seperate variables. As the DN has comma’s within it, I just couldn’t split on those.
However the DN was enclosed in quotes. So I put this together:
function process-line
{
# The marker needs to be set to split on the same characters, but due to the format
# of the Group CSV, the characters are in reverse ,"
$Marker = $Inline.IndexOf(",""")
$PartA = $Inline.Substring(0,$Marker)
# The plus one reduces the substring by 1 to remove the leading comma
$PartB = StripQuotes($Inline.Substring(($Marker +1),($Inline.Length - ($Marker +1))))
# Process PartA
$Temp = $PartA.split(",")
$GrpName = $Temp[0]
$GrpDesc = $Temp[1]
$GrpType = $Temp[2]
# Process PartB
$GrpDN = $PartB
write-host $grpName $grpDesc $grpDN $GrpType
}
The line:
Test Group1,PowerShell Test Group,Security Domain Local,"cn=users"
would result in:
Test Group1 PowerShell Test Group Security Domain Local cn=users
Great. Until I remembered the Import-CSV cmdlet. Rather than opening up my CSV file with something like:
$data = get-content d:\coding\ps\groups.csv
foreach ($line in $data)
{
process-line $line
}
This approach is better suited:
$data = import-csv d:\coding\ps\groups.csv
foreach ($line in $data)
{
write-host $line.GrpName $line.Desc $line.GrpType $line.DN
}
Now the cmdlet removes the need for trying to work out where to split the line, and setting up each variable individually because so long as your CSV has a header row,
GrpName,Desc,GrpType,DN
Test Group1,PowerShell Test Group,Security Domain Local,"cn=users"
the Import-CSV enables you to access the correct column by name.
I guess the moral of the story here, is when dealing with a new language, have a think about what you’re trying to do and then see if there’s any built in functionality that can shorten the task!
Now we have another good example of PowerShell reducing the code required to get the job done, and this time using a cmdlet :)
- None Found
Topics: PowerShell | 1 Comment »
October 29th, 2009 at 04:55
[...] can read about them and a bit about how to use them here, and here. Additionally, I would refer anyone with scripting questions to check out the Scripting Guys [...]