Solved

Output to Excel defined column and rows

Posted on 2012-04-11
19
586 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 45

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 45

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 45

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 45

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 45

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 45

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 45

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 45

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 45

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 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