Reading and update a CSV file

PowerShell Help
1

Hello all,

I am pretty new here and new to powershell as well. Hoping i can learn and get some ideas on my problem at hand.

I want to maintain a CSV file, like this, where the date is the key value and data in other columns is associated with the date. For example if i want to query the value of Throughput on 05/03/2020 i should get back 4. Secondly i also want to keep updating it on a daily basis by adding new rows to it.

<colgroup><col style="width: 65pt;" span="9" width="87" /> </colgroup>
Date Throughput SourceLatency DestinationLatency Scheduled Synced Completed TotalData Failed
05/03/2020 4 20 2 5 2 1 10 1
06/03/2020 5 30 3 7 3 2 20 2
07/03/2020 6 40 4 9 4 3 30 3
08/03/2020 7 50 5 11 5 4 40 4
09/03/2020 8 60 6 13 6 5 50 5
i tried using a pscustomobject like this however i cannot query values for a given date and if i try and append this to a CSV file the content is added to the last column instead of a new line
$Table = [PSCustomObject]@{
Date = "18/03/2020"
Throughput = '10'
SourceLatency = '20'
DestinationLatency = '30'
Scheduled = '2'
Synced = '4'
Completed = '5'
TotalData = '30'
Failed = '3'
MRS = 'MRS4'
}
Hope it makes sense and looking forward and tips and hints

 

 

2 
# Make some test data
1..5 | foreach { 
    [PSCustomObject]@{
        Date = “$(15 + $_)/03/2020”
        Throughput = $($_ + 5)
        SourceLatency = ’20’
        DestinationLatency = ’30’
        Scheduled = ‘2’
        Synced = ‘4’
        Completed = ‘5’
        TotalData = $(30 - $_)
        Failed = ‘3’
        MRS = ‘MRS4’
    }
} | Export-Csv .\test1.csv -NoTypeInformation

# Reading the data
$myData = Import-Csv .\test1.csv 

# Query the value of Throughput on 16/03/2020
($myData | where Date -EQ '16/03/2020').Throughput # 6

# Adding new data
[PSCustomObject]@{
    Date = “22/03/2020”
    Throughput = 22
    SourceLatency = ’20’
    DestinationLatency = ’30’
    Scheduled = ‘2’
    Synced = ‘4’
    Completed = ‘5’
    TotalData = 22
    Failed = ‘3’
    MRS = ‘MRS4’
} | Export-Csv .\test1.csv -NoTypeInformation -Append
3

Thanks a lot Sam. it works pretty well

When i am reading data it returning the value but its returning it twice, like this

PS /Users/varun> ($myData| where Date -EQ ‘16/03/2020’).sourcelatency
20
20

I can filter it again like this but is there a better way of doing it to get one value returned

PS /Users/varun> ($myData| where Date -EQ ‘16/03/2020’).sourcelatency[0]

4

What that means is that you have two rows that have a date value of ‘16/03/2020’, so you need to decide what you want to do in that situation. Do you want both results, or just the first one, or just the last one, and then set your code accordingly.

5

what he said :slight_smile: