Connecting to CSV data from PowerGadgets

[IGSFX]

If you want to connect PowerGadgets to CSV data, the easiest way to do so is using the import-csv cmdlet included with PowerShell. For example, let's assume you have a Sales.csv file with Units and Amount sold per Month, with the following data:

Sales.csv
Month,Units,Amount
January,1723,134561.35
February,2167,141289.90
March,2541,165182.18
April,2643,190781.36
May,2434,175111.85
June,2934,204518.71
July,2338,163779.05
August,1834,136528.49
September,2840,188787.35
October,2612,187614.22
November,2411,190993.00
December,3025,211743.80

The first try to plot such data is running the following PowerShell command:

import-csv Sales.csv | out-chart

However, this will show an empty chart:

/TeamBlogImages/ConnectingtoCSVdatafromPowerGadgets_59F0/NoDataAvailable.jpg

The reason for this is that import-csv returns every field as a string, so PowerGadgets cannot know automatically which fields contain plottable data. PowerGadgets has the capability to cast any field to a number, when you specify which fields to plot using the -values parameter. You can also specify which field should be used as the X axis label with the -label parameter. The second try gives you the chart you want:

import-csv Sales.csv | out-chart -values Units,Amount -label Month

/TeamBlogImages/ConnectingtoCSVdatafromPowerGadgets_59F0/CsvChart2.jpg

Connecting to CSV data from the PowerGadgets Creator

The PowerGadgets Creator UI doesn't support the casting from strings capability (this is something we might enhance in a future version). For such reason, a different approach must be taken. Through the use of PowerShell's scriptblock capabilities, you can return an object that can be understood by the Creator. The PowerShell command to invoke from the PowerGadgets Creator data section is the following:

import-csv Sales.csv | select Month,{$_.Units},{$_.Amount}

/TeamBlogImages/ConnectingtoCSVdatafromPowerGadgets_59F0/CsvChartFromScriptblock_thumb.jpg

This is almost the chart you want to obtain, but it's not quite there yet. If you review carefully, the chart legend shows $_.Units and $_.Amount because it's using the string passed to the series. To overcome this issue, we resource to a capabilty in the scriptblocks which is honored in PowerGadgets. Your final command is the following:

import-csv Sales.csv | select Month,@{Expression={$_.Units};Name="Units Sold"},@{Expression={$_.Amount};Name="Sales Amount"}

/TeamBlogImages/ConnectingtoCSVdatafromPowerGadgets_59F0/CsvChartFromScriptblock2_thumb.jpg

IvanG