Solved

Output to Excel defined column and rows

Posted on 2012-04-11
19
588 Views
Last Modified: 2012-05-02
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
Comment
Question by:techdrive
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 37837440
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
 

Author Comment

by:techdrive
ID: 37837579
How do you do this aikimark?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37838130
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:techdrive
ID: 37838351
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
 
LVL 46

Expert Comment

by:aikimark
ID: 37838601
I don't think that write-output is used for this function.

What version of PowerShell are you using?
0
 

Author Comment

by:techdrive
ID: 37838855
2.0
0
 

Author Comment

by:techdrive
ID: 37838865
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
 
LVL 46

Expert Comment

by:aikimark
ID: 37838991
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
 

Author Comment

by:techdrive
ID: 37839126
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
 
LVL 46

Expert Comment

by:aikimark
ID: 37839888
Ok.  Let's try piping the $outputs array to the command

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

Open in new window

0
 

Author Comment

by:techdrive
ID: 37840307
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
 
LVL 46

Expert Comment

by:aikimark
ID: 37840566
What does your Export-Csv line look like?

We are closer than we were.
0
 

Author Comment

by:techdrive
ID: 37840632
$outputs  | Export-Csv -Delimiter "`t" -notypeinformation -path c:\temp\lister.csv
0
 
LVL 46

Accepted Solution

by:
aikimark earned 500 total points
ID: 37840649
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
 

Author Comment

by:techdrive
ID: 37840734
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
 
LVL 46

Expert Comment

by:aikimark
ID: 37840780
you just posted two very different data layouts.  What are you seeing in the output file?
0
 

Author Comment

by:techdrive
ID: 37840830
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
 

Author Comment

by:techdrive
ID: 37840884
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
 
LVL 46

Expert Comment

by:aikimark
ID: 37841127
perhaps /1MB should be /1000000
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question