Help with CSV

PowerShell Help
1

I’m going to start getting a csv file every week from which I need to create a report. I really need to script this but need some help!

Files shared using GitHub.

Fruits.csv has many products (object) where “[;” is a unique ID for the object name. The square bracket followed by the unique ID number and a semi-colon always follows a friendly name for the product e.g. mybananas [99;]. Following this first semi-colon may or may not be location names of the product that for this report are irrelevant. Need the ability to process only those products (via unique IDs) that are provided in in a filed called process_these.txt
Many time events (time). Each object has an entry for each time. This point in time is what the values from each object need to be grouped by.
Each object has a quantity for each time recorded (quantity).
Each object has two other pieces of information in d1 and d2 (d1, d2).

From this I need to create an object (maybe a CSV) with the following

For each “time”:

Process only those “objects” with unique IDs listed in process_these.txt.

Export a CSV with the following headers: Time,sumquantity,sumd1,averaged2

E.g. taking the data from time 01/01/2017 01:02:01 I would expect to see the following:

$timeout; every “time” entry e.g. 01/01/2017 01:02:01
$sumquantity; sum of “quantity” from all objects at that time e.g. 11
$sumd1; sum of “d1” from all objects at that time e.g. 20
$averaged2; average of “d2” from all objects at that time e.g. 5

Example Csv-out file:
Time,sumquantity,sumd1,averaged2
01/01/2017 01:01:01,2,20,7
01/01/2017 01:02:01,11,20,5

2

And where your code ?
nobody write all code for you for free
but you can get help for special subjects
for example:

  • Q: I want to now how can I split object into id and location for future use
  • A:
  if ($line.object -match '(.*)\[(.*)\]') {
    $objectName = $matches[1]
    $objectID, $objectLocations = $matches[2] -split ';\s*' 
  }
3

Hello Max. Thank you.

My code is immature at the moment and just gets the input files and output file setup.
The logic of what to do with the csv once imported and grouped by ‘time’ is where I need some pointers as to what I should be doing next.

$fruits_csv = "C:\temp\fruits.csv"
$objectID_txt = "C:\temp\process_these.txt"
$data = Import-Csv $fruits_csv | Group-Object Time
$data | ForEach-Object{
#need help with the logic here    
    
} | export-csv c:\temp\report.csv -NoTypeInformation

Thanks,
JohnZ

4

so, you need this info:
grouping objects by property: Group-Object -Property xxx
sorting objects : Sort-Object -Property xxx
splitting one complex property info I showed above,
and finally You can add new Property for each object line like
$line | Add-Member -MemberType NoteProperty -Name ObjectID -Value $objectID

From these blocks you already can describe all of your logic.
and from this point you can get further help

5

Using fruits.csv and process_these.txt I know that the output file should end up as per the original post but I’m simply too much of a novice at this point to connect the dots.

Would you be able to show me an example for the time and sum of quantity at that time for only given object IDs that I’d like to export to a csv and I can work the remainder (sumd1, averaged2)?

6 
#csv simulation
$csv = 'object,time,quantity,d1,d2
mybananas [1; location1; location2],01/01/2017 01:01:01,1,10,5
mybananas [1; location1; location2],01/01/2017 01:02:01,9,10,9
myoranges [2; location1; location2],01/01/2017 01:01:01,1,10,9
myoranges [2; location1; location2],01/01/2017 01:02:01,2,10,1
my-prunes [3; location89],01/01/2017 01:01:01,1,10,9
my-prunes [3; location89],01/01/2017 01:02:01,2,10,9
my--pears [9;],01/01/2017 01:01:01,6,10,9
my--pears [9;],01/01/2017 01:02:01,6,10,9
' -split "`n" | ConvertFrom-Csv -Delimiter ','
#Add needed properties to csv
$csv | Foreach-Object {
  if ($_.object -match '(.*)\[(.*)\]') {
    $objectName = $matches[1]
    $objectID, $objectLocations = $matches[2] -split ';\s*' 
  }
  else {
    $objectName = ''
    $objectID, $objectLocations = '', @()
  }
  $_ | Add-Member -MemberType NoteProperty -Name ObjectID -Value $objectID
  $_ | Add-Member -MemberType NoteProperty -Name ObjectName -Value $objectName
  $_ | Add-Member -MemberType NoteProperty -Name ObjectLocations -Value $objectLocations
 }
# find objectid 1 and measure quantity example
$csv | Where-Object { $_.ObjectID -eq '1' } | Measure-Object -Property quantity -Sum
#group by time example
$csv | Where-Object { $_.ObjectID -eq '1' } | Group-Object -Property time
#in any above examples you can add "... | export-csv" to export info to csv or save it to variable "$var = ..."
#now it output info to screen so you can see the available properties to work with
#
# and there really useful code:
# take id's from file and sum quantity by id
# add objectid to results, export to csv
$ids = get-content d:\process_these.txt 
$ids | Foreach-Object {
   $id = $_
   $csv | Where-Object { $_.ObjectID -eq $id } | Measure-Object -Property quantity -Sum |
   # all existing measure properties + additional property - object id
   Select-Object *, @{name='ObjectID'; expression={$id}}
} |
Export-Csv -Delimiter ';' -NoTypeInformation -Path "d:\results.csv"
7

This is fantastic code. Being a novice I’m still not wrapping my head around how this could be tweaked so that the results.csv would show every ‘time’, and for each ‘time’ the sum of the ‘quantity’ for only those objects found in the ‘$_.ObjectID’? If you can show an example of the logic that would be needed to achieve this I can hopefully add to it the sum for the d1s at each time and the average of the d2s at each time. Thnx,JZ

Example output from the fruits.csv and process_these.txt:

8

if you need only selected ids

  1. you can filter csv for ids,

  2. group filtered by time,

  3. then for each .group

  4. call filter or group by id once more #if you need differeciate ids in one time

  5. and then measure several times for each property

  6. then save and export result

  7. where-object ( $_.objected -in $ids)

  8. group-object time

  9. foreach … {
    $data = $_.group
    …
    }

  10. ==1 or =~2

  11. $result = $datagroup | measure

  12. $data | add-member $result1
    $data | add-member $result2

  13. … | export-csv

and now your try :slight_smile:

9

Trying…failing to see where to add this.
I’ve only been able to work out how to add the properties to the csv that will get output for time, sumquantity, sumd1 and avgd2. Not sure if where I have this in the script is correct and can’t work out the logic from your last post to populate them. Any other pointers? Also, I don’t know what is meant by ==1 or =~2.
Thnx,JZ

### csv simulation
$csvin = 'object,time,quantity,d1,d2
mybananas [1; location1; location2],01/01/2017 01:01:01,1,10,5
mybananas [1; location1; location2],01/01/2017 01:02:01,9,10,9
myoranges [2; location1; location2],01/01/2017 01:01:01,1,10,9
myoranges [2; location1; location2],01/01/2017 01:02:01,2,10,1
my-prunes [3; location89],01/01/2017 01:01:01,1,10,9
my-prunes [3; location89],01/01/2017 01:02:01,2,10,9
my--pears [9;],01/01/2017 01:01:01,6,10,9
my--pears [9;],01/01/2017 01:02:01,6,10,9
' -split "`n" | ConvertFrom-Csv -Delimiter ','

### add needed properties to csv
$csvin | Foreach-Object {
  if ($_.object -match '(.*)\[(.*)\]') {
    $objectName = $matches[1]
    $objectID, $objectLocations = $matches[2] -split ';\s*' 
  }
  else {
    $objectName = ''
    $objectID, $objectLocations = '', @()
  }
  $_ | Add-Member -MemberType NoteProperty -Name ObjectID -Value $objectID
  $_ | Add-Member -MemberType NoteProperty -Name ObjectName -Value $objectName
  $_ | Add-Member -MemberType NoteProperty -Name ObjectLocations -Value $objectLocations
 }

### process selected products only (objectIDs)
$ids = Get-Content "d:\temp\process_these.txt" 
$ids | Foreach-Object {
   $id = $_
   $csvin | Where-Object { $_.ObjectID -eq $id } | Group-Object time
   
      ###??? | foreach {$data = $_.group}
      ###???$result = $datagroup | measure
      ###???==1 or =~2
      ###???$data | Add-Member -MemberType NoteProperty -Name Time -Value $time
      ###???$data | Add-Member -MemberType NoteProperty -Name Quantity -Value $sumquantity
      ###???$data | Add-Member -MemberType NoteProperty -Name d1s -Value $sumd1s
      ###???$data | Add-Member -MemberType NoteProperty -Name d2s -Value $avgd2s
} | Export-Csv -Delimiter ',' -NoTypeInformation -Path "d:\temp\results.csv"