CODEWITHSUNDEEP
excelpowershell
Deej
Deej

Reputation: 47

Powershell exporting variables to excel

Ok, so I've been given the task of learning powershell at work to write a few scripts. I have some experience with other languages (just a little) like Java, Python, HTML, and CSS. Powershell is turning out to be a totally different beast, and so I'm not exactly sure how to go about doing things sometimes. Right now I'm trying to figure out how to get my computer name, domain, username and datetime into an excel doc. Here's what I'm trying:

$compname = $env:COMPUTERNAME | Select-Object @{Name='Computer Name'; Expression={$_.Computer_Name}}
$domain = $env:USERDNSDOMAIN | Select-Object @{Name='Domain'; Expression={$_.Domain}}
$user = $env:USERNAME | Select-Object @{Name='Username'; Expression={$_.Username}}
$date = Get-Date -format F | Select-Object @{Name='Date'; Expression={$_}}
$file = "$env:USERPROFILE\Desktop\test4.csv" 
$row = @($Date, $compname, $domain, $user)
$row | Export-Csv $file -NoType

I put my variables into an array hoping it would help, but no luck. Right now only the date is printing. The other variables are all empty and I have no idea why. I can get them to work using Write-Out, but not exactly the way I want, and I'm assuming that Export-CSV is my best option. Am I wrong in that assumption?

Upvotes: 0

Views: 6715

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174920

When you pipe an array or a collection of objects (@(1,2,3) is 3-element array for example), Export-Csv treats each object as a separate row.

If you want all the values in different columns on the same row, you'll need to create an object with the properties you want in each column.

You can create a custom object with the New-Object cmdlet and its -Property parameter. Assign the properties to a hashtable and use that as the Property argument, like so:

$Props = @{
    "Date" = Get-Date -Format f
    "Computer Name" = $env:COMPUTERNAME
    "Username" = $env:USERNAME
    "Domain" = $env:USERDNSDOMAIN
}

$MyObject = New-Object -TypeName psobject -Property $Props

Now, you can pipe the object to Export-Csv:

$FilePath = "$env:USERPROFILE\Desktop\test4.csv" 

$MyObject | Export-Csv $FilePath -NoTypeInformation

If you want to change the order of the columns (or remove or add new ones on the fly), you can use Select-Object:

$MyObject | Select-Object 'Date','Domain','Username','Computer Name' | Export-Csv $FilePath -NoTypeInformation

Upvotes: 2

Related Questions