• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 668
  • Last Modified:

Powershell: Need to output to .csv

In my code, I am gathering user information from an OU in Active Directory. The information includes the OfficePhone number. The PhoneNum function removes +, -, ., (, ) from the number and joins all the digits into one long number.
When I run the code in the ISE, the output is exactly what I want to see:

FullName,City,State,EmailAddress,OfficePhone

However, when I try to output to csv, the file is created but is either blank, or has a single column of numbers.
I have tried out-file and export-csv. I have also tried to run the code from the command line with >> c:\test\users.csv.
Please help.

$erroractionpreference = "SilentlyContinue"
$NAUsers = Get-ADUser -SearchBase "OU=Fabrikam,DC=microsoft,DC=com" -Filter * -Properties * |Sort-Object Name |Select-Object -Property Name, City, State, EmailAddress, OfficePhone, MobilePhone |Where-object {$_.EmailAddress}
$outfile = "c:\test\ADUsersPhone.csv"

foreach($users in $NAUsers){

$something =(PhoneNum($users.officephone))

$output =@($users.Name, $users.City, $users.State, $users.EmailAddress, $something)

$output |join-string -separator "," 
}

Function PhoneNum($stringer){

#Remove "+"
if($stringer.IndexOf("+") -ge 0){
    $stringer1 = $stringer -replace "\+",""
    }
    else{
    $stringer1 = $stringer
    }
#Remove "."
if($stringer1.IndexOf('.') -ge 0){
  $stringer2 = $stringer1 -replace "\.",""
  }
  else{
  $stringer2 = $stringer1
  }
#Remove "-"
if($stringer2.IndexOf('-') -ge 0){
  $stringer3 = $stringer2 -replace "-",""
  }
  else{
  $stringer3 = $stringer2
  }
#Remove "("
if($stringer3.IndexOf("(") -ge 0){
  $stringer4 = $stringer3 -replace '\(',""
  }
  else{
  $stringer4 = $stringer3
  }
#Remove ")"
if($stringer4.IndexOf(")") -ge 0){
  $stringer5 = $stringer4 -replace '\)',""
  }
  else{
  $stringer5 = $stringer4
  }

$private:rOphone = $stringer5.Split(" ", [StringSplitOptions]::RemoveEmptyEntries)
$uOphone = [string]::Join("", $rOphone)
return $uOphone
}

Open in new window

0
clockwood
Asked:
clockwood
  • 3
1 Solution
 
prashanthdCommented:
Try the following..
$erroractionpreference = "SilentlyContinue"
$NAUsers = Get-ADUser -SearchBase "OU=Fabrikam,DC=microsoft,DC=com" -Filter * -Properties * |Sort-Object Name |Select-Object -Property Name, City, State, EmailAddress, OfficePhone, MobilePhone |Where-object {$_.EmailAddress}
$outfile = "c:\test\ADUsersPhone.csv"

foreach($users in $NAUsers){

$something =(PhoneNum($users.officephone))

#$output =@($users.Name, $users.City, $users.State, $users.EmailAddress, $something)

#$output |join-string -separator "," 
$output="$users.Name,$users.City,$users.State,$users.EmailAddress,$something"
$output
$output | Out-File $outfile -Append
}

Function PhoneNum($stringer){

#Remove "+"
if($stringer.IndexOf("+") -ge 0){
    $stringer1 = $stringer -replace "\+",""
    }
    else{
    $stringer1 = $stringer
    }
#Remove "."
if($stringer1.IndexOf('.') -ge 0){
  $stringer2 = $stringer1 -replace "\.",""
  }
  else{
  $stringer2 = $stringer1
  }
#Remove "-"
if($stringer2.IndexOf('-') -ge 0){
  $stringer3 = $stringer2 -replace "-",""
  }
  else{
  $stringer3 = $stringer2
  }
#Remove "("
if($stringer3.IndexOf("(") -ge 0){
  $stringer4 = $stringer3 -replace '\(',""
  }
  else{
  $stringer4 = $stringer3
  }
#Remove ")"
if($stringer4.IndexOf(")") -ge 0){
  $stringer5 = $stringer4 -replace '\)',""
  }
  else{
  $stringer5 = $stringer4
  }

$private:rOphone = $stringer5.Split(" ", [StringSplitOptions]::RemoveEmptyEntries)
$uOphone = [string]::Join("", $rOphone)
return $uOphone
}

Open in new window

0
 
prashanthdCommented:
Modify Line 12 to following

$output="$($users.Name),$($users.City),$($users.State),$($users.EmailAddress),$something"
0
 
clockwoodAuthor Commented:
prashanthd...

That did it! Thank you very much. If you would be so kind, could you explaing what the "$(" does to the variables?
0
 
prashanthdCommented:
1)Access a variable within ""

$name="Bill"

write-host "My name is $name"

Output - My Name is Bill

2)Access property of an object with ""

$mailbox=Get-Mailbox bill

write-host "My Email address is $($mailbox.smtpaddress)"

output - My email address is bill@abc.com
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now