Author Posts

February 2, 2018 at 1:15 pm

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

February 2, 2018 at 1:20 pm

$csv.signature | select -first 1 -outvariable variablename

February 6, 2018 at 1:55 pm

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

February 6, 2018 at 3:25 pm

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

February 6, 2018 at 4:13 pm

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

February 7, 2018 at 8:55 am

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    }

February 7, 2018 at 10:03 am

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.

February 7, 2018 at 10:11 am

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    }

February 7, 2018 at 10:22 am

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

February 7, 2018 at 10:25 am

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"

February 7, 2018 at 10:33 am

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.

February 7, 2018 at 10:42 am

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"

February 7, 2018 at 11:23 am

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!? 😉

February 7, 2018 at 11:48 am

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.

February 7, 2018 at 12:30 pm

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? 😉

February 7, 2018 at 12:41 pm

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.

February 7, 2018 at 12:56 pm

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.

February 7, 2018 at 1:14 pm

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

February 7, 2018 at 1:23 pm

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.