Wednesday, October 10, 2012

Using Powershell and CSV to Automate Your Work - Part 1

Powershell is an awesome tool for anyone working with SharePoint.  With a few simple lines of code you can accomplish a lot of work in a short amount of time.  You can also utilize a CSV file to assist with this.  In this post I'll show you have to construct a CSV in Excel then open the file with Powershell and loop through its contents.

Part 2 of this demo is here.  Also, the full code including CSVs and Scripts can be downloaded from the Microsoft TechNet Gallery.

This demo will be pretty basic, all we'll do is open the CSV and write out the contents of each line.  While simple, it will illustrate how easy it is to work with a CSV file in Powershell.  In another post we'll look at using this same setup to automate placing Webparts on pages in your SharePoint environment.

First, open Excel and create a simple CSV:


We'll keep this CSV simple, it only has 3 columns:
  • Column 1
  • Column 2
  • Column 3
Easy peesy.  Now perform a "Save As" and make sure you choose "CSV (Comma delimited)", we'll call it "Powershell-CSV-Demo-Part-1":



Now, we'll save this in a folder in our C:\ called "Powershell", so the full path is "C:\Powershell\Powershell-CSV-Demo-Part-1.csv".

Now for the script:

$openCsv = Import-Csv -path "C:\Powershell\Powershell-CSV-Demo-Part-1.csv"
$openCsv | ForEach-Object {
   
    # Set Variables from CSV
    $col1 = $_.'Column 1'
    $col2 = $_.'Column 2'
    $col3 = $_.'Column 3'
   
    # Write out the values
    Write-Host "Data from CSV: " $col1 $col2 $col3
    

}

Here is what the script is doing:
  1. We open the CSV file using the Import-Csv command.
  2. We loop through each line of the CSV using the ForEach-Object command.
  3. Once inside the loop, we assign each column value to a variable, then Write-Host the values back out to the console.
Running this script through the ISE we get the following:


Running this script in the console we get the following:


There you go, pretty easy.  In the next post we'll use a similar script and CSV file to automate placing Webparts onto pages in SharePoint.

Part 2 of this demo is here. Also, the full code including CSVs and Scripts can be downloaded from the Microsoft TechNet Gallery.

Enjoy!

No comments:

Post a Comment