Automation

 View Only
  • 1.  Formatting CSV Exports for Excel Import

    Posted May 16, 2013 05:22 PM

    Hi,

    I've got a script that I run which collects host configuration information as well as VM configuration information (RAM/CPU/Disk/etc...) and while I have the script working great for the most part, I'm wondering if anyone can show me the easiest way to take a cluster name such as "SEA Test Cluster" and remove the spaces in it before exporting to the CSV, which would make it way easier for Excel delimiters to format columns correctly.

    Here's the contents of the script I'm using (Host/cluster script):

    $myCol = @()

    ForEach ($Cluster in Get-Cluster)

        {

            ForEach ($vmhost in ($cluster | Get-VMHost))

            {

                $VMView = $VMhost | Get-View

                            $VMSummary = “” | Select HostName, ClusterName, MemorySizeGB, CPUSockets, CPUCores

                            $VMSummary.HostName = $VMhost.Name

                            $VMSummary.ClusterName = $Cluster.Name

                            $VMSummary.MemorySizeGB = $VMview.hardware.memorysize / 1024Mb

                            $VMSummary.CPUSockets = $VMview.hardware.cpuinfo.numCpuPackages

                            $VMSummary.CPUCores = $VMview.hardware.cpuinfo.numCpuCores

                            $myCol += $VMSummary

                        }

                }

    $myCol #| out-gridview

    Here's the command I'm running at the CLI: .\ESXiHost_Stats.ps1 | FT HostName, ClusterName, MemorySizeGB, CPUSockets, CPUCores > FileName.csv

    I'm using the FT to format the table so the host properties are laid out horizontally instead of vertically, which makes it easier for sorting and viewing in Excel.  Our host names don't have any spaces in them, however, our Cluster names do, so I'd like to pull the spaces out with as much automation as possible.

    Example: Original Cluster name: SEA DEV Cluster  --> New ClusterName when exported to CSV: SEADEVCluster

    I'm still learning PS, so not sure if this is easy or not.  Other Option I have is to put Underscores in vCenter for cluster names or something, which I'd like to avoid if I don't have to do it.

    I've heard great things about this community, so am not doubting someone can quickly point me in the right direction! :smileygrin:

    Thanks!



  • 2.  RE: Formatting CSV Exports for Excel Import
    Best Answer

    Posted May 16, 2013 05:37 PM

    Removing the blanks in the clustername could be done like this

     $VMSummary.ClusterName = $Cluster.Name.Replace(" ","") 


  • 3.  RE: Formatting CSV Exports for Excel Import

    Posted May 16, 2013 06:02 PM

    Perfect!  I owe ya a vbeer :smileyhappy:



  • 4.  RE: Formatting CSV Exports for Excel Import

    Posted May 16, 2013 09:01 PM

    vCheers :smileygrin: