Output to Excel defined column and rows

I have this script which outputs to a file. What I have been trying to do is have the columns and rows defined where this goes into an excel spreadsheet. I realize you can do the output to a csv with a redirection > c:\temp\file.csv but that is too messy. I have seen some examples of where the rows and columns are defined. I am also trying to get a grasp on this so if anyone also have any resources that would be helpful also, thanks

Get-Content C:\list.txt | 
    Foreach {
        
        $mbx = Get-Mailbox -id $_
        $mbxstats = Get-Mailboxstatistics -id $_ | select DisplayName,@{l=’MailboxSize(MB)’;e={$_.TotalItemSize/1MB}},ItemCount
        $fldstats = Get-Mailboxfolderstatistics -id $_ | select name,foldersize,itemsinfolder
        $date = Get-Date |  select datetime
        
        $out = New-Object -Type PSObject
        $out | add-member -type noteProperty -name Alias -value $mbx.Alias
        $out | add-member -type noteProperty -name DisplayName -value $mbxstats.DisplayName
        $out | add-member -type noteProperty -name ServerName -value $mbx.ServerName
        $out | add-member -type noteProperty -name ProhibitSendReceiveQuota -value $mbx.ProhibitSendReceiveQuota
        $out | add-member -type noteProperty -name UseDatabaseQuotaDefaults -value $mbx.UseDatabaseQuotaDefaults
        $out | add-member -type noteProperty -name IssueWarningQuota -value $mbx.IssueWarningQuota       
        $out | add-member -type noteProperty -name ’MailboxSize(MB)’ -value $mbxstats.’MailboxSize(MB)’
        $out | add-member -type noteProperty -name ItemCount -value $mbxstats.ItemCount
        
        Write-Output $out | Out-File C:\Results.txt -append
        Write-Output $fldstats | Out-File C:\Results.txt -append
}

Open in new window

techdriveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
If you output the file as tab-delimited instead of comma-delimited, and give it an XLS file extension, Excel will treat it like a workbook.
techdriveAuthor Commented:
How do you do this aikimark?
aikimarkCommented:
In your case, I would expect it to look something like this:
Export-Csv -Delimiter "`t" -InputObject $out

Open in new window

===========
it is also possible to automate Excel.  Here are a couple of nice TechNet articles on this technique
http://blogs.technet.com/b/heyscriptingguy/archive/2006/09/08/how-can-i-use-windows-powershell-to-automate-microsoft-excel.aspx

http://technet.microsoft.com/en-us/library/dd347724.aspx
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

techdriveAuthor Commented:
This is not working. I tried the last part with the -inputobject and it failed.

Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null
.
At C:\mail3123012.ps1:26 char:111
+   Write-Output $fldstats | Out-File C:\temp\Finalresults.txt -append | export
-csv -Delimiter "`t" -inputobject <<<<  $out
    + CategoryInfo          : InvalidData: (:) [Export-Csv], ParameterBindingV
   alidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,M
   icrosoft.PowerShell.Commands.ExportCsvCommand

 I did change my script around a little bit and the ending is like this. I cannot use append so I am only getting one portion of the data.

     Here is what I came up with but its still not working.

 Write-Output $props, $date | export-csv -notypeinformation -Delimiter "`t" -path c:\finalresults.xls
  Write-Output $fldstats | export-csv -notypeinformation -Delimiter "`t" -path c:\finalresults.xls
aikimarkCommented:
I don't think that write-output is used for this function.

What version of PowerShell are you using?
techdriveAuthor Commented:
2.0
techdriveAuthor Commented:
FYI: I saw this site also but it still does not help with how to map the fields to a spreadsheet http://blogs.technet.com/b/heyscriptingguy/archive/2006/09/08/how-can-i-use-windows-powershell-to-automate-microsoft-excel.aspx
aikimarkCommented:
Please test this.

$outputs = @()
Get-Content C:\list.txt | 
    Foreach {
        
        $mbx = Get-Mailbox -id $_
        $mbxstats = Get-Mailboxstatistics -id $_ | select DisplayName,@{l=’MailboxSize(MB)’;e={$_.TotalItemSize/1MB}},ItemCount
        $fldstats = Get-Mailboxfolderstatistics -id $_ | select name,foldersize,itemsinfolder
        $date = Get-Date |  select datetime
        
        $out = New-Object -Type PSObject -Property @{
        	Alias = $mbx.Alias
        	DisplayName = $mbxstats.DisplayName
        	ServerName = $mbx.ServerName
		ProhibitSendReceiveQuota = $mbx.ProhibitSendReceiveQuota
		UseDatabaseQuotaDefaults = $mbx.UseDatabaseQuotaDefaults
		IssueWarningQuota = $mbx.IssueWarningQuota       
		’MailboxSize(MB)’ = $mbxstats.’MailboxSize(MB)’
		ItemCount = $mbxstats.ItemCount
        	}
	$outputs += $out
}
Export-Csv -Delimiter "`t" -InputObject $outputs

Open in new window

techdriveAuthor Commented:
It is prompting me below. Should this be prompting me for information
cmdlet Export-Csv at command pipeline position 1
Supply values for the following parameters:
aikimarkCommented:
Ok.  Let's try piping the $outputs array to the command

$outputs | Export-Csv -Delimiter "`t"

Open in new window

techdriveAuthor Commented:
I ran this in a test lab and the data is not correct. I had to do some tweaking of course and added the -notypeinformation as a parameter of this command followed by the -path paramter. All still failed.



"ServerName      ""ProhibitSendReceiveQuota""      ""UseDatabaseQuotaDefaults""      ""DisplayName""      ""Alias""      ""IssueWarningQuota""      ""MailboxSize(MB)""      ""ItemCount"""
"corp-mb-01      ""unlimited""      ""True""      ""Bill Buckner""      ""bbuckner""      ""95MB""      ""20B""      ""12"""
"corp-mb-01      ""unlimited""      ""True""      ""Chitown36""      ""Chitown36""      ""95MB""      ""10B""      ""4"""
"corp-mb-01      ""unlimited""      ""True""      ""Chitown9""      ""Chitown9""      ""95MB""      ""0B""      ""0"""
aikimarkCommented:
What does your Export-Csv line look like?

We are closer than we were.
techdriveAuthor Commented:
$outputs  | Export-Csv -Delimiter "`t" -notypeinformation -path c:\temp\lister.csv
aikimarkCommented:
Try a different file extension.  Otherwise, I don't see anything wrong.

$outputs  | Export-Csv -Delimiter "`t" -notypeinformation -path c:\temp\lister.xls

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
techdriveAuthor Commented:
well the thing I have encountered is not the data not going into the file but the way it's displaying. If you reference the script that I posted you get something like this below. In what I have been trying its all over.



Displayname  Doe, John
alias               John Doe
username       john.doe
database        corp-mb-01 sg23db23

Name                                                     Foldersize            ITEMSinfolder
top of information store                          0                                 0
inbox                                                       12452                   2
sent items                                                 293                     1
deleted items                                            23452                    4
aikimarkCommented:
you just posted two very different data layouts.  What are you seeing in the output file?
techdriveAuthor Commented:
No this is the output I am looking for.  This is what the script above displays that I originally posted. I think I might change the way I am doing this since this seems to be difficult here. I was wondering if I could display one set of information and use a variable to call a script within a script and pass it to that script. Here is what I am speaking of. Lets just say I have a list of users and I pass them through the script. Can I set up an array or variable to pass call this through another script.
techdriveAuthor Commented:
hey there I am going to go with this. Thanks for the idea of tab deliminted  output file. I am going to go ahead and go with the following solution below.


        Write-Output $out, $date | Out-File C:\Results.csv -append
        Write-Output $fldstats | Out-File C:\Results.csv -append
      $string = Get-Content c:\results.csv |out-string
      $string.Replace(",","`t") |out-file c:\results13.csv


Just one last question though. Why is the size not converting on the total size of my mailbox size  @{l=’MailboxSize(MB)’;e={$_.TotalItemSize/1MB}} this is the code I have with the select statement.
aikimarkCommented:
perhaps /1MB should be /1000000
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.