Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Formatting data into excel with powershell

Posted on 2010-09-19
10
Medium Priority
?
701 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 71

Accepted Solution

by:
Chris Dent earned 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
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 anti-spam), the admin…
Screencast - Getting to Know the Pipeline

877 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