Solved

Formatting data into excel with powershell

Posted on 2010-09-19
10
678 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
Set OWA language and time zone in Exchange for individuals, all users or per database.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

23 Experts available now in Live!

Get 1:1 Help Now