Pfthb

I need to change a CSV column, that has no headers.

Here's my test data WITH HEADERS:


data.csv:

order|color|shape
1|green|square
1|red|rectangle

And here's what i used to change the first column to 0.

$ImportedCSV = Import-CSV data.csv -Delimiter '|'
$NewCSV = Foreach ($Entry in $ImportedCsv) 
 Switch ($Entry."order") 
 1 $Entry."order" = "0"
 
 $Entry

$NewCSV | Export-CSV done.csv -Delimiter '|' -NoTypeInformation

(Get-Content -Path done.csv ).Replace('"','') | Set-Content -Path done.csv

The result is this:

order|color|shape
0|green|square
0|red|rectangle

My data.csv wont have headers to start with.


Can "Switch ($Entry."order")" use column numbers rather than the header name?

So, something like "Column 1" rather then "order"?



Please no mention of simply adding the headers then delete the headers when done.

In the end, i need to change a column's data based on column number, rather than header name.


So...replace ($Entry."order") with what?


($Entry.Column 1) = nope

($Entry.Column-1) = nope


Thanks for any help.

Let's assume you have:

data_noheader.csv

7|green|square
8|red|rectangle
9|blue|triangle

And you want to change the green square (in the first row) to purple and the item where col0 is 8 to gold star.

A few things change:

1. Explicitly set the header with -Header so that the first line isn't treated as the header.


2. Edit $NewCSV in place rather than composing it with ForEach (this isn't required but makes the code a bit easier to follow and work with)


3. Instead of looping ForEach and looking at the column name the row is selected explicitly for things that you want to change by the actual row.


4. For things that you want to change based on the value of a column use a similar pattern to the one you used before and just check the value.


5. Strip the header back off with Select-Object -Skip 1
   

$NewCSV = Import-CSV data_noheader.csv -Delimiter '|' -Header @("col0", "col1", "col2")
$NewCSV[0].col1 = "purple" # Set based on the actual row position
ForEach ($Entry in $NewCSV) 
 If ($Entry.col0 -Eq "8") # Modify data in other columns in a row based on the value of a particular column
 $Entry.col1 = "gold"
 $Entry.col2 = "star"
 

$NewCSV | Export-CSV done_noheader.csv -Delimiter '|' -NoTypeInformation

(Get-Content -Path done_noheader.csv ).Replace('"','') | Select-Object -Skip 1 | Set-Content -Path done_noheader.csv

The result in done_noheader.csv will now be:

7|purple|square
8|gold|star
9|blue|triangle

In case the delimiter is known and unique (not part of quoted column data)

You can obtain column count

$delim = '|'
$Cols = (get-Content .data.csv|select -first 1).split($delim).Count

And automatically apply numbered Header

$csv = Import-Csv .data.csv -Delim $delim -Header @(1..$Cols)

> $csv

1 2 3
- - -
7 purple square
8 gold star
9 blue triangle

Manipulate the columns in a ForEach

$csv | ForEach-Object $_.1 = 0 

> $csv

1 2 3
- - -
0 purple square
0 gold star
0 blue triangle

And lateron save, stripping quotes and header if neccessary.