Reading and update a CSV file

Welcome Forums General PowerShell Q&A Reading and update a CSV file

Viewing 4 reply threads
  • Author
    Posts
    • #211125
      Participant
      Topics: 1
      Replies: 1
      Points: 14
      Rank: Member

      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

       

       

    • #211173
      Participant
      Topics: 12
      Replies: 519
      Points: 1,194
      Helping Hand
      Rank: Community Hero
      # 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
      
    • #211407
      Participant
      Topics: 1
      Replies: 1
      Points: 14
      Rank: Member

      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]

    • #211419
      Participant
      Topics: 6
      Replies: 667
      Points: 97
      Helping Hand
      Rank: Member

      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.

    • #211476
      Participant
      Topics: 12
      Replies: 519
      Points: 1,194
      Helping Hand
      Rank: Community Hero

      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.

      what he said 🙂

Viewing 4 reply threads
  • You must be logged in to reply to this topic.