none
Export data to excel with powershell RRS feed

  • Question

  • Code i'm using: 

    $Filter = @{Expression={$_.Name};Label="DiskName"}, `
              @{Expression={$_.Label};Label="Label"}, `
              @{Expression={$_.FileSystem};Label="FileSystem"}, `
              @{Expression={[int]$($_.BlockSize/1KB)};Label="BlockSizeKB"}, `
              @{Expression={[int]$($_.Size / 1gb)};Label="CapacityGB"}, `
              @{Expression={[int]$($_.Freespace/1GB)};Label="FreeSpaceGB"}
    Get-WmiObject –Class Win32_LogicalDisk –ComputerName 127.1.1.1 | Format-Table $Filter –AutoSize

    Result i get: 

    My question:

    What command(s) Can i use to put this infromation (Amount of Space free) in the right place at Excel? Lets say B5-B20 needs to be the amount of free space of Disk C:, and D5-D20 needs to be the mount of free space on disk D? etc?.. Is this Possible? 

    ----- 

    I want to use this on 5-10 servers. - This script will be done every week

    THanks all.

    Regards,

    Kece

    



    • Edited by Kece Monday, May 12, 2014 12:01 PM
    Monday, May 12, 2014 11:58 AM

Answers

  • I think this will give you what it seems like you're asking for, but I'm not sure it's what you want. 

    $OutputFile = "c:\temp\DiskInventory.xlsx"
    $serverlist = "server1","server2"
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Add()
    1..2 | ForEach {$workbook.worksheets.item(2).Delete()}
    $InventorySheet = $workbook.Worksheets.Item(1)
    $InventorySheet.Name = 'Server Drive Inventory'
    $InventorySheet.Activate() | Out-Null
    $row = 5
    $InventorySheet.Cells.Item(1,1) = "Server" Foreach ($server in $serverlist) { $column = 1 $InventorySheet.Cells.Item($row,$column) = $server Try { Foreach ($Drive in (Get-WmiObject –Class Win32_LogicalDisk –ComputerName $server)) { $column ++ $InventorySheet.Cells.Item($row,$column) = $Drive.FreeSpace/1GB If ($InventorySheet.Cells.Item(1,$column) -notmatch "FreeSpace") { $InventorySheet.Cells.Item(1,$column) = "$($Drive.Name) FreeSpaceGB" } } } Catch { $InventorySheet.Cells.Item($row,2) = "Inaccessible" } $row ++ } $workbook.SaveAs($OutputFile) $excel.Quit() [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null


    I hope this post has helped!


    • Edited by Rhys W Edwards Monday, May 12, 2014 3:02 PM
    • Marked as answer by Kece Tuesday, May 13, 2014 11:48 AM
    Monday, May 12, 2014 3:00 PM
  • Yes, it's easy enough to get the additional fields but you didn't ask for that and weren't clear on how you wanted them arranged so I didn't include them.

    The last error is because you didn't copy the code correctly or modified it in some way - Out-Null is the cmdlet I posted, Out-N means something got cut off somewhere.  

    I'd have to guess that something else might have been cutoff or copied incorrectly because I ran the code myself before posting it and it worked.  Excel opens with three worksheets by default and the part of the code where it errored is used to remove the extra worksheets.  You say it creates the csv, so maybe you changed the name of the output file to .csv instead of xlsx.  CSV do not have worksheets, so perhaps that's why the first two errors.  Just remove that 1..2 command if that's the way you want to go.

    To add the capacity modify the main foreach loop for the drives like this:

          Foreach ($Drive in (Get-WmiObject –Class Win32_LogicalDisk –ComputerName $server)) {
                $column ++
                $InventorySheet.Cells.Item($row,$column) = $Drive.FreeSpace/1GB
                If ($InventorySheet.Cells.Item(1,$column) -notmatch "FreeSpace") {
                   $InventorySheet.Cells.Item(1,$column) = "$($Drive.Name) FreeSpaceGB"
                } 
    	    $column ++
    	    $InventorySheet.Cells.Item($row,$column) = $Drive.Size/1GB
                If ($InventorySheet.Cells.Item(1,$column) -notmatch "CapacityGB") {
                   $InventorySheet.Cells.Item(1,$column) = "$($Drive.Name) CapacityGB"
                } 
          }


    I hope this post has helped!

    • Marked as answer by Kece Tuesday, May 13, 2014 11:47 AM
    Tuesday, May 13, 2014 10:20 AM

All replies

  • You can write to excel spreadsheets from PowerShell, however it's a pain in the neck. Outputting to CSV is more common, followed by XML or HTML reports.
    Monday, May 12, 2014 12:14 PM
  • Just output to CSV instead or format:

    Get-WmiObject –Class Win32_LogicalDisk|
         Select-Object $Filter |
         export-csv file.csv


    ¯\_(ツ)_/¯

    Monday, May 12, 2014 12:55 PM
  • Just output to CSV instead or format:

    Get-WmiObject –Class Win32_LogicalDisk|
         Select-Object $Filter |
         export-csv file.csv


    ¯\_(ツ)_/¯

    Thank you, it worked to put this in an csv. i know gotta figure out how i just can get specific asked-information into Excel/csv. Thanks again.
    Monday, May 12, 2014 1:52 PM
  • What is missing?

    Your filter looks wrong.

    $Filter = @{E={$_.Name};N="DiskName"}, 
              @{E={$_.FileSystem};N="FileSystem"}, 
              @{E={[int]$($_.BlockSize)};N="BlockSizeKB"}, 
              @{E={[int]$($_.Size / 1gb)};N="CapacityGB"}, 
              @{E={[int]$($_.Freespace/1GB)};N="FreeSpaceGB"}
    
    #out put looks like this;
    #TYPE Selected.System.Management.ManagementObject
    "DiskName","FileSystem","BlockSizeKB","CapacityGB","FreeSpaceGB"
    "C:","NTFS","0","911","651"
    "D:",,"0","0","0"
    "E:",,"0","0","0"
    "F:","NTFS","0","298","225"
    "G:",,"0","0","0"
    "H:",,"0","0","0"
    "I:",,"0","0","0"
    "K:","NTFS","0","20","2"


    ¯\_(ツ)_/¯

    Monday, May 12, 2014 2:13 PM
  • Are you asking how to change the order of the columns?  Just change the order.  They export in the order specified.


    ¯\_(ツ)_/¯

    Monday, May 12, 2014 2:16 PM
  • Start here:

    $computer='omega'
    $outcsv='c:\scripts\test.csv'
    $Filter=@{N='DiskName'        ;E={$_.Name                  }}, 
                @{N='FileSystem'      ;E={$_.FileSystem            }}, 
                @{N='FreeSpaceGB'  ;E={[int]$($_.Freespace/1GB) }},
                @{N='BlockSizeKB'   ;E={[int]$($_.BlockSize)     }}, 
                @{N='CapacityGB'    ;E={[int]$($_.Size / 1gb)    }}
    
    Get-WmiObject –Class Win32_LogicalDisk –ComputerName $computer | 
        Select-Object $Filter |
        Export-Csv $outcsv -NoTypeInformation
        




    ¯\_(ツ)_/¯





    • Edited by jrv Monday, May 12, 2014 2:30 PM
    Monday, May 12, 2014 2:24 PM
  • I think this will give you what it seems like you're asking for, but I'm not sure it's what you want. 

    $OutputFile = "c:\temp\DiskInventory.xlsx"
    $serverlist = "server1","server2"
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Add()
    1..2 | ForEach {$workbook.worksheets.item(2).Delete()}
    $InventorySheet = $workbook.Worksheets.Item(1)
    $InventorySheet.Name = 'Server Drive Inventory'
    $InventorySheet.Activate() | Out-Null
    $row = 5
    $InventorySheet.Cells.Item(1,1) = "Server" Foreach ($server in $serverlist) { $column = 1 $InventorySheet.Cells.Item($row,$column) = $server Try { Foreach ($Drive in (Get-WmiObject –Class Win32_LogicalDisk –ComputerName $server)) { $column ++ $InventorySheet.Cells.Item($row,$column) = $Drive.FreeSpace/1GB If ($InventorySheet.Cells.Item(1,$column) -notmatch "FreeSpace") { $InventorySheet.Cells.Item(1,$column) = "$($Drive.Name) FreeSpaceGB" } } } Catch { $InventorySheet.Cells.Item($row,2) = "Inaccessible" } $row ++ } $workbook.SaveAs($OutputFile) $excel.Quit() [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null


    I hope this post has helped!


    • Edited by Rhys W Edwards Monday, May 12, 2014 3:02 PM
    • Marked as answer by Kece Tuesday, May 13, 2014 11:48 AM
    Monday, May 12, 2014 3:00 PM
  • Sorry for my late reaction --


    Result i get is almost the same i tested yesterday- 

    With this code i get :

    DiskName,"FileSystem","FreeSpaceGB","BlockSizeKB","CapacityGB"
    C:,"NTFS","13","0","68"
    D:,"NTFS","543","0","4657"
    E:,,"0","0","0"
    

    Tuesday, May 13, 2014 8:13 AM
  • Hey thanks for your reaction i get this error I use your code- but it makes the CSV file. 

    Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"
    At line:5 char:17
    + 1..2 | ForEach {$workbook.worksheets.item(2).Delete()}
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
        + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI
     
    Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"
    At line:5 char:17
    + 1..2 | ForEach {$workbook.worksheets.item(2).Delete()}
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
        + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI
     
    Out-N : The term 'Out-N' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the sp
    elling of the name, or if a path was included, verify that the path is correct and try again.
    At line:29 char:90
    + ... ject]$excel) | Out-N
    +                    ~~~~~
        + CategoryInfo          : ObjectNotFound: (Out-N:String) [], CommandNotFoundException
        + FullyQualifiedErrorId : CommandNotFoundException


    result of the code:

    Server	A: FreeSpaceGB	C: FreeSpaceGB	E: FreeSpaceGB
    			
    			
    			
    192.X.X.X	13,40331268	543,4093399	0
    192.X.X.X	0	16,05441666	66,05538177
    

    Is it possible to get - Diskcapacity + freescape? 

    btw im also exprimenting on this code

    Thanks!

    Tuesday, May 13, 2014 8:17 AM
  • Yes, it's easy enough to get the additional fields but you didn't ask for that and weren't clear on how you wanted them arranged so I didn't include them.

    The last error is because you didn't copy the code correctly or modified it in some way - Out-Null is the cmdlet I posted, Out-N means something got cut off somewhere.  

    I'd have to guess that something else might have been cutoff or copied incorrectly because I ran the code myself before posting it and it worked.  Excel opens with three worksheets by default and the part of the code where it errored is used to remove the extra worksheets.  You say it creates the csv, so maybe you changed the name of the output file to .csv instead of xlsx.  CSV do not have worksheets, so perhaps that's why the first two errors.  Just remove that 1..2 command if that's the way you want to go.

    To add the capacity modify the main foreach loop for the drives like this:

          Foreach ($Drive in (Get-WmiObject –Class Win32_LogicalDisk –ComputerName $server)) {
                $column ++
                $InventorySheet.Cells.Item($row,$column) = $Drive.FreeSpace/1GB
                If ($InventorySheet.Cells.Item(1,$column) -notmatch "FreeSpace") {
                   $InventorySheet.Cells.Item(1,$column) = "$($Drive.Name) FreeSpaceGB"
                } 
    	    $column ++
    	    $InventorySheet.Cells.Item($row,$column) = $Drive.Size/1GB
                If ($InventorySheet.Cells.Item(1,$column) -notmatch "CapacityGB") {
                   $InventorySheet.Cells.Item(1,$column) = "$($Drive.Name) CapacityGB"
                } 
          }


    I hope this post has helped!

    • Marked as answer by Kece Tuesday, May 13, 2014 11:47 AM
    Tuesday, May 13, 2014 10:20 AM
  • Thank you very much. You saved my day :) i need to make the GB amount integers. that i'm finished and i will try to handle the rest by my self thanks i appreciate this alot. 
    Tuesday, May 13, 2014 11:56 AM
  • Sorry for my late reaction --


    Result i get is almost the same i tested yesterday- 

    With this code i get :

    DiskName,"FileSystem","FreeSpaceGB","BlockSizeKB","CapacityGB"
    C:,"NTFS","13","0","68"
    D:,"NTFS","543","0","4657"
    E:,,"0","0","0"

    What is wrong with that.  That is perfect CSV and will open correctly in Excel.  I don't think any  of us understand what you are asking.  Why doesn't that CSV do what you need?


    ¯\_(ツ)_/¯

    Tuesday, May 13, 2014 2:23 PM
  • Hey thanks for your reaction i get this error I use your code- but it makes the CSV file. 

    Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"
    At line:5 char:17
    + 1..2 | ForEach {$workbook.worksheets.item(2).Delete()}
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
        + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI
     
    Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"
    At line:5 char:17
    + 1..2 | ForEach {$workbook.worksheets.item(2).Delete()}
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
        + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI
     
    Out-N : The term 'Out-N' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the sp
    elling of the name, or if a path was included, verify that the path is correct and try again.
    At line:29 char:90
    + ... ject]$excel) | Out-N
    +                    ~~~~~
        + CategoryInfo          : ObjectNotFound: (Out-N:String) [], CommandNotFoundException
        + FullyQualifiedErrorId : CommandNotFoundException


    result of the code:

    Server	A: FreeSpaceGB	C: FreeSpaceGB	E: FreeSpaceGB
    			
    			
    			
    192.X.X.X	13,40331268	543,4093399	0
    192.X.X.X	0	16,05441666	66,05538177

    Is it possible to get - Diskcapacity + freescape? 

    btw im also exprimenting on this code

    Thanks!

    You are demanding others write a script for you and you are not making any effort to understand how to use a script.  The answer was given explicitly long ago.  You just don't understand.  Now you have a much more complicated and error prone script that does less that your original script.  Please try to think about what is happening and why.


    ¯\_(ツ)_/¯

    Tuesday, May 13, 2014 2:27 PM