Import-CSV Alter cell based on header name

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
localpct
Posts: 320
Joined: Wed Sep 09, 2015 12:53 pm

Import-CSV Alter cell based on header name

Post by localpct »

I cannot seem to figure out a way to replace text in a cell, for a certain header:

Import-Csv $Config | Where-Object { ($_.Name -eq 'OneNote Notebooks') | ForEach-Object {

$_.Destination -eq "\\Server\$user\OneNote Notebooks"

}
}

Currently, the cell for Destination states \\$server\$user\Documents\One Note, so I just want to replace it

User avatar
mxtrinidad
Site Admin
Posts: 399
Joined: Sun Mar 03, 2013 12:42 pm

Re: Import-CSV Alter cell based on header name

Post by mxtrinidad »

The code you presented is not correct, and I'm not sure why you are using the Import-CSV cmdlet as you already have the $Config object created.

You better off work with the existing object $Config, build another object with the updated changes. It is not clear where you want to display the results. It is a Datagrid view cell, or do you mean an Excel cell.

In the object, property look for the *.replace() method. Then you could do "$_.Destination.replace('existing value', 'new value')" but this will no retain the change value. You will need to rebuild the object as of type [Array], see sample below:

Code: Select all

## - Create a type [Array] object:
❯ [Array] $myArrayobj = foreach($item in $Config){
$item
};

## - Should display object as type System.Array:
❯ $myArrayObj.GetType() 

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

## - List Array object before the change:
❯ $myArrayObj
Name,version,Destination
Mantis,1.0,\\Server\$user\mantis
Office 365,1.0,\\Server\$user\Office365
One NoteBook,0.9,\\Server\$user\OneNote Notebooks
PowerShell,7,\\Server\$user\PowerShell7
AzureCLI,1.0,\\Server\$user\AzureCLI

## - One line to replace value of element in the array: (will replace the string but will update the object)
❯ $myArrayObj.replace('\\Server\$user\OneNote Notebooks','\\Server\$user\MyNotebooks')

## - Saved update: 
$myArrayObjUpdated = $myArrayObj.replace('\\Server\$user\OneNote Notebooks','\\Server\$user\MyNotebooks')
I hope this example will help you!

User avatar
localpct
Posts: 320
Joined: Wed Sep 09, 2015 12:53 pm

Re: Import-CSV Alter cell based on header name

Post by localpct »

I apologize...

$config is a CSV file that I pull from a web server. So technically it's:
and OneNote Notebooks is a header in the CSV

Import-Csv backup.csv | Where-Object { ($_.Name -eq 'OneNote Notebooks') | ForEach-Object {

$_.Destination -eq "\\Server\$user\OneNote Notebooks"

}
}

User avatar
mxtrinidad
Site Admin
Posts: 399
Joined: Sun Mar 03, 2013 12:42 pm

Re: Import-CSV Alter cell based on header name

Post by mxtrinidad »

It's, not clear yet, but getting there. Also, providing a sample of the *.csv file would have help.
But, you'll need to rebuild the object.

The line: $_.Destination -eq "\\Server\$user\OneNote Notebooks", only returns "True" or "False". And, you're trying to do everything on a one-liner.
For clarity, you'll need to add code,

In the code below, I created a sample CSV file, which contains a 3 column header and data. Then use the following code to look for the name field value and change the destination with the following:

Code: Select all

$Config = Import-Csv c:\MyScripts\TestCsv.csv;

$Config

$Config.GetType()

## - Create a updated object:
$ConfigUpdated = foreach($item in $Config){
	if($item.Name -eq 'OneNote Notebooks'){
		$chgDestination = '\\Server\User\MyNoteBook';
		$updatedline = $item | Select-Object name, version, `
			@{ label = 'Destination'; Expression = { $chgDestination } };
		$updatedline;
	} else {
		$item | Select-Object name, version, destination;
	}
}; $ConfigUpdated;

This will create the new object $ConfigUpdated with the changes to be displayed on a Datagrid view. Or, if it's exported back to a CSV file.

:)

jvierra
Posts: 14543
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Import-CSV Alter cell based on header name

Post by jvierra »

This would be easier to understand if the code was formatted better.

Code: Select all

Import-Csv $Config |
    Where-Object { ($_.Name -eq 'OneNote Notebooks') | 
        ForEach-Object {
            $_.Destination = "\\Server\$user\OneNote"
        }
    }
Unfortunately your question is vague and missing information. The above will just replace the value dependent on the "Name" field.
Clarify and post a sample of your CSV.

jvierra
Posts: 14543
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Import-CSV Alter cell based on header name

Post by jvierra »

The following will update a Csv when the Name column contains the value and output the complete CSV to a new Csv.

Code: Select all

Import-Csv $Config | 
    ForEach-Object {
        if($_.Name -eq 'OneNote Notebooks'){
            $_.Destination = "\\Server\$user\OneNote"
        }
        $_
    } |
    Export-Csv newfile.csv

User avatar
localpct
Posts: 320
Joined: Wed Sep 09, 2015 12:53 pm

Re: Import-CSV Alter cell based on header name

Post by localpct »

jvierra wrote:
Mon Mar 02, 2020 3:10 pm
The following will update a Csv when the Name column contains the value and output the complete CSV to a new Csv.

Code: Select all

Import-Csv $Config | 
    ForEach-Object {
        if($_.Name -eq 'OneNote Notebooks'){
            $_.Destination = "\\Server\$user\OneNote"
        }
        $_
    } |
    Export-Csv newfile.csv
This worked. My CSV just fills in variables for a robocopy script I use in a WinPE environment.

Locked