export powershell to excel document

Welcome Forums General PowerShell Q&A export powershell to excel document

This topic contains 4 replies, has 4 voices, and was last updated by

 
Participant
10 months, 1 week ago.

  • Author
    Posts
  • #92952

    Participant
    Points: 4
    Rank: Member

    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
    
  • #92956

    Participant
    Points: 22
    Rank: Member

    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.

  • #92958

    Participant
    Points: 1
    Rank: Member

    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

  • #92983

    Participant
    Points: 31
    Rank: Member

    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.

  • #93255

    Participant
    Points: 4
    Rank: Member

    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
    
    

The topic ‘export powershell to excel document’ is closed to new replies.