Need help understanding difference between Get-Content and Import-Csv in Powershell

I'm having trouble understanding when to use Get-Content and when to use Import-Csv for modifying a CSV file. Here is an example of my file, which is an output of a hash table:

Name Key Value AOI1\\ABC1 AOI1\\ABC1 TRUE AOI2\\DEF2 AOI2\\DEF2 TRUE \#AOI3\#\\GHI3 \#AOI3\#\\GHI3 FALSE 

I need to do the following to the file:

1. Remove '\\' and everything after it in the Name column
2. Remove '\\' and everything before it in the Key column
3. Replace '\#' with '#' in the last row
4. Rename the column headers

The result should look like this:

Loc FName Result AOI1 ABC1 TRUE AOI2 DEF2 TRUE #AOI3# GHI3 FALSE 

For removing the '\\' and everything after in the Name column, I came up with this script:

Import-Csv c:\test.csv | % {$_.Name.split('\\\\')[0]} 

This outputs the following:

AOI1 AOI2 

However, I want to be able to write everything back to the same Csv file, so I tried changing it to:

Get-Content c:\test.csv | % {$_.Name.split('\\\\')[0]} 

However, I ended up with a "You cannot call a method on a null-valued expression" error message. If I keep the Import-Csv and modify the script to also do item 4, I get the same error message. Here's that script:

Import-Csv U:\To_Delete\Layer_search\results_STACK_layers.csv | Select-Object @{n='AOI';e={$_.'Name'}}, @{n='LAYER';e={$_.'Key'}}, @{n='IN MAP';e={$_.'Value'}} | % {$_.Name.split('\\\\')[0]} 

So, how do I modify my CSV and output it to the same CSV afterwards? What could be causing that error message?

Sorry for the long-winded post, but I wanted to provide enough examples.

Thanks!

1 Answer

presuming you cannot use the advice from Bill_Stewart about fixing the source, here is one way to do the conversion. [grin] part of your problem was your use of the string split method instead of the string split operator. the 1st treats every character as a split target. the 2nd uses regex to split on the split string instead of on the characters in the split string.

# fake reading in a CSV file # in real life, use Import-CSV $InStuff = @' Name, Key, Value AOI1\\ABC1, AOI1\\ABC1, TRUE AOI2\\DEF2, AOI2\\DEF2, TRUE \#AOI3\#\\GHI3, \#AOI3\#\\GHI3, FALSE '@ | ConvertFrom-Csv $Results = foreach ($IS_Item in $InStuff) { [PSCustomObject]@{ # new version removes the unwanted "\" from the ends of the "Loc" value #Loc = ($IS_Item.Name -split [regex]::Escape('\\'))[0] Loc = ($IS_Item.Name -split [regex]::Escape('\\'))[0].Replace('\') FName = ($IS_Item.Key -split [regex]::Escape('\\'))[1] Result = [convert]::ToBoolean($IS_Item.Value) } } # on screen $Results # send to a CSV file $Results | Export-Csv -LiteralPath "$env:TEMP\Heather_RearrangedCSV.csv" -NoTypeInformation 

on screen ...

Loc FName Result --- ----- ------ AOI1 ABC1 True AOI2 DEF2 True #AOI3# GHI3 False 

CSV file content ...

"Loc","FName","Result" "AOI1","ABC1","True" "AOI2","DEF2","True" "#AOI3#","GHI3","False"