import scv to two demension array

PowerShell Help
1

by SHURALE at 2012-12-07 00:10:30

Hello guys!
I am a fresh with PS and really need your help. I have a csv file that consists of fileds like number, date, cost, comms and etc. And i would like to create different reports using this file. The first of all i need to import this file and sort by date. Then i need group by date (i mean i need to collect record by month so i need to go throug each record and define what is the month and put the record to some array). Once i get records grouped by month i will process the record for each month. Is there any way to import csv file to two demension array[recordbumber][field]? Or maybe you could advise how is better to handle with this task?
by nohandle at 2012-12-07 05:23:40
Hi, import the file to Powrshell using Import-CSV cmdlet and save the output to variable. In the variable you should have collection of objects that has properties which are taken from the files header.
Use pipelines (|), Sort-object -Property <property name to sort by> and Group-Object -Property <property name to group by> and try to get the desired output (You’ll probably need to pipe it to Format-table or out-gridview to see it correctly)
If you have Month property in the CSV it should be easy, If don’t you’ll have to create the property yourself first and then orded by it:
#creating test object collection
#new array
$test = @()
$hash = @{wholeDate="2012-12-24"}
$test += New-Object psobject -Property $hash
$hash = @{wholeDate="2012-12-31"}
$test += New-Object psobject -Property $hash
$hash = @{wholeDate="2012-1-10"}
$test += New-Object psobject -Property $hash


#testing
$test|select *, @{name="Month"; expression={($.WholeDate -split "-")[1]}} | group -Property Month

Count Name Group
----- ---- -----
2 12 {@{wholeDate=2012-12-24; Month=12}, @{wholeDate=2012-12-31; Month=12}}
1 1 {@{wholeDate=2012-1-10; Month=1}}
by nohandle at 2012-12-07 05:29:10
select *, @{name="Month"; expression={($.WholeDate -split "-")[1]}}
select * (all properties), and add property named Month that is calculated as such:
Take the property wholedate in the object that is currently in the pipeline ($_) and split the value by - (the value is 2012-12-24 for the first one in collection) resulting in this array:
2012
12
24
Arrays are indexed from zero and we are looking for the month so we will choose the one with index 1
(2012,12,24)[1]
making the value of the Month property 12.

The property object is passed further to the pipeline (in our case to output), the original object is not affected.
by SHURALE at 2012-12-10 03:19:26
Hi nohandle !

I really appreciate your help. You provided me with good exapmple. So i will try to implement it now. Thanks!Good luck!