Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

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

0
techdrive
Asked:
techdrive
  • 10
  • 9
1 Solution
 
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.
0
 
techdriveAuthor Commented:
How do you do this aikimark?
0
 
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
0
 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

 
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
0
 
aikimarkCommented:
I don't think that write-output is used for this function.

What version of PowerShell are you using?
0
 
techdriveAuthor Commented:
2.0
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
0
 
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

0
 
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:
0
 
aikimarkCommented:
Ok.  Let's try piping the $outputs array to the command

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

Open in new window

0
 
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"""
0
 
aikimarkCommented:
What does your Export-Csv line look like?

We are closer than we were.
0
 
techdriveAuthor Commented:
$outputs  | Export-Csv -Delimiter "`t" -notypeinformation -path c:\temp\lister.csv
0
 
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

0
 
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
0
 
aikimarkCommented:
you just posted two very different data layouts.  What are you seeing in the output file?
0
 
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.
0
 
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.
0
 
aikimarkCommented:
perhaps /1MB should be /1000000
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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