Splitting values into two columns powershell csv file

Question

I have a PowerShell script that creates a csv file and neatly separates all user input. I need the remaining output to be split across the two headers and I'm struggling to find out how. Tried lots of different code but had no luck.

$Devices = read-host -Prompt "Enter Full Device Name" | out-file 'C:\Users\Public\Desktop\Devices.csv' -Force
$Find = ", "
$Replace = "`n"
$Arrange = (Get-Content 'C:\Users\Public\Desktop\Devices.csv') -replace "$Find","$Replace" | Set-Content 'C:\Users\Public\Desktop\Devices.csv' -Force
$CSV = import-csv 'C:\Users\Public\Desktop\Devices.csv' -Header "Firstname","Lastname" 
$CSV | Export-Csv -NoTypeInformation 'C:\Users\Public\Desktop\Devices.csv' 
$import = Import-Csv 'C:\Users\Public\Desktop\Devices.csv'

This is the output I currently have in the CSV:

This is the output I am after:

Could almost do with a foreach loop as the first and last names are likely to change as these are inputted using a variable

any help is appreciated.

Mathias R. Jessen · Accepted Answer · 2021-01-22 13:04:58Z

1

Export-Csv exports 1 column per distinct property of the input - so to get 2 columns, you need to pipe an object with 2 properties to Export-Csv.

# read device names, split into individual strings
$devices = Read-Host -Prompt "Enter Full Device Name(s)"
$devices = $devices -split ',\s*' |ForEach-Object Trim

# now create one object per device name
$records = $devices |ForEach-Object {
  # start by splitting the string into 2 on the first `-`
  $FirstName,$LastName = $_ -split '-',2

  # now create the object
  [pscustomobject]@{
    FirstName = $FirstName
    LastName = $LastName
  }
}

# ... and finally, export to CSV
$records |Export-Csv 'C:\Users\Public\Desktop\Devices.csv' -NoTypeInformation

If you want to retain the - as part of the FirstName value, change this line:

$FirstName,$LastName = $_ -split '-',2

to:

$FirstName,$LastName = $_ -split '(?<=-)',2

edited Jan 22, 2021 at 13:04

answered Jan 22, 2021 at 11:55

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Walter Mitty Over a year ago

Upvoted, with a quibble. Your code eliminates the hyphen when you split, right?

Mathias R. Jessen Over a year ago

@WalterMitty It does indeed, I've updated the answer to show how to retain it (if necessary) now :-)

Clint Oliveira · Accepted Answer · 2021-01-22 11:56:45Z

Try this out. Since I don't know what your input file looks like there might be some redundant steps in there. Feel free to modify the code to remove any redundant lines if you feel like.

$Devices = read-host -Prompt "Enter Full Device Name" | out-file 'C:\Users\Public\Desktop\Devices.csv' -Force
$Find = ", "
$Replace = "`n"
$Arrange = (Get-Content 'C:\Users\Public\Desktop\Devices.csv') -replace "$Find","$Replace" | Set-Content 'C:\Users\Public\Desktop\Devices.csv' -Force
$CSV = import-csv 'C:\Users\Public\Desktop\Devices.csv' -Header "Firstname","Lastname" 
$X = $CSV | select -Skip 1 
$y = $x -replace '-','-,' # this adds a comma so that the values are in different columns
$y | Export-Csv -NoTypeInformation 'C:\Users\Public\Desktop\Devices.csv'
$import = Import-Csv 'C:\Users\Public\Desktop\Devices.csv' -Header  "Firstname","Lastname"

Collectives™ on Stack Overflow

Splitting values into two columns powershell csv file

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related