none
Convert array into .csv file

    Pertanyaan

  • I need to use the command "Cluster Group" in Powershell to create a .csv file using the output:

    Listing status for all available resource groups:

     

    Group                Node            Status

    -------------------- --------------- ------

    AutEas-US            FILE-NY         Online

    Home-US              FILE-NY         Online

    Cluster Group        FILE-NY         Online

    Shared-US            FILE-NY         Online

    Apps-US              FILE-NY         Online

    This is the output that i am getting from running the command. i would like to loop through it and only capture the items under Group,Node,Status. I'm not sure how to do this capturing only the data, can anyone help?

    $CluGroups = Cluster Group

    $CluItems = @()

    FOREACH ($CluGroup in $CluGroups)
    {
        $ClusVar = $CluGroup
        $ClusVar
        $CluItems += $ClusVar
    }

    29 Maret 2012 23:16

Jawaban

  • OK, you are super close, anyway to remove the spaces:

    $ClusGroups=(cluster group)[7..9999]|?{$_}
    $output=foreach($line in $ClusGroups){
        New-Object PSObject -Property @{
               Group=$line.SubString(0,20)
               Node=$line.SubString(21,15)
               Status=$line.SubString(37,6)
            }
    }
    $output | Sort-Object group,node,status | Export-Csv "C:\Scripts\output.csv" -NoTypeInformation

    • Ditandai sebagai Jawaban oleh CMR NYC 05 April 2012 12:01
    31 Maret 2012 13:26

Semua Balasan

  • YOu would have to read the input one line at a ti,e and split the line into colums.

    $clustergroups.Split(' ')

    This will produce an array of columns that can be accessed by position.Of course you will need to remove the hea

    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(' ',[system.StringSplitOptions]::RemoveEmptyEntries)
        New-Object PSObject -Property @{
    	   Group=$cols[0]
               Node=$cols[1]
               Status=$cols[2]
            }
    }

    der and undereline like this:

    $CluGroups = Cluster Group
    $ClusGroup=$ClusGroups[2..999]


    ¯\_(ツ)_/¯




    • Diedit oleh jrv 29 Maret 2012 23:43
    29 Maret 2012 23:24
  • That doesn't seem to work:

    New-Object : A parameter cannot be found that matches parameter name 'PSObject'.
    At C:\Scripts\ClusterCsv.ps1:7 char:25
    +     New-Object -PSObject <<<<  -Property @{
        + CategoryInfo          : InvalidArgument: (:) [New-Object], ParameterBindingException
        + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

    This is the code that i am running:

    $CluGroups = Cluster Group
    $CluItems = @()
    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(" ")
        New-Object -PSObject -Property @{
    	   Group=$cols[0]
               Node=$cols[1]
               Status=$cols[2]
            }
    }

    I don't know why it's not working?
    29 Maret 2012 23:34
  • Sorry I got the parameters wrong.

    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(' ',[system.StringSplitOptions]::RemoveEmptyEntries)
        New-Object PSObject -Property @{
    	   Group=$cols[0]
               Node=$cols[1]
               Status=$cols[2]
            }
    }

    This works


    ¯\_(ツ)_/¯

    29 Maret 2012 23:42
  • OK, but doesent the new object need a name? How can i get it to a .csv?

    $CluGroups = Cluster Group

    $CluItems = @()

    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(' ',[system.StringSplitOptions]::RemoveEmptyEntries)
        New-Object PSObject -Property @{
        Group=$cols[0]
               Node=$cols[1]
               Status=$cols[2]
            }
    }

    29 Maret 2012 23:54
  • i addes this but its not really giving me the output i'm looking for.


    Node                                          Status                                       Group                                      
    ----                                          ------                                       -----                                      
    status                                        for                                          Listing                                    
                                                                                                                                          
                                                                                                                                          
                                                                                                                                          
    Node                                          Status                                       Group                                      
                                                                                                                                          
    ---------------                               ------                                       --------------------                       
                                                                                                                                          
    FILE-NY                                       Online                                       AutEas-US                                  
                                                                                                                                          
    FILE-NY                                       Online                                       Home-US                                    
                                                                                                                                          
    Group                                         FILE-NY                                      Cluster                                    
                                                                                                                                          
    FILE-NY                                       Online                                       Shared-US                                  
                                                                                                                                          
    FILE-NY                                       Online                                       Apps-US                                    
                                                                                                           

    ***********************************************

    $CluGroups = Cluster Group
    $CluItems = @()
    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(' ',[system.StringSplitOptions]::RemoveEmptyEntries)
        $Items = New-Object PSObject -Property @{
    	   Group=$cols[0]
               Node=$cols[1]
               Status=$cols[2]
            }
            $CluItems += $Items
    }
    $CluItems | Out-File "C:\Scripts\CMRTest.txt"

    30 Maret 2012 0:00
  • I thought you wanted a CSV.  Out-File cannot output objects.  It will convert everything into a string.  Use Export-Csv.

    And stop with the empty array stuff.

    $CluGroups = Cluster Group
    # you need to fix the returned strings here first
    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(' ',[system.StringSplitOptions]::RemoveEmptyEntries)
        $Items = New-Object PSObject -Property @{
    	   Group=$cols[0]
               Node=$cols[1]
               Status=$cols[2]
            }
    } | Export-Csv c:\scripts\output.csv


    ¯\_(ツ)_/¯


    • Diedit oleh jrv 30 Maret 2012 0:08
    30 Maret 2012 0:08
  • I do need it in a .csv. I used the out-file to show you what the code was producing. How can i get it into a .csv?
    30 Maret 2012 0:35
  • I'm lost I am not sure what you are telling me to do the code as you send it does not produce an ourput. All i get is the header info in the .csv file? If you can break down what is going on it would be helpful. What does "You need to fix the returned strings here first" mean are you telling me thats what the next line is suppoed to do for me or is there something that i have to do?
    30 Maret 2012 0:47
  • $CluGroups = (Cluster Group)[2..999]
    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(' ',[system.StringSplitOptions]::RemoveEmptyEntries)
        $Items = New-Object PSObject -Property @{
    	   Group=$cols[0]
               Node=$cols[1]
               Status=$cols[2]
            }
    } | Export-Csv c:\scripts\output.csv

    This export the output into a CSV file.

    Hopefully the output of 'cluster groups' is not unicode


    ¯\_(ツ)_/¯

    30 Maret 2012 0:54
  • When I run this i am getting this error:

    An empty pipe element is not allowed.
    At C:\Scripts\ClusterCsv.ps1:9 char:4
    + } | <<<<  Export-Csv c:\scripts\output.csv
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : EmptyPipeElement

    Export-csv usually needs to pipe from an object, no?

    30 Maret 2012 1:00
  • What do you see if you run this:

    $CluGroups = (Cluster Group)[2..999]
    foreach($CluGroup in $CluGroups){
        $cols
    =$CluGroup.Split(' ',[system.StringSplitOptions]::RemoveEmptyEntries)
        $Items
    = New-Object PSObject -Property @{
      
    Group=$cols[0]
              
    Node=$cols[1]
              
    Status=$cols[2]
           
    }
    }


    ¯\_(ツ)_/¯

    30 Maret 2012 1:10
  • There are no errors when I run that, but in the ISE there doesn't look like there is any info processed after line 2 as i step through the code

    30 Maret 2012 1:19
  • What do you see if you just run this: Cluster Group


    ¯\_(ツ)_/¯

    30 Maret 2012 1:34
  • Now what do you see when you do this:

     (Cluster Group)[2..999]


    ¯\_(ツ)_/¯

    30 Maret 2012 1:59
  • This was the result:

    (Cluster Group)[2..999]

    30 Maret 2012 2:05
  • Impossible!!

    Do it this way and see what happens.

    $lines=Cluster Group
    $lines[2..999]



    ¯\_(ツ)_/¯


    • Diedit oleh jrv 30 Maret 2012 2:22
    30 Maret 2012 2:21
  • Same output, is this impossible to do?

    30 Maret 2012 2:29
  • What is this:

    $lines.Count


    ¯\_(ツ)_/¯

    30 Maret 2012 2:33
  • the count is "18"
    30 Maret 2012 2:43
  • This code is giving me the following output in the .csv:

    $CluGroups = (Cluster Group)[2..999]

    $CluItems = @()

    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(" ",[system.StringSplitOptions]::RemoveEmptyEntries)
        $Items = New-Object PSObject -Property @{
        Group=$cols[0]
              Node=$cols[1]
               Status=$cols[2]
            }
            $CluItems += $Items

    }

    $CluItems | Export-Csv "c:\scripts\output.csv"

    I know you said forget about the empty array but its seems to capture the data. No I have a few issues left. How can i start at a particular line and parse out the garbage. also one of the groups has a space in it so the line i circled should be "Cluster Group", "FILE-NY","Online"

    30 Maret 2012 2:51
  • You keep changing the code in ways that make it impossible fo rit to work as intended.  I was try8ing to get you to see your errors but you don't seem to want to follow my lead.

    This is NOT the code I posted. 

    $CluGroups = (Cluster Group)[2..999]
    $CluItems = @()
    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(" ",[system.StringSplitOptions]::RemoveEmptyEntries)
        $Items = New-Object PSObject -Property @{
        Group=$cols[0]
              Node=$cols[1]
               Status=$cols[2]
            }
            $CluItems += $Items
    } 
    $CluItems | Export-Csv "c:\scripts\output.csv"

    My code is this and for a reason.

    $CluGroups = (Cluster Group)[2..999]
    foreach($CluGroup in $CluGroups){
        $cols=$CluGroup.Split(' ',[system.StringSplitOptions]::RemoveEmptyEntries)
        $Items = New-Object PSObject -Property @{
    	   Group=$cols[0]
               Node=$cols[1]
               Status=$cols[2]
            }
    } | Export-Csv c:\scripts\output.csv

    I am also going to ask you to run it in the CLI and not the ISE.  The ISE can behave eratically.

    Do NOT try to retype the code. Just copy and paste it into the CLI.

    The file you jst posted is proof that teh code works as intended and that your attempts to iunterpret my instructiosn are causing it to fail.

    This is basic POwerSHell.  Sort of PowerShell 101.  Nothing tricky is happening.  If you wuld just run it eactly as posted it would work as required.


    ¯\_(ツ)_/¯

    30 Maret 2012 3:14
  • I just tried to paste it in as you said into the shell not the ISE. It would not run in that manner. I then pasted it in to a text file and called it SampleCode.ps1 and executed it from the Shell window and not the ISE The results are the same as before: An empty pipe element is not allowed.

     

    30 Maret 2012 3:25
  • I asked you to paste the code in.  I did not say to run it  from a file.

    Copy it from the sceen and paste it into the CLI console.  Do you know how to copy and paste in Windows?


    ¯\_(ツ)_/¯

    30 Maret 2012 4:21
  • I see whare ytou are having an issue.  Yu have a bad cluster group name.  The bane"Cluster Group" has a space in it.  It should not.  If you are using this naming cxonvention (which will cause other issues later) we will need to do field bound extractions. It works the same way but requires fixed length fields.

    $ClusGroups=(cluster group)[2..9999]
    foreach($line in $ClusGroups){
        New-Object PSObject -Property @{
    	   Group=$line.SubString(0,20)
               Node=$line.SubString(21,15)
               Status=$line.SubString(37,6)
            }
    } | Export-Csv output.csv
    This will prevent names with spaces from breaking the output.

    This was what I was trying to see.  Every time you didn't follow the steps distracted me from narroweing in on why your system was breaking and mine was not.  Once I realizewd that you had odd names for cluster groups I saw the issue you were getting and teh solution.  Every time you chnaged the code such as by trying to inject soem kind of unnecessary array into the code it made it harder to see the issue.

    Sray away from creating empty arrays except when you really understand why you are using them.  They are one of the biggest errors I have seen for newby PowerShell scripters.  They seem to abe all the rage with the new guys because someone suggested they were a fix for evry bad thing that could every happen in a script.  They have now become the source of many a bad habit.

    Underatanding and using the default 'null' property in a pipeline is far more useful than wrapping everything in an array.


    ¯\_(ツ)_/¯


    • Diedit oleh jrv 30 Maret 2012 14:34
    30 Maret 2012 14:29
  • Still having an issue:

    PS C:\Scripts> C:\Scripts\SampleCode.ps1
    An empty pipe element is not allowed.
    At C:\Scripts\SampleCode.ps1:8 char:4
    + } | <<<<  Export-Csv "C:\Scripts\output.csv"
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : EmptyPipeElement
    31 Maret 2012 0:53
  • Still having an issue:

    PS C:\Scripts> C:\Scripts\SampleCode.ps1
    An empty pipe element is not allowed.
    At C:\Scripts\SampleCode.ps1:8 char:4
    + } | <<<<  Export-Csv "C:\Scripts\output.csv"
        + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : EmptyPipeElement

    No one can know what your issue is if you do not post the code you are running.  I asked you to copy and paste the code but I see you are still putting it in a file.  This cahnges the mechanism of  eexcecution in a way that is hard to know.  If you would at tleas post a link to teh file so we could download it it would be better.


    ¯\_(ツ)_/¯

    31 Maret 2012 2:05
  • its the code you sent me previously:

    $ClusGroups=(cluster group)[2..9999]
    foreach($line in $ClusGroups){
       
    New-Object PSObject -Property @{
      
    Group=$line.SubString(0,20)
              
    Node=$line.SubString(21,15)
              
    Status=$line.SubString(37,6)
           
    }
    } | Export-Csv output.csv

    31 Maret 2012 2:37
  • If I run that exact code it works absolutely correctly.  I get a fully qualified CSV file with all of the fields.

    What version of POwerSHell are you running?


    ¯\_(ツ)_/¯

    31 Maret 2012 3:02
  • Version 2 on Server 2003 x64.

    31 Maret 2012 3:11
  • Ok - that last time I mucked it up.

    Try this it will work now.

    $ClusGroups=(cat clus.txt)[2..9999]
    $output=foreach($line in $ClusGroups){
        New-Object PSObject -Property @{
               Group=$line.SubString(0,20)
               Node=$line.SubString(21,15)
               Status=$line.SubString(37,6)
            }
    } 
    $output | Export-Csv output.csv


    ¯\_(ツ)_/¯

    31 Maret 2012 3:57
  • $ClusGroups=(cat clus.txt) is that right?
    31 Maret 2012 4:04
  • After running the code you sent this is what I get::

    $ClusGroups=(cat clus.txt)[2..9999]
    $output=foreach($line in $ClusGroups){
        New-Object PSObject -Property @{
               Group=$line.SubString(0,20)
               Node=$line.SubString(21,15)
               Status=$line.SubString(37,6)
            }
    }
    $output | Export-Csv output.csv

    PS C:\Scripts> .\SampleCode.ps1
    Get-Content : Cannot find path 'C:\Scripts\clus.txt' because it does not exist.
    At C:\Scripts\SampleCode.ps1:1 char:17
    + $ClusGroups=(cat <<<<  clus.txt)[2..9999]
        + CategoryInfo          : ObjectNotFound: (C:\Scripts\clus.txt:String) [Ge
       t-Content], ItemNotFoundException
        + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCo
       ntentCommand

    Cannot index into a null array.
    At C:\Scripts\SampleCode.ps1:1 char:28
    + $ClusGroups=(cat clus.txt)[ <<<< 2..9999]
        + CategoryInfo          : InvalidOperation: (System.Object[]:Object[]) [],
        RuntimeException
        + FullyQualifiedErrorId : NullArray

    You cannot call a method on a null-valued expression.
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : InvalidOperation: (SubString:String) [], Runtime
       Exception
        + FullyQualifiedErrorId : InvokeMethodOnNull

    Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null
    .
    At C:\Scripts\SampleCode.ps1:9 char:21
    + $output | Export-Csv <<<<  output.csv
        + CategoryInfo          : InvalidData: (:) [Export-Csv], ParameterBindingV
       alidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,M
       icrosoft.PowerShell.Commands.ExportCsvCommand

    31 Maret 2012 4:12
  • $ClusGroups=(cluster group)[2..9999]
    $output=foreach($line in $ClusGroups){
        New-Object PSObject -Property @{
               Group=$line.SubString(0,20)
               Node=$line.SubString(21,15)
               Status=$line.SubString(37,6)
            }
    } 
    $output | Export-Csv output.csv

    That was my test code.  You need to use the original.


    ¯\_(ツ)_/¯

    31 Maret 2012 4:20
  • More Errors:

    $ClusGroups=(cluster group)[2..9999]
    $output
    =foreach($line in $ClusGroups){
       
    New-Object PSObject -Property @{
              
    Group=$line.SubString(0,20)
              
    Node=$line.SubString(21,15)
              
    Status=$line.SubString(37,6)
           
    }
    }
    $output
    | Export-Csv output.csv

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Substring" with "2" argument(s): "Index and length must refe
    r to a location within the string.
    Parameter name: length"
    At C:\Scripts\SampleCode.ps1:4 char:33
    +            Group=$line.SubString <<<< (0,20)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    PS C:\Scripts>

    31 Maret 2012 4:25
  • You must have blank lines in your the input.  You will have to filter the output to remove blank lines.


    ¯\_(ツ)_/¯

    31 Maret 2012 5:22
  • how do you suggest i fix that?
    31 Maret 2012 12:49
  • Just use a where clause to filter out empty lines.

    $ClusGroups=(cluster group)[2..9999]|?{$_}


    ¯\_(ツ)_/¯

    31 Maret 2012 13:02
  • OK, you are super close, anyway to remove the spaces:

    $ClusGroups=(cluster group)[7..9999]|?{$_}
    $output=foreach($line in $ClusGroups){
        New-Object PSObject -Property @{
               Group=$line.SubString(0,20)
               Node=$line.SubString(21,15)
               Status=$line.SubString(37,6)
            }
    }
    $output | Sort-Object group,node,status | Export-Csv "C:\Scripts\output.csv" -NoTypeInformation

    • Ditandai sebagai Jawaban oleh CMR NYC 05 April 2012 12:01
    31 Maret 2012 13:26
  • Just trim it.

    ¯\_(ツ)_/¯

    31 Maret 2012 14:15
  • How can i trim them?
    31 Maret 2012 22:03
  • Just use .Trim()

    Look into System.String http://msdn.microsoft.com/en-us/library/t97s7bs3.aspx


    ¯\_(ツ)_/¯

    31 Maret 2012 22:20
  • Function Get-ClusterGroup{
    $ClusGroups=(cluster group)[7..9999]|?{$_}
    $output=foreach($line in $ClusGroups){
        New-Object PSObject -Property @{
               Group=$line.SubString(0,20).trim()
               Node=$line.SubString(21,15).trim()
               Status=$line.SubString(37,6).trim()
            }
    } 
    $output | Sort-Object group,node,status | Export-Csv "C:\Scripts\output.csv" -NoTypeInformation
    $output | FT
    }
    Get-ClusterGroup

    The trim worked. I ran this on a few other clusters that i have and I ran into another issue:

    The default name for the SQL resource is longer than the other script was expecting and runs into the "Node" column. I tried playing with the lenghts but i am not able to fix the issue, any ideas?

    02 April 2012 11:37
  • $output | ft -auto

    It does not run into it it just overwrites on screen.

    If the text file is truncating then you need to change the output format of the cluster utility.


    ¯\_(ツ)_/¯

    02 April 2012 15:40
  • Yuou marked your comment as an answer.  The asnwer is the one that solved you issue and not jsut you last comment.


    ¯\_(ツ)_/¯

    05 April 2012 12:14