export powershell to excel document

PowerShell Help
1

I have a script that gives me a list with new created users.

But i want to output all to Excel document.
With also diferent kind of row names like Extensionattribute6 must have a other name in Excel.

is this possible and can some one help me with this?

[system.reflection.assembly]::loadwithpartialname('microsoft.visualbasic') | Out-Null
$Country = [microsoft.visualbasic.interaction]::inputbox('Fill in the Country name in the short OU version','Country')
$givendate = [microsoft.visualbasic.interaction]::inputbox('Fill in the begin date of the month','Given Date')
$Lastdate = [microsoft.visualbasic.interaction]::inputbox('Fill in the last date of the month','Last Date') 
$fileLocation = [microsoft.visualbasic.interaction]::inputbox('give up filelocation were to save the file','Save As','c:\temp\data.csv')

$BeginDate = get-date $givendate
$EndDate = get-date $Lastdate
$OurUsers = Get-ADUser -SearchBase "OU=$country,DC=corp,DC=local" -filter * -Properties *
$OurUsers | Where-Object {$_.whencreated -gt $BeginDate} | 
Select-Object CN, SamAccountName,WhenCreated, Extensionattribute6, Mail, Info, AccountExpirationDate, PasswordNeverExpires, CannotChangePassword, PasswordNotRequired| Sort-Object whencreated -Descending |Format-Table | Export-Csv $fileLocation
2

You should try removing the format-table from your pipeline. It isn’t required when exporting to csv, and is only for displaying objects on screen.

You can rename properties in your select statement. If Extensionattribute6 is to be renamed to attrib6 you replace the property name in the select-object properties list with a hash table like @{“name”=“attrib6”;“expression”={$_.Extensionattribute6}}

Hope that helps.

3

Hi Jeremy,
if you want a different Name you can try something like this

Select-Object CN, SamAccountName,WhenCreated, @{Name="YourCustomName";Expression={$_.Extensionattribute6}}, Mail, Info, AccountExpirationDate, PasswordNeverExpires, CannotChangePassword, PasswordNotRequired| Sort-Object whencreated -Descending | Export-Csv -NoTypeInformation $fileLocation

if you want to output to screen you can use import-csv $FileLocation and read back the csv file to console or first save the output to a variable and then use export-csv

Regards
Shihan

4

Plus with Export-CSV is looking for objects from the pipeline. Adding the Format-Table in there I think will give you one line for each data item, but it will be some data from Format-Table, it won’t be your actual data. Not sure I exactly understand it, but I know by past error this is not the result I want. Learned by trial and error. Good luck.

5

Okay maybey some thing completely different.

I have this script already build out in Powershell but then with a html output,
is it possible to import it to excel?
and built this in the script

script is below:

# This is the style that will be used for the html Report output
$style = "body{font-family: verdana; font-size: 10pt;}"
$style = $style + "table{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "th{border: 1px solid black; background: #0681cc; padding: px5; }"
$style = $style + "td{border: 1px solid black; background: #ffffff; padding: px5; }"
$style = $style + ""
$style = $style + "AD report"

# this is the auto logon for the sender for the email (office 365)
$Username = "mail adres"
$Password = ConvertTo-SecureString "password" -AsPlainText -Force
$Office365creds = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password


[system.reflection.assembly]::loadwithpartialname('microsoft.visualbasic') | Out-Null
$Country = [microsoft.visualbasic.interaction]::inputbox('Fill in the Country name in the short OU version','Country')
$givendate = [microsoft.visualbasic.interaction]::inputbox('Fill in the begin date of the month','Given Date')
$Lastdate = [microsoft.visualbasic.interaction]::inputbox('Fill in the last date of the month','Last Date') 
$fileLocation = [microsoft.visualbasic.interaction]::inputbox('give up filelocation were to save the file','Save As','c:\temp\defaultfilelocation.html')

$BeginDate = get-date $givendate
$EndDate = get-date $Lastdate
$OurUsers = Get-ADUser -SearchBase "OU=$country,DC=corp,DC=local" -filter * -Properties *
$OurUsers | Where-Object {$_.whencreated -gt $BeginDate} | 
Select-Object CN, SamAccountName,WhenCreated, Extensionattribute6, Mail, Info, AccountExpirationDate, PasswordNeverExpires, CannotChangePassword, PasswordNotRequired| Sort-Object whencreated -Descending |
convertto-HTML -as Table -PreContent "Generated $(get-date)New Created Users" -Head $style | 
Out-File $filelocation | out-string

$Sender = "jeremy.smit@campofriofg.com"
$SMTP = "outlook.office365.com"
$Recipients = @("Jeremy Smit ", "Frans Pospiech ")
$Subject = "Report new created AD User"
$Body = "Hereby the new created AD Users Report"
$Attachment = new-object Net.Mail.Attachment($fileLocation)

Start-Sleep 2

Send-MailMessage -To $Recipients -From $Sender -Subject $Subject -Body $Body -Attachments $fileLocation -SmtpServer $SMTP -Credential $Office365creds -UseSsl -Port 587 -DeliveryNotificationOption never