Solved

Formatting data into excel with powershell

Posted on 2010-09-19
10
690 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
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: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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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