Removing substring and commit change to object

This topic contains 8 replies, has 3 voices, and was last updated by  Max Kozlov 2 years, 11 months ago.

  • Author
  • #27478

    Tony Pagliaro

    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?

  • #27483

    Max Kozlov

    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

  • #27484

    Simon Wåhlin

    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:

    $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

    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.

  • #27503

    Tony Pagliaro

    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.

    Your solution looks doable, I will try it out and report back.

  • #27510

    Max Kozlov

    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
    hostname,device name,device address

    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'
    $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

  • #27513

    Max Kozlov

    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

  • #27517

    Tony Pagliaro

    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 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!

  • #27518

    Simon Wåhlin

    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 🙂

  • #27520

    Max Kozlov

    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 :))

You must be logged in to reply to this topic.