Trouble exporting CSV with powershell

vishalkrishnaswamy · 2018-06-29T14:30:24.000Z

I’m trying to import a csv with two columns and several entries. I then have to change the layout of the csv by adding additional columns. However, I’m running into issues when casting the object as a string and trying to enter it back into the csv. My code is as following:

Connect-MsolService
Get-MsolUser -all | Select-Object UserPrincipalName, DisplayName | Export-Csv -Path “path” –NoTypeInformation

$header = “Name”,“Employee ID”,“Title”,“Phone”,“Email”,“Site”,“Location”,“Department”,“Notes”

$c = Import-Csv -Path “path” | ConvertFrom-Csv -Header $header

foreach ($row in $c){
$temp = “$row”.split(“=”,“;”)
$row.Name = $temp

$temp=“$row”.split(“DisplayName=”,“}”
$row.Email = “$temp”
}
$c | export-csv -Path “C:\Users\vkrishnaswamy\Documents\scripts\AssetTigerUpdate\data.csv” -NoTypeInformation

Is there an easier way to either modify the csv or to complete this script?

Thanks,

Vishal

Neally · 2018-06-29T14:34:20.000Z

Welcome!

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

Neally · 2018-06-29T14:38:49.000Z

Can you show is what you want the CSV to look like in the end?

You just want to append the columns?

vishalkrishnaswamy · 2018-06-29T15:22:10.000Z

Thanks! Will do next time!

Neally · 2018-06-29T15:25:27.000Z

if you just want to add more columns you can do this:

$header = "Name","Employee ID","Title","Phone","Email","Site","Location","Department","Notes"
$data   = import-csv "$env:USERPROFILE\desktop\in.csv"

foreach( $headerinfo in $header){
    $data | Add-Member NoteProperty $headerinfo 'null'
}

$data | ft *

2018-06-29_10-25-13.png800×76 25.5 KB

vishalkrishnaswamy · 2018-06-29T15:26:27.000Z

I figured out how to format it correctly by using calculated properties!

vishalkrishnaswamy · 2018-06-29T15:27:36.000Z

The problem was that the way I was pulling the data for the csv initially grouped all of the properties under one header.

Neally · 2018-06-29T15:28:41.000Z

VishaI:

I figured out how to format it correctly by using calculated properties!

oh ok so you just used select-object

$data | 
select displayname,
@{n='something';e={'somevalue'}}

Neally · 2018-06-29T15:29:56.000Z

VishaI:

The problem was that the way I was pulling the data for the csv initially grouped all of the properties under one header.

You did not mention that, giving an example of what the output looks like helps :¬)

Glad you got it figured out.

jitensh · 2018-06-29T17:42:45.000Z

Looks like you ran

import-csv "$env:USERPROFILE\desktop\in.csv"|select userprincipalname,displayname|export-csv $env:USERPROFILE\desktop\nest.csv -notype