add column value to an existing csv file-CodePudding

I have a csv where I have to fill data into 2 existing column which are blank.

I was trying the below code but it is still blank

$v1='1.1.1.1'
$v2 = '2.2.2.2'

Import-Csv -Path "C:\New.csv" |
Select-Object *,@{Name='server_IP_address';Expression={$v1}},
@{Name="Server's Backup IP address";Expression={$v2}} | 
Export-Csv "C:\Csv\New_1.csv" -NoTypeInformation

Please let me know what I am missing here

CodePudding user response：

Assumed you used the correct names for the existing headers something like this should work

$v1 = '1.1.1.1'
$v2 = '2.2.2.2'

Import-Csv -Path "C:\New.csv" | 
    Select-Object -ExcludeProperty 'server_IP_address', "Server's Backup IP address" |
        Select-Object *, @{Name = 'server_IP_address'; Expression = { $v1 } },
            @{Name = "Server's Backup IP address"; Expression = { $v2 } } | 
                Export-Csv "C:\Csv\New_1.csv" -NoTypeInformation

CodePudding user response：

fill data into 2 existing columns

I was trying the below code but it is still blank

Your code implies two things:

server_IP_address and Server's Backup IP address are not the names of existing properties (columns), because trying to use Select-Object to create calculated properties (@{ Name=...; Expression=... }) whose names are among the existing columns, selected with *, would fail, due to duplicate property names:
- Select-Object: The property cannot be processed because the property "<name>" already exists.
If your command did succeed, the implication is that you added two new columns to your CSV file.

To fill or update existing columns, the simplest approach is to use a ForEach-Object with property assignments:

Import-Csv -Path "C:\New.csv" |
  ForEach-Object { 
    $_.'server_IP_address' = $v1
    $_."Server's Backup IP address" = $v2
  } | Export-Csv "C:\Csv\New_1.csv" -NoTypeInformation

To add the desired values under new column names, stick with your existing approach, but exclude the original properties (columns) with -ExcludeProperty - though it is worth heeding Theo's recommendation to avoid spaces and ' chars. in column names, so as not to complicate later programmatic processing.

Note:

In the code below I'm assuming sample names server_IP and server_BackupIP as the original column names; adjust as needed.
The new columns are added after the existing ones; to prepend them, use -Property @{ ... }, @{ ... }, *; to insert them between existing columns would require explicitly enumerating the column names and inserting the calculated properties (@{ ... }) as needed (avoiding the explicit enumeration would require a more complex, reflection-based approach via ForEach-Object).

Import-Csv -Path "C:\New.csv" |
  Select-Object -ExcludeProperty server_IP, server_BackupIP -Property *,   
    @{ Name='server_IP_address';Expression={ $v1 } },
    @{ Name="Server's Backup IP address";Expression={ $v2 } } |
      Export-Csv "C:\Csv\New_1.csv" -NoTypeInformation

^{As an aside: If you were to only exclude properties, without adding new ones, a bug in Windows PowerShell would still require explicitly specifying -Property * in order for -ExcludeProperty to take effect; this bug has been fixed in PowerShell (Core) 7 .}

Note that you could use the same technique to effectively rename existing columns (while possibly also transforming their values), if you output (and possibly transform) their existing values from the Expression script blocks; e.g. Expression = { $_.server_IP }