Solved

Formatting data into excel with powershell

Posted on 2010-09-19
10
693 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
[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
  • 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 71

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
Independent Software Vendors: 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!

 

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
 
LVL 71

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 71

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

What, When and Where - Security Threats from Q1

Join Corey Nachreiner, CTO, and Marc Laliberte, Information Security Threat Analyst, on July 26th as they explore their key findings from the first quarter of 2017.

Question has a verified solution.

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

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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