locked
How to retrieve the partilcuar columns from csv file using powershell script RRS feed

  • Question

  • I'm trying to retreive the server name and volume from csv file and  csv file it consists of following information. For eg if there are 3 servers.. I want to select each server and  its volume.

    Let us say the file name is strcsvfile

     $strCSVFile = Join-Path -Path (Get-Item -Path $MyInvocation.MyCommand.Path).Directory.FullName -ChildPath ("Ex_Disk_Report_TLog_Under_" + $intPercentFree + ".csv")
    $table | Export-Csv -path $strCSVFile

    ServerName Volume Size Used Free PercentFree
    MBX1 T:\DB01-DB20\ 149.998 92.692 57.306 38.205
    MBX2 T:\DB21-DB40\ 149.998 104.457 45.541 30.361
    MBX3 T:\DB01-DB20\ 149.998 92.665 57.333 38.222

    Here I'm trying to get which subfolder is consuming more space on each server. Could anybody help me out?

    Tuesday, September 17, 2013 11:19 AM

Answers


  • There's no place like 127.0.

    Hi Fred,

    Thank you for your reply. I'm  confused.. Can you please help me with an example? output of $ table varies from time to time that means server names and volumes will get changed. For eg : there are 5 servers and I need to clean up the data on 5 server by adding cleanup script to this script. For your information sub folder names are same on all servers.

    Hello Reddy,

    I sure could, but you see: If I'll be doing your job for you, I might as well be earning your pay. This isn't really a free scripting service here, but a forum where professionals pass on info amongst each other.

    Do yourself a favor: Learn PowerShell. You'll spend a lot less time that way in the long run and improve your own employability.

    Good luck to you on your path to mastering PowerShell - I too am still walking it and far from my own target.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by Bill_Stewart Monday, December 30, 2013 8:19 PM
    Tuesday, September 17, 2013 3:53 PM

All replies

  • Hello Reddy,

    if you're trying to read a csv-file formated as this one, Import-Csv -Path $strCSVFile -Delimiter " " should work.

    If that's the output of $table, then you already have the data in PowerShell, and Write-Host $table[0].ServerName should write "MBX1".

    You can iterate over each entry using something like foreach ($server in $list){Write-Host $server.ServerName}

    I'm sure there are more formitable ways to work with it, but that's a basic method that allows you access to all your data.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, September 17, 2013 12:10 PM
  • Hi,

    You can pass your $table object to Sort-Object and have it sort on the Used property and then use Select-Object to get the first entry.

    http://technet.microsoft.com/en-us/library/ee176968.aspx

    http://technet.microsoft.com/en-us/library/ee176955.aspx

    $table | Sort-Object Used -Descending | Select-Object -First 1


    Don't retire TechNet! - (Maybe there's still a chance for hope, over 11,760+ strong and growing)

    Tuesday, September 17, 2013 12:43 PM
  • Hello Reddy,

    if you're trying to read a csv-file formated as this one, Import-Csv -Path $strCSVFile -Delimiter " " should work.

    If that's the output of $table, then you already have the data in PowerShell, and Write-Host $table[0].ServerName should write "MBX1".

    You can iterate over each entry using something like foreach ($server in $list){Write-Host $server.ServerName}

    I'm sure there are more formitable ways to work with it, but that's a basic method that allows you access to all your data.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Hi Fred,

    Yes that's the output of $table. Here I want to retrieve server name and volume to check which subfolder is consuming more space.. How can i check subfolders size on each server?

    Tuesday, September 17, 2013 12:57 PM
  • Hi,

    You can pass your $table object to Sort-Object and have it sort on the Used property and then use Select-Object to get the first entry.

    http://technet.microsoft.com/en-us/library/ee176968.aspx

    http://technet.microsoft.com/en-us/library/ee176955.aspx

    $table | Sort-Object Used -Descending | Select-Object -First 1


    Don't retire TechNet! - (Maybe there's still a chance for hope, over 11,760+ strong and growing)

    Hi Mike,

    I  think it gives First entry information that means server name But How can i check which subfolder consuming more space on Each and every server.


    Tuesday, September 17, 2013 1:10 PM
  • Hello Reddy,

    if you want to retrieve the numbers in the table, $table[0].Used returns the number for used space on MBX1. If you want to check the Subfolders of T:\DB01-DB20\ and their size ...

    foreach ($server in $list)
    {
        $items = Get-Childitem $server.Volume -Directory
        foreach ($item in $items)
        {
            $status = (Get-ChildItem $item -recurse | Measure-Object -property length -sum)
            $size = (status.sum / 1MB)
            Write-Host ($item.Fullname + ": " + $size + " MB")
        }
    }

    That ought to output all the subfolders' Sizes.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, September 17, 2013 1:33 PM
  • Hi Mike,

    I  think it gives First entry information that means server name But How can i check which subfolder consuming more space on Each and every server.


    You're right, that will select the header, so you'll just need to adjust it slightly.

    I'm confused, though, are you looking to parse this CSV file or are you asking a completely different question?


    Don't retire TechNet! - (Maybe there's still a chance for hope, over 11,760+ strong and growing)

    Tuesday, September 17, 2013 1:35 PM
  • Hi Mike,

    I  think it gives First entry information that means server name But How can i check which subfolder consuming more space on Each and every server.


    You're right, that will select the header, so you'll just need to adjust it slightly.

    I'm confused, though, are you looking to parse this CSV file or are you asking a completely different question?


    Don't retire TechNet! - (Maybe there's still a chance for hope, over 11,760+ strong and growing)

    Hi Mike,

    Sorry for the confusion, I dont want to confuse any one. Here, the output of $table is

    ServerName   Volume            Size      Used      Free   PercentFree
    MBX1         T:\DB01-DB20\  149.998  92.692  57.306 38.205
    MBX2         T:\DB21-DB40\ 149.998 104.457 45.541 30.361
    MBX3         T:\DB01-DB20\ 149.998 92.665 57.333 38.222

    My requirement is

    For eg : when Server MBX1 utilizes more space on volume T:\DB1-DB20. I want to check which subfloder consumes more space on \\MBX1\T:\DB1-DB20

    Please note down DB01-DB20 folder it consists of sub foldes like DB1, DB2 till DB 20. Once it finds the subfolder I will add another script here to cleanup the data.


    I want to repeat this process for each and every server and its respective volume.


    Tuesday, September 17, 2013 1:57 PM
  • Hello Reddy,

    I'll just add one contribution then for the time being. One of my snippets. Just add it at the head of your script and you can call the function whenever you need:

    function Get-FolderSize
    {
    	<#
    		.SYNOPSIS
    			Returns size of folder
    	
    		.DESCRIPTION
    			Returns the size of the folder parameter. The sum of all childitems (recursive) is returned
    	
    		.PARAMETER path
    			The path of the folder to be thus scanned
    	
    		.INPUTS
    			System.String
    	
    		.OUTPUTS
    			System.Int32
    	#>
    	Param(
    		[String]
    		$path
    	)
    	
    	# If path is valid, do it
    	if (Test-Path $path)
    	{
    		$folderStats = Get-ChildItem $path -Recurse | Measure-Object -property length -sum
    		$size = $folderStats.sum
    		return $size
    	}
    	
    	# Throw error if path not valid
    	Else
    	{
    		Write-Error "Illegal or empty Path parameter"
    	}
    }

    So all you need is to go through your list of servers, build the paths, get the subfolders and use the function on them :)

    I'm sure you'll manage.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, September 17, 2013 2:10 PM
  • This is easier:

    $fso=New-Object -ComObject Scripting.FileSystemObject
    $folder=$fso.GetFolder('c:\')
    $folder.SubFolders|select name,size



    ¯\_(ツ)_/¯



    • Edited by jrv Tuesday, September 17, 2013 2:57 PM
    Tuesday, September 17, 2013 2:56 PM
  • This is easier:

    $fso=New-Object -ComObject Scripting.FileSystemObject
    $folder=$fso.GetFolder('c:\')
    $folder.SubFolders|select name,size



    ¯\_(ツ)_/¯



    Hi Jrv,

    Can you please elaborate more ? what is the purpose of ComObject Scripting.FileSystemObject?

    Tuesday, September 17, 2013 3:04 PM
  • Hello Reddy,

    I'll just add one contribution then for the time being. One of my snippets. Just add it at the head of your script and you can call the function whenever you need:

    function Get-FolderSize
    {
    	<#
    		.SYNOPSIS
    			Returns size of folder
    	
    		.DESCRIPTION
    			Returns the size of the folder parameter. The sum of all childitems (recursive) is returned
    	
    		.PARAMETER path
    			The path of the folder to be thus scanned
    	
    		.INPUTS
    			System.String
    	
    		.OUTPUTS
    			System.Int32
    	#>
    	Param(
    		[String]
    		$path
    	)
    	
    	# If path is valid, do it
    	if (Test-Path $path)
    	{
    		$folderStats = Get-ChildItem $path -Recurse | Measure-Object -property length -sum
    		$size = $folderStats.sum
    		return $size
    	}
    	
    	# Throw error if path not valid
    	Else
    	{
    		Write-Error "Illegal or empty Path parameter"
    	}
    }

    So all you need is to go through your list of servers, build the paths, get the subfolders and use the function on them :)

    I'm sure you'll manage.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Hi Fred,

    Thank you for your reply. I'm  confused.. Can you please help me with an example? output of $ table varies from time to time that means server names and volumes will get changed. For eg : there are 5 servers and I need to clean up the data on 5 server by adding cleanup script to this script. For your information sub folder names are same on all servers.

    Tuesday, September 17, 2013 3:12 PM
  • This is easier:

    $fso=New-Object -ComObject Scripting.FileSystemObject
    $folder=$fso.GetFolder('c:\')
    $folder.SubFolders|select name,size



    ¯\_(ツ)_/¯



    Hi Jrv,

    Can you please elaborate more ? what is the purpose of ComObject Scripting.FileSystemObject?

    Help New-Object -par ComObject
    Help New-Object -Examples


    ¯\_(ツ)_/¯

    Tuesday, September 17, 2013 3:15 PM
  • Hi Fred,

    Thank you for your reply. I'm  confused.. Can you please help me with an example? output of $ table varies from time to time that means server names and volumes will get changed. For eg : there are 5 servers and I need to clean up the data on 5 server by adding cleanup script to this script. For your information sub folder names are same on all servers.


    I think you need to learn to write your own scripts.  You are now adding to the original question.  You really need to start a new topic for a new question.  Please review the forum guidelines.

    ¯\_(ツ)_/¯

    Tuesday, September 17, 2013 3:18 PM
  • I think you need to learn to write your own scripts.  You are now adding to the original question.  You really need to start a new topic for a new question.  Please review the forum guidelines.


    ¯\_(ツ)_/¯

    Yeah, that's the spirit!  Maybe, to learn to write scripts, he should go post questions on a forum where people offer help with that sort of thing... oh, right.

    Seriously, man.  If you're not going to contribute something helpful, just shut up.

    Tuesday, September 17, 2013 3:30 PM

  • There's no place like 127.0.

    Hi Fred,

    Thank you for your reply. I'm  confused.. Can you please help me with an example? output of $ table varies from time to time that means server names and volumes will get changed. For eg : there are 5 servers and I need to clean up the data on 5 server by adding cleanup script to this script. For your information sub folder names are same on all servers.

    Hello Reddy,

    I sure could, but you see: If I'll be doing your job for you, I might as well be earning your pay. This isn't really a free scripting service here, but a forum where professionals pass on info amongst each other.

    Do yourself a favor: Learn PowerShell. You'll spend a lot less time that way in the long run and improve your own employability.

    Good luck to you on your path to mastering PowerShell - I too am still walking it and far from my own target.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by Bill_Stewart Monday, December 30, 2013 8:19 PM
    Tuesday, September 17, 2013 3:53 PM
  • Hi Reddy,

    I don't know for certain, based on what you've posted, but your $table variable is probably not really a table.  Even though the data is formatted as a table when PowerShell displays it at the console, the $table variable is probably an array of objects.  People have posted examples in this thread already of how you can access the contents of that array in a loop (which will work even if number of objects have changed; more rows in the table, so to speak.)  Here are a couple of examples of how you could start with your $table array and identify objects with less than 20 percent free space, at which point you can start looking into code for how to calculate subfolder sizes (see other examples in this thread, and the link I've copied below):

    foreach ($object in $table)
    {
        if ($object.PercentFree -lt 20)
        {
            # Add code here to calculate subfolder sizes for this $object.
        }
    }
    
    $table |
    Where-Object { $_.PercentFree -lt 20 } |
    ForEach-Object {
        $object = $_
        # Add code here to calculate subfolder sizes for this $object.
    }
    


    You can learn more about PowerShell's various options for looping over collections in the about_For, about_Foreach, about_While, and about_Do, as well as in the ForEach-Object cmdlet help.

    As for the rest of your question (how to figure out the size of subfolders), the tricky part here is that you're trying to do this remotely.  To do that, you have two options:  1) Cause a script to execute locally on the target machines, then pull back its results (using Invoke-Command, if you've set up PowerShell Remoting, for example), or 2) Calculate the folder sizes over a share (if the volumes are shared).  The second option may not perform very well, depending on the number of files / folders and your network speed.

    Here's another thread from this forum on the same topic, that I found with a quick web search: http://social.technet.microsoft.com/Forums/scriptcenter/en-US/d13e0d87-c428-4fcc-9d87-51101a968e5e/getting-folder-size-from-remote-computers-using-vbs-or-wmi

    Tuesday, September 17, 2013 4:42 PM
  • Reddy - Don't get discouraged.  You clearly need to get the very basics of PowerShell before pursuing more complex methods.  Go through the learning resources  on this site and review scripts in the repository.

    We will not write your script for you and teaching you incrementally is outside of the scope of this forum.

    Once you have a handle on the basics you will find that you will be able to more readily understand the answers you are getting.


    ¯\_(ツ)_/¯

    Tuesday, September 17, 2013 5:34 PM