CSV: Read first row

This topic contains 18 replies, has 3 voices, and was last updated by  Olaf Soyk 3 months, 2 weeks ago.

  • Author
    Posts
  • #92786

    baschi
    Participant

    Hey guys.
    I'm using a CSV File as a kind of an Database in an backupscript.
    CSV Looks like:
    Signature Autocorrect Dictionary
    ——— ———– ———-
    20180131 20180133 20180133
    20180132 20180132 20180132
    20180102 20180102 20180102

    I Need to read the first row of signature for example.
    After I importet the csv I use $csvcsv.Signature to get the whole Signature column, so far so good but how can I read the first row only and save it into a var?
    I also want to delete the first row of Signature after I saved it to the var, and save it to the csv file.
    What I tried so far:
    $csvcsv.Signature [0] and $csvcsv.Signature [0,1,2,3,4,5]

    Thanks guys,
    baschi

  • #92788

    Jon
    Participant
    $csv.signature | select -first 1 -outvariable variablename
    • #93067

      baschi
      Participant

      Awesome Jon that worked, thanks!
      I have another challenge, I add with
      {
      $csvcsv += [PSCustomObject]@{
      Dictionary2 = $Datum }
      }
      values to the csv object and export it afterwards but it seems that I always get a new line for each entrie.

      PS Z:\> $csvcsv

      Signature Autocorrect Dictionary1 Dictionary2
      ——— ———– ———– ———–
      00000000 00000000 00000000 00000000
      20180206
      20180206
      20180206

      (in this forum they delete the spaces in my result it looks like this: http://prntscr.com/iawjwi )

      Best Regards,
      baschi

    • #93084

      Olaf Soyk
      Participant

      baschi,
      the following code might help you to understand:

      $Datum = Get-Date -Format 'yyyyMMdd'
      
      $RawCsv = @'
      Signature;Autocorrect;Dictionary
      20180131;20180133;20180133  
      20180132;20180132;20180132  
      20180102;20180102;20180102
      '@
      
      $Csv = ConvertFrom-Csv -InputObject $RawCsv -Delimiter ';'
      $NewCsv = $Csv | ForEach-Object {
          [PSCustomObject]@{
              Signature    = $_.Signature
              Autocorrect  = $_.Autocorrect
              Dictionary   = $_.Dictionary
              Disctionary1 = $Datum
          }
      }
      $NewCsv
      
    • #93150

      baschi
      Participant

      Sorry no I really don't. The Output of $csv and $newcsv are the same even the types, when I check with get-member.

      PS Z:\> $Csv
      
      Signature Autocorrect Dictionary
      --------- ----------- ----------
      20180131  20180133    20180133  
      20180132  20180132    20180132  
      20180102  20180102    20180102  
      
      
      
      PS Z:\> $newcsv
      
      Signature Autocorrect Dictionary Disctionary1
      --------- ----------- ---------- ------------
      20180131  20180133    20180133   20180207    
      20180132  20180132    20180132   20180207    
      20180102  20180102    20180102   20180207    
      
      
      
      PS Z:\> $newcsvgm
      
      PS Z:\> $newcsv| gm
      
      
         TypeName: System.Management.Automation.PSCustomObject
      
      Name         MemberType   Definition                         
      ----         ----------   ----------                         
      Equals       Method       bool Equals(System.Object obj)     
      GetHashCode  Method       int GetHashCode()                  
      GetType      Method       type GetType()                     
      ToString     Method       string ToString()                  
      Autocorrect  NoteProperty string Autocorrect=20180133        
      Dictionary   NoteProperty string Dictionary=20180133         
      Disctionary1 NoteProperty System.String Disctionary1=20180207
      Signature    NoteProperty string Signature=20180131          
      
      
      
      PS Z:\> $csv| gm
      
      
         TypeName: System.Management.Automation.PSCustomObject
      
      Name        MemberType   Definition                    
      ----        ----------   ----------                    
      Equals      Method       bool Equals(System.Object obj)
      GetHashCode Method       int GetHashCode()             
      GetType     Method       type GetType()                
      ToString    Method       string ToString()             
      Autocorrect NoteProperty string Autocorrect=20180133   
      Dictionary  NoteProperty string Dictionary=20180133    
      Signature   NoteProperty string Signature=20180131     

      this is how I create the csv:

       $csvfill = @(
        [pscustomobject]@{
      
      
      "Signature"="00000000"
      "Autocorrect"="00000000"
      "Dictionary1"="00000000"
      "Dictionary2"="00000000"
      }
      )
      $csvfill|export-csv -Path $Csvpath -NoTypeInformation

      I think I need to manipulate the object different than I do, but I don't know how to.
      This creates always a new row:

      $csvcsv += [PSCustomObject]@{
          Dictionary1 = $Datum    }
    • #93153

      Olaf Soyk
      Participant

      baschi,
      probably I've got something wrong. What is it what you actually try to achieve?
      Of course the types are the same. But $Csv and $NewCsv have a distinct difference. $NewCsv has 1 column more.

    • #93156

      baschi
      Participant

      thanks Olaf. I try to add one row to a defined column.
      F.e.:
      add one entry to "Signature" = "20180101" (manipulating the customobject and writing it back to the csv after)
      the next step would be to delete the first entry of it.
      F.e.:
      I added one entry to Signature and have
      Siganture
      "20180108"
      "20180101"
      "20180102"
      "20180101"

      now as I have 4 rows I would like to delete the first one.
      Signature
      "20180101"
      "20180102"
      "20180101"

      My whish would be something like:
      $csv.Signature +="20180101" but that isn't working. I think I have to manipulate the object but I don't know how.
      This code always adds another row which is not my goal I want to fill per column.

      $csvcsv += [PSCustomObject]@{
          Autocorrect = $Datum    }
    • #93159

      Olaf Soyk
      Participant

      baschi,
      for csv data you cannot delete one single "cell" in one comlumn and leave the rest like it is. You have to tread one line completely at a time. You could make a single cell "empty". ... something like this:

      $Datum = Get-Date -Format 'yyyyMMdd'
      
      $RawCsv = @'
      Signature;Autocorrect;Dictionary
      20180131;20180133;20180133  
      20180132;20180132;20180132  
      20180102;20180102;20180102
      '@
      
      $Csv = ConvertFrom-Csv -InputObject $RawCsv -Delimiter ';'
      $NewCsv = $Csv | ForEach-Object {
          If($_.Signature -eq '20180132'){
              $_.Signature = ''
          }
          [PSCustomObject]@{
              Signature    = $_.Signature
              Autocorrect  = $_.Autocorrect
              Dictionary   = $_.Dictionary
              Disctionary1 = $Datum
          }
      }
      $NewCsv
    • #93160

      baschi
      Participant

      But then I would have to know what the value of the cell, but I don't know it. I simply want to delete the first entry in Signature f.e.

      May you know how I can add single column entries?
      Here is how I could manipulate one column entrie but I can't delete it and still not add:

      $csvcsv[0].Signature="xyz"
    • #93162

      Olaf Soyk
      Participant

      If you want to delete the first value of signature you can do something like this:

      $Datum = Get-Date -Format 'yyyyMMdd'
      $count = 0
      $RawCsv = @'
      Signature;Autocorrect;Dictionary
      20180131;20180133;20180133  
      20180132;20180132;20180132  
      20180102;20180102;20180102
      '@
      
      $Csv = ConvertFrom-Csv -InputObject $RawCsv -Delimiter ';'
      $NewCsv = $Csv | ForEach-Object {
          If($Count -eq 0){
              $_.Signature = ''
          }
          [PSCustomObject]@{
              Signature    = $_.Signature
              Autocorrect  = $_.Autocorrect
              Dictionary   = $_.Dictionary
              Disctionary1 = $Datum
          }
          $count++
      }
      $NewCsv

      May you know how I can add single column entries?

      For csv data you can't. You have to add a complete row. This row can have empty "cells" but it has to be complete.

    • #93165

      baschi
      Participant

      Thanks I can also do it with

      $csvcsv[0].Signature=""

      but it doesn't really delete my entry, it just empties it. The csv file will look like:

      "Signature","Autocorrect","Dictionary","Disctionary1"
      "","20180133","20180133 ","20180207"
      "20180132","20180132","20180132 ","20180207"
      "20180102","20180102","20180102","20180207"
    • #93166

      Olaf Soyk
      Participant

      Yes. That's the way it works. What's the problem with that? What do you actually try to achieve? You might want to do something what's not necessary!? 😉

    • #93169

      baschi
      Participant

      I think you could be right. I try to use this as an kind of database where I write the dates when I sucessfully stored a backup.

    • #93178

      Olaf Soyk
      Participant

      OK, I can imagine something like that but I still don't understand why you try that hard to delete one cell instead of simply "cleaning" it. Try to think about following sscenario: When you try to delete a cell in Excel it will ask you how would you like to fill up the hole – with cells from the right or from the bottom. How do think Powershell should deal with this dilemma? 😉

    • #93181

      baschi
      Participant

      Powershell should from my point of view have some option to do this.
      I do it cause I use it for versioning. When I have more then 3 rows, so 4, I want to delete the first one and the backup of it.

    • #93184

      Olaf Soyk
      Participant

      You can delete the first cell of the column Signature but you would have to delete the rest of this row as well. Do the cells in one row have any relation with each other? If not – a csv data set might be the wrong approach to store the information.

    • #93186

      baschi
      Participant

      You are right, they aren't related to each other. What would be better to store values in a data on harddrive?

    • #93189

      Olaf Soyk
      Participant

      If these information aren't structured data you could store them in a plain text file. But you would have to parse it by yourself then.

  • #93079

    Jon
    Participant

    No clue, sorry! Hopefully someone else will be able to chime in.

You must be logged in to reply to this topic.