Expert Solution for 2011 Scripting Games Beginner Event 8: Use PowerShell to Write Disk File Information for Excel

Expert Solution for 2011 Scripting Games Beginner Event 8: Use PowerShell to Write Disk File Information for Excel

  • Comments 1
  • Likes

Summary: Microsoft PowerShell MVP, Doug Finke, gathers file information and writes it to a CSV file that is readable in Excel while solving 2011 Scripting Games Beginner Event 8.

Microsoft Scripting Guy, Ed Wilson, here. Doug Finke is the expert commentator for Beginner Event 8.

Photo of Doug Finke

Doug Finke is a Microsoft PowerShell MVP working for Lab49, a company that builds advanced applications for the financial service industry. Over the last 20 years, Doug has been a developer and author working with numerous technologies. You can catch up with Doug on his blog, Development in a Blink.

Worked solution

Beginner Event 8 in the 2011 Scripting games is a challenge in automating the gathering of disk file information with Windows PowerShell and showing it in a Microsoft Excel spreadsheet, with the least amount of human intervention. I’ll start here:

  1. Get-ChildItem c:\ |
  2.     Select FullName, Length, LastWriteTime |
  3.         Export-Csv –NoTypeInformation .\test.csv
  4. Invoke-Item .\test.csv

This solves the challenge, but it doesn’t get extra points. I’ll highlight a few pieces. In line 1, I get the items in the directory, specifying the root on drive C; and then in line 2, I ask for only three properties. Next, I save the output to test.csv by using the Export-Csv cmdlet. This cmdlet saves the output from the directory listing in a comma-separated format, which Microsoft Excel can read with no problem. The NoTypeInformation parameter omits the first line "#TYPE ". Finally, line 4 uses the Invoke-Item cmdlet, which performs the default action on the specified item. Because I am exporting to text.csv, if I were to double-click on that file in the file explorer, Windows will launch Excel and open it. That is what Invoke-Item does. Pretty cool!

Getting extra credit

Right now, the script is hard coded to work only on the root of drive C, and it is not a reusable function. I want to fix that.

Function Out-Excel ($path="$pwd") {

    Get-ChildItem $path |

        Select-Object FullName, Length, LastWriteTime |

        Sort Length -Descending |

        Export-Csv -NoTypeInformation $pwd\test.csv

    Invoke-Item $pwd\test.csv

}

Now I can call Out-Excel from the command line, and if I do not specify a directory, it defaults to the present working directory $pwd. This happens because we default the parameter $path to $pwd. I now have options in how I can call this function:

Out-Excel

No parameters. Defaults to $pwd

Out-Excel c:\

 

Out-Excel $PSHOME

Shows the files in the Windows PowerShell home directory

A few more tweaks

Sometimes I pass in the wrong path, I do not want to wait for Excel to come up blank, so I will cause the script to gracefully fail with useful information. In addition, I want to see the largest file first. I will add a Test-Path, a throw and a Sort. Test-Path determines if the path exists. If it does not, I throw, which generates a terminating error. So the script will not continue to the Get-Child or the Invoke-Item parameters, and Excel will not start. After choosing the three properties I want to see, I sort the data by the Length property in descending order.

Function Out-Excel {

    param ($path="$pwd")     

    if( -Not (Test-Path $path) ) {

        throw "Cannot find path [$path]"

    }

    Get-ChildItem $path |

        Select-Object FullName, Length, LastWriteTime |

        Sort Length -Descending |

        Export-Csv -NoTypeInformation $pwd\test.csv

    Invoke-Item $pwd\test.csv

}

I went from a four-line script that pulled specific properties from some data, saved it to a CSV file (with comma-separated values) so that I could automatically review the data in Excel. The final script is 11 lines and results in a flexible reusable script that reports problems quickly so I can recover and provide the correct parameters.

The more you invest in Windows PowerShell by learning key fundamentals like functions, parameters, and built-in cmdlets, the more you reap in becoming more productive.

Thank you for your solution for Beginner Event 8, Doug.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hello Doug,

    an easy, understandable working solution!

    I won't add any comments on error handling --- like I can't write to "$pwd\test.csv" this time :-)

    kind regards, Klaus