Solved

Formatting data into excel with powershell

Posted on 2010-09-19
10
685 Views
Last Modified: 2012-05-10
Hello guys I am  a newbie here that is loving powershell and thankful for a site like this.
I have this script here and here is what I am trying to accomplish. The script below works great with the output below.I would like to sharpen it up a bit for readibility purposes. Please read my comments as they are interwoven.


This is the script below

$TotalCount = @{}
Get-ChildItem -Recurse \\servername\share | foreach {
    $objOwner = (Get-ACL $_.FullName).Owner
    $Totalcount[$objOwner] += $_.Length
}
$TotalCount | Format-wide



Here is the output is below

user      size

user1      1254741
user2      145221
user3      95412477
user4      554412
user5      2144110
user6      554712
user7      11441

I am now trying to get the output to save in an excel file instead.


$TotalCount = @{}
Get-ChildItem -Recurse \\servername\share | foreach {
    $objOwner = (Get-ACL $_.FullName).Owner
    $Totalcount[$objOwner] += $_.Length
}
$TotalCount | Format-wide | export-csv c:\users\svc_xch\log.xls


My goal is to get the output to work like this in excel. I would like to have the GB for gigabytes and MB for megabytes. Any clues please.

USER   SIZE
userA  125GB
USERB  140MB
USERC  320GB
USERD  73MB
0
Comment
Question by:tdodd72
  • 6
  • 3
10 Comments
 
LVL 1

Expert Comment

by:Brent387
ID: 33716963
I've been trying your script on my computer and it's not working. What is the data that youre trying to pull from the server?
0
 

Author Comment

by:tdodd72
ID: 33718030
With this script here I am getting the file owner and the user name. I did modify the \\servername\share portion with that of the share or drive that I am trying to pull.


$TotalCount = @{}
Get-ChildItem -Recurse \\servername\share | foreach {
    $objOwner = (Get-ACL $_.FullName).Owner
    $Totalcount[$objOwner] += $_.Length
}
$TotalCount
0
 
LVL 70

Accepted Solution

by:
Chris Dent earned 250 total points
ID: 33723452

You're mixing output formatters, that's rarely a good idea.

It should go something roughly like this.

Note that your output is CSV format you shouldn't really label the file .xls, I doubt Excel will complain, but the formats are very different.

Chris
$Path = "\\servername\share"

$Output = @{}
Get-ChildItem $Path -Recurse | ForEach-Object {
  $Owner = (Get-Acl $_.FullName).Owner
  If ($Output.Contains($Owner)) {
    $Output.$Owner += $_.Length
  } Else {
    $Output.Add($Owner, $_.Length)
  }
}

$Output.Keys | Select-Object `
  @{n='Name';e={ $_ }},
  @{n='Size';e={
    If     (($Output.$_ / 1Gb) -ge 1) { "$('{0:D2}' -f ($Output.$_ / 1Gb)) Gb" }
    ElseIf (($Output.$_ / 1Mb) -ge 1) { "$('{0:N2}' -f ($Output.$_ / 1Mb)) Mb" }
    ElseIf (($Output.$_ / 1kb) -ge 1) { "$('{0:N2}' -f ($Output.$_ / 1Kb)) Kb" }
    Else   { "$($Output.$_) b" } }} |
  Export-Csv "out.csv" -NoTypeInformation

Open in new window

0
 

Author Comment

by:tdodd72
ID: 33730909
When running this command against my fileshare where I have full admin access I am getting the following error message.


    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Contains" with "1" argument(s): "Key cannot be null.
Parameter name: key"
At line:3 char:23
+   If ($Output.Contains <<<< ($Owner)) {
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Contains" with "1" argument(s): "Key cannot be null.
Parameter name: key"
At line:3 char:23
+   If ($Output.Contains <<<< ($Owner)) {
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Contains" with "1" argument(s): "Key cannot be null.
Parameter name: key"
At line:3 char:23
+   If ($Output.Contains <<<< ($Owner)) {
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Contains" with "1" argument(s): "Key cannot be null.
0
 

Author Comment

by:tdodd72
ID: 33730928
I am sorry let me clarify. From your last script  you posted. By the way I like the concept of mb, kb, gb.  Nice looking script however please look below as I am getting the following message when running this. Of course  I replaced the $Path variable with my fileshare name.

$Path = "\\servername\share"

$Output = @{}
Get-ChildItem $Path -Recurse | ForEach-Object {
  $Owner = (Get-Acl $_.FullName).Owner
  If ($Output.Contains($Owner)) {
    $Output.$Owner += $_.Length
  } Else {
    $Output.Add($Owner, $_.Length)
  }
}

$Output.Keys | Select-Object `
  @{n='Name';e={ $_ }},
  @{n='Size';e={
    If     (($Output.$_ / 1Gb) -ge 1) { "$('{0:D2}' -f ($Output.$_ / 1Gb)) Gb" }
    ElseIf (($Output.$_ / 1Mb) -ge 1) { "$('{0:N2}' -f ($Output.$_ / 1Mb)) Mb" }
    ElseIf (($Output.$_ / 1kb) -ge 1) { "$('{0:N2}' -f ($Output.$_ / 1Kb)) Kb" }
    Else   { "$($Output.$_) b" } }}

Here is the message I am getting below.

    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Contains" with "1" argument(s): "Key cannot be null.
Parameter name: key"
At line:3 char:23
+   If ($Output.Contains <<<< ($Owner)) {
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Contains" with "1" argument(s): "Key cannot be null.
Parameter name: key"
At line:3 char:23
+   If ($Output.Contains <<<< ($Owner)) {
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Contains" with "1" argument(s): "Key cannot be null.
Parameter name: key"
At line:3 char:23
+   If ($Output.Contains <<<< ($Owner)) {
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Contains" with "1" argument(s): "Key cannot be null.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 70

Expert Comment

by:Chris Dent
ID: 33732270

It's possible it's failed to read the owner. Lets test for and handle that possibility.

Chris
$Path = "\\servername\share"

$Output = @{}
Get-ChildItem $Path -Recurse | ForEach-Object {
  $Owner = (Get-Acl $_.FullName).Owner
  If (!$Owner) { $Owner = "Unknown" }

  If ($Output.Contains($Owner)) {
    $Output.$Owner += $_.Length
  } Else {
    $Output.Add($Owner, $_.Length)
  }
}

$Output.Keys | Select-Object `
  @{n='Name';e={ $_ }},
  @{n='Size';e={
    If     (($Output.$_ / 1Gb) -ge 1) { "$('{0:D2}' -f ($Output.$_ / 1Gb)) Gb" }
    ElseIf (($Output.$_ / 1Mb) -ge 1) { "$('{0:N2}' -f ($Output.$_ / 1Mb)) Mb" }
    ElseIf (($Output.$_ / 1kb) -ge 1) { "$('{0:N2}' -f ($Output.$_ / 1Kb)) Kb" }
    Else   { "$($Output.$_) b" } }} |
  Export-Csv "out.csv" -NoTypeInformation

Open in new window

0
 

Author Comment

by:tdodd72
ID: 33732914
You do have a major point. I did not think about this. So my question is what is the best way to determine if someone owns a file or the file belongs to them if it is sitting in a general folder. The issue I am facing is the these files are sitting in a folder that everyone puts files in. I guess more like some sort of repository.
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33732929

It's extremely difficult to do, personally I don't think you can rely on the Owner field, 90% of the time that'll be Administrators.

I'm afraid I don't have much of a work-around for that. If they're office documents you could try and read the document metadata. It's not as easy, and that may not be accurate either :-\

Every time I've had to deal with something like that I've ended up pushing it back onto the users. I doubt that's very effective either. Rock and hard places spring to mind.

Chris
0
 

Author Comment

by:tdodd72
ID: 33849236
opened a new question
0
 

Author Closing Comment

by:tdodd72
ID: 33849241
.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hi all.   The other day I had to change the passwords for a bunch of users on the fly. Because they were so many, I decided to do it in an automated way and I would like to share it with you all.   If you are not doing it directly in a Domain Co…
This article will help you understand what HashTables are and how to use them in PowerShell.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

948 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

22 Experts available now in Live!

Get 1:1 Help Now