I have a CSV file with computer info, including a column for hostname. Data in this column sometimes has notes, so an entry could read “COMPUTERNAME (offline friday)” …for instance.
I have imported the CSV as an object variable, and tried using a foreach loop with an indexof looking for a space character, and a substring doo-dad to get the new text string. I don’t know how to commit the change in the new foreach variable back into the table of the original object.
I also want to make sure no errors if a value has no space in the string. I used an if-break but unsure that’s the right choice.
$CSVObject = gc SomeFile.csv
$devices = $CSVObject."device name"
foreach ($device in $devices) {
[int]$charSpace = $device.indexof(" ")
if ($charSpace -lt 1) {BREAK}
$device = $device.substring(0,$charSpace) #what do i do here?
}
I did something similar earlier in my script to remove the first 24 lines of data, because the column headers start on line 25. This worked.
(gc $CSV)[24…((gc $CSV).length)] |out-file $CSV
But how do I commit changes to an object property value without clobbering the entire file?
If I understand you properly, you need something similar to
# Importing file as csv you protect object structure, gc load file as array of strings, but import-csv make objects
$devices = import-csv somefile.csv
#using foreach you modify each object property 'device name',
# replacing space and all characters after it to nothing
foreach ($device in $devices) {
$device.'device name' = $device.'device name' -replace '\s.*'
}
#save result to output file
$devices | export-csv somefile_out.csv
take a look to -Delimiter and -Encoding parameters for xxxxx-csv cmdlets if you do not get properly formatted objects after first line
Hi Tony,
You can use Import-CSV to read a csv-file and get objects.
Objects can be written to a CSV-file using Export-Csv
I assume that the hostname is found in the column named hostname.
For each object read, remove any whitespace in beginning and end of hostname, split the hostname on first whitespace found and keep only the first part.
Assign the new value to property hostname
Export objects from pipeline to a new csv-file using Export-Csv.
This could look something like this:
[pre]
$Delimiter = ‘,’
$InputPath = ‘C:\folder\somefile.csv’
$OutputPath = ‘C:\folder\somefile-fixed.csv’
$Column = ‘Hostname’
Import-Csv -Delimiter $Delimiter -Path $InputPath |
Foreach{$.$Column = ($.$Column.Trim() -Split ‘\s’,2)[0];$_} |
Export-Csv -Path $OutputPath -Delimiter $Delimiter -NoTypeInformation
[/pre]
To break the critical part up, $_.hostname.Trim() will remove any whitespace (space, tab and such) from the start and end of astring.
-split is an operator used to split a text in many parts. For more details see Get-Help about_Split. The first parameter given to -Split is ‘\s’, which is regular expression for any whitespace, the second parameter 2 tells -split to split in maximum two strings.
[0] is an index saying that we want the first string from the split.
The semicolon is just a replacement for a linebreak and the $_ writes the current object back to the pipeline.
I hope this is what you were looking for.
Thanks both of you for your replies.
Max, my CSV file is not just a list of hostnames, it has other data too. So I would have to first import the csv, then isolate the hostname column before doing the foreach. This is where I got tripped up.
Simon,
Your solution looks doable, I will try it out and report back.
Tony, my solution and Simon’s solution almost the same 
and You improperly understand both. We do not ‘isolate’ needed columns, we modify just one column (object property) and leave others intact.
Both variants assume that you have several columns in csv, I assume one of it is “device name”, Simon - ‘hostname’, other columns can be anything
like
hostname,device name,device address
aaa,dev1,addr1
bbb,dev2,addr2
this table imported by import-csv converted in array of two objects with three properties each.
$devices[0].hostname='aaa, $devices[0].‘device name’=‘dev1’; $devices[0].‘device address’ = ‘addr1’
and
$devices[1].hostname='bbb, $devices[1].‘device name’=‘dev2’; $devices[1].‘device address’ = ‘addr2’
In my solution I modify only one column ‘device name’ and Simon’s solution modify colimn ‘hostname’
after modify cycle both save result to csv, converting objects back to table by export-csv
last difference - using of Trim() by Simon. that’s all
btw, using -split and get first array element more ineffective way than -replace ‘\s.*’ to nothing (‘’)
but both do the same - get string part before first space character and save it back to object property
The output should be an object variable which I will Eventually export to CSV.
So here is the relevant variable I’ve defined at this problem spot in my script: The “device name” value in the first line has some test after a space, so it should pick it up.
PS D:\ps\test3> $InputBaselineCSV
Directory: D:\ps\test3
Mode LastWriteTime Length Name
-a— 7/15/2015 12:15 AM 2529570 2015-jul-10_04-17_report.CSV
Now here’s me running the block to try and take this file, import it, and remove the notes from the “device name” column. Then I check the value of “device name” in the first line.
PS D:\ps\test3> $InputBaselineCSVObject = Import-Csv $InputBaselineCSV | Foreach{$.‘device name’ = ($.‘device name’.Trim() -Split ‘/s’,2)[0];$_}
PS D:\ps\test3> $InputBaselineCSVObject[0]
Customer/Site : HQ-Production
Device Name : ADAM-HP 3ert5
Device Class : Workstations - Windows
Patch Name : Intel - Other hardware - Intel(R) Active Management Technology - SOL
Product : Windows 7
Classification : Drivers
Published Date : 2012-May-15
Approval Status : Not Approved
Installation Status : Not Installed since 2015-Mar-13
…so that was Simon’s method. You’ll notice I changed it a bit to keep the data as an object and to address some coding errors you would see if you pasted it into the ISE (pipe after a bracket a couple times, for example).
Here’s max’s method, you’ll see i tweaked it to match my variables.
PS D:\ps\test3> $InputBaselineCSVObject = Import-Csv $InputBaselineCSV
foreach ($device in $InputBaselineCSVObject) {
$device.'device name' = $device.'device name' -replace '\s.*'
}
And the result…!
PS D:\ps\test3> $InputBaselineCSVObject[0]
Customer/Site : RFA-HQ_IDC-Production
Device Name : ADAM-HP
Device Class : Workstations - Windows
Patch Name : Intel - Other hardware - Intel(R) Active Management Technology - SOL
Product : Windows 7
Classification : Drivers
Published Date : 2012-May-15
Approval Status : Not Approved
Installation Status : Not Installed since 2015-Mar-13
It worked! I guess it just goes to show you, don't judge a book by its grammar ;)
Max, if you're ESL then you might not get that joke but thanks and sorry for doubting you!
I’m glad you made it work!
The reason the first try doesnt work is that you typed the “backslash” the wrong way. It should be:
-Split '\s'
Just as Max pointed out earlier, the two examples do almost exactly the same thing, I had my browser open and didn’t refresh before posting so I didn’t see Max’s response, I’m sorry for that Max, really didn’t mean any offense
I do not feel myself offended 
I’m glad that Tony make it to work.
And we always must learn better ways to do our work, but for that we must understand what we doing in every line of our scripts.
I feel pain when I see badly planned script or difficult way to do something simple :))