Solved

Output to Excel defined column and rows

Posted on 2012-04-11
19
575 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
  • 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now