Creating a GUI for CSV Data

• Windows PowerShell

User Rating: 5 / 5

I often manage data in CSV files, typically data that originates in an Excel spreadsheet. The Import-Csv cmdlet, which creates custom objects from the contents of a CSV file, makes CSV data an optimal choice in Windows PowerShell.

But, how do you manage CSV data in a Windows UI? PowerShell Studio offers several options. In this post, I’ll cover data in Out-GridView, a single Textbox, a collection of Textbox objects, and a DataGridView.

[Thanks to Joel Ivarsson for suggesting the post topic. If you have a topic suggestion, send me an email at This email address is being protected from spambots. You need JavaScript enabled to view it..]

Use Out-GridView

One of the easiest ways to display complex data is to use the Out-GridView cmdlet. You can launch it from the console or any UI.

I created a form with a Start button and put the command that calls Out-GridView with my CSV data in the button’s Click event. For the CSV data, I’m using a file of data about the SAPIEN MVPs for 2015. Notice that I’m not assigning the data to any element in my form, because the grid view is external; it displays in a separate window.

$buttonStart_Click={
    Import-Csv -Path C:\ps-test\SAPIENMVP2015.csv `
        -Header ID, Name, City, State, Country |
    Out-GridView –Title Get-CsvData
}

Here’s the result. The grid that Out-Gridview generates includes features for searching and filtering, and sorting.

Use a Textbox

Out-GridView is very easy, but it generates an independent window. Let’s examine a few strategies that display the data within your UI. The simplest one is a textbox.

In this simple implementation, I use the Get-Content cmdlet to get the CSV file content, pipe it to the Out-String cmdlet, and assign the result to the Text method of the Textbox. If I omit the call to Out-String, I lose the the line breaks in the file. To preserve the line breaks, use Out-String or the Raw parameter of Get-Content.

$buttonStart_Click={
    $textboxOutput.Text = Get-Content -Path C:\ps-test\SAPIENMVP2015.csv | Out-String
}

Here’s the result. Quick, but not pretty. And, it doesn’t have any features for searching or sorting.

Use a Textbox Table

For this implementation, I’ve created a series of Textbox objects and arranged them in a table. I set the name of each Textbox to “Textbox” followed by a number that indicates the row and column position of the Textbox in the table. For example, first box is Textbox00 (row 0, column 0), the last one in the first row is Textbox04 (row 0, column 4) and the last one is Textbox34 (row 3, column 4).

To load the CSV data into this faux-grid, I use a nested For loop that advances through the rows (the $i variable), filling the columns (the $j variable) in each row. I used the Split operator with a comma delimiter to split each row into its component values and array notation to refer to the rows and columns.

The tricky part was getting the variable that represents each carefully named Textbox. I used the Get-Variable cmdlet to get a variable named “Textbox$i$j”, where the values of $i and $j advance as we move through the loops. Then, I used the dot method to get its value (the textbox), and assigned the current object ($thisrow[$j]) to the Text property of the textbox.

$buttonStart_Click = {
    $rows = Get-Content C:\ps-test\SAPIENMVP2015.csv
 
    for ($i = 0; $i -lt $rows.count; $i++)
    {
        $thisrow = $rows[$i] -split ","
        for ($j = 0; $j -lt $thisrow.count; $j++)
        {
            (Get-Variable -Name "Textbox$i$j").Value.Text = $thisrow[$j]
        }
    }
}

Here’s the result. It’s prettier than the textbox, but it has no searching or sorting features. Because I created a static form, I needed to know the exact size of my data in advance. I could have built the form dynamically, but that would have been substantially more complex.

Use a DataGridView

A DataGridView object is a much better solution for CSV data. This is inherently a much more complex object, but PowerShell Studio adds code that does almost all of the work for you.

To begin, click File, New form, Grid template.

In the Load button’s Click event, I found an example among the comments and just followed the pattern in the example. The ConvertTo-DataTable and Load-DataGridView functions are added to the Control Helper Functions region of your script when you select the Grid template.

1. Get objects and save them in a variable.

   $processes = Get-WmiObject Win32_Process -Namespace "Root\CIMV2"

2. Use the ConvertTo-DataTable function to create a table of your objects and their properties. The value of the InputObject parameter is the variable that contains your objects. Save the result in the $table variable.

   $table = ConvertTo-DataTable -InputObject $processes –FilterWMIProperties

3. Use the Load-DataGridView function to load the table in $table into the DataGridView control.

   Load-DataGridView -DataGridView $datagridviewResults -Item $table

For my implementation, I used the Import-Csv cmdlet to convert the items my CSV file to custom objects and saved them in a $rows variable. Because I didn’t have a header row in my CSV file, I used the Header parameter to specify names for the properties in each row. Then, I set $rows as the value of the InputObject parameter of the ConvertTo-DataTable function and used the Load-DataGridView command unchanged.

$rows = Import-Csv -Path C:\ps-test\SAPIENMVP2015.csv `
    -Header ID, Name, City, State, Country
$table = ConvertTo-DataTable -InputObject $rows
Load-DataGridView -DataGridView $datagridviewResults -Item $table

Now, when I click the Load button, the data from my CSV file appears in a well-formatted grid. Clicking the header labels sorts the values in alphanumeric order.

I made a few changes in my code to give it a bit of polish. I piped my imported CSV custom objects to the Select-Object cmdlet. In the value of the Property parameter, I used a calculated property to cast the ID value as an integer. Also, because the value of Country is always USA (so far!), I omitted it.

$rows = Import-Csv -Path C:\ps-test\SAPIENMVP2015.csv `
    -Header ID, Name, City, State, Country |
Select-Object -Property @{Label = "ID"; Expression = { [int32]$_.ID }}, `
    Name, City, State

I also used the Properties pane in PowerShell Studio to change a few properties of the DataGridView. I changed the BackgroundColor property to “Window” and, in the AlternatingRowsDefaultCellStyle property, I changed the Background property of alternate cells to “Light Blue.” I also resized the form. Because the DataGridView is properly anchored in to the form, it resizes automatically.

Now, when I run and click the Load button, I get a clear and appealing display that sorts correctly.

I love it when the easiest solution is the best one!

June Blender is a technology evangelist at SAPIEN Technologies, Inc. You can reach her at This email address is being protected from spambots. You need JavaScript enabled to view it. or follow her on Twitter at @juneb_get_help.

• Prev
• Next