none
System.Object[] Message when converting txt to csv

    Question

  • Hi,

    I have a powershell script which converts a text file to a csv and then processes it.

    Here is the output of the txt file

    I am running this piece of code to convert this to a csv

    Get-Content "D:\TRHMIBS\BIN\TRH_NUM_ETC_SWF.txt" |
    	 ForEach-Object{
    		$p = @{ }
    	        $p.PLAZA_ID, $p.LANE_ID, $p.Lane_POS, $p.LANE_TYPE, $p.START_TIME, $p.END_TIME, $p.LANE_COMP, $p.CURRENT_VALUE, $p.TYPICAL_VALUE, $p.UFM_COUNT, $p.PLAZA_NAME = $_.Split(' ', [system.StringSplitOptions]::RemoveEmptyEntries)
    		New-Object PsObject -Property $p
    	    }|Export-Csv D:\TRHMIBS\BIN\TRH_NUM_ETC_SWF.csv -NoTypeInformation
    

    The output of the csv file is not what I am expecting

    I am seeing a System.Object message in the csv file. I am expecting all the values in the txt file as specific columns in csv. Can someone please advise what I am missing. Thanks in advance.

    Wednesday, March 08, 2017 8:56 PM

All replies

  • Hi,

    I have a text file as shown below. I am running a script to convert the data to a csv file for further processing.

    The script is below

    $inputData = get-content "D:\BKP.txt"
    & {
      for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
        if ( $inputData[$row] ) {
          $inputData[$row] | select-string '^(\d+)\s+(\d+)\s+(\d+)\s+(\d+)' | foreach-object {
            new-object PSObject -property @{
              "LANE_COM" = $_.Matches[0].Groups[1].Value
              "LAST_RUN" = $_.Matches[0].Groups[2].Value
              "ALERT" = $_.Matches[0].Groups[3].Value
            } | select-object LANE_COM,LAST_RUN,ALERT
          }
        }
      }
    } | export-csv "D:\outputdata.csv" -notypeinformation

    The Script is not running as expected. Any suggestions.

    Thanks

    Saturday, February 25, 2017 1:52 AM
  • hat is it not doing?  Why do you think it should work?

    Why are you using "&"??

    It is unnecessary and pointless.

    Your RegEx appears to not match the data.

    Posting images of data is useless in a forum. No one can copy and test with your data.

    Try this to see what I mean:

    'TRER    02/15/2017 06;00 am     1' -match '^(\d+)\s+(\d+)\s+(\d+)\s+(\d+)'

    The output of "-match" and Select-String nothing if there are 0 matches.

    This kind of file is best parsed using "SubString"


    \_(ツ)_/


    • Edited by jrv Saturday, February 25, 2017 3:15 AM
    Saturday, February 25, 2017 3:09 AM
  • Try this:
    $inputData = get-content "D:\BKP.txt" | Select-Object -Skip 2
    foreach ( $row in $inputData) {
        $row -match '^(\w+)\s+(\d{2}/\d{2}/\d{4}\s+\d{2}:\d{2}\s+(AM|PM))\s+(\d+)' | Out-Null
        New-Object  PSObject -Property @{
            'LANE_COM' = $Matches[1]
            'LAST_RUN' = $Matches[2]
            'ALERT' = $Matches[4]
        } | export-csv "D:\outputdata.csv" -NoTypeInformation -Append -Delimiter ','
    }


    Grüße - Best regards

    PS:> (79,108,97,102|%{[char]$_})-join''

    Saturday, February 25, 2017 3:09 AM
  • Thanks for your reply, I am seeing this error when running the script.

    PS D:\TRHMIBS\UFM\3HRKPI> .\3HRKPI.ps1
    Cannot index into a null array.
    At D:\TRHMIBS\UFM\3HRKPI\3HRKPI.ps1:13 char:31
    +         'LANE_COM' = $Matches[ <<<< 1]
        + CategoryInfo          : InvalidOperation: (1:Int32) [], RuntimeException
        + FullyQualifiedErrorId : NullArray

    Export-Csv : A parameter cannot be found that matches parameter name 'Append'.
    At D:\TRHMIBS\UFM\3HRKPI\3HRKPI.ps1:16 char:66
    +     } | export-csv "D:\outputdata.csv" -NoTypeInformation -Append <<<<  -Delimiter ','
        + CategoryInfo          : InvalidArgument: (:) [Export-Csv], ParameterBindingException
        + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.ExportCsvCommand

    Export-Csv : A parameter cannot be found that matches parameter name 'Append'.
    At D:\TRHMIBS\UFM\3HRKPI\3HRKPI.ps1:16 char:66
    +     } | export-csv "D:\outputdata.csv" -NoTypeInformation -Append <<<<  -Delimiter ','
        + CategoryInfo          : InvalidArgument: (:) [Export-Csv], ParameterBindingException
        + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.ExportCsvCommand

    Script is erroring out here

    'LANE_COM' = $Matches[1]

    Below is the text file

    LANE_COM LAST_RUN                 ALERT                                         
    -------- ------------------- ----------                                         
    Xerox    02/15/2017 06:00 AM          1                                         
    

    Saturday, February 25, 2017 3:52 AM
  • With a fixed width columnar output this is far easier and more accurate:

    #file =
    <#
    LANE_COM LAST_RUN                 ALERT
    -------- ------------------- ----------
    TRER     02/15/2017 06:00 AM          1
    Xerox    02/15/2017 06:00 AM          1          
    #>
    
    Get-Content $filename |
        ForEach-Object{
    		[pscustomobject]@{
    			LANE_COM = $_.SubString(0,8).Trim()
    			LAST_RUN = $_.SubString(10, 19).Trim()
    			ALERT = $_.SubString(30,10).Trim()
    	    }
    	}
    


    \_(ツ)_/

    Saturday, February 25, 2017 5:14 AM
  • Here is another approach.

    $file = Get-Content 'D:\BKP.txt'
    
    $headers = $file[1].Trim() -split '\s+'
    $data = $file[3].Trim() -split '\s{2,}'
    
    $hash = [ordered]@{}
    for($i=0;$i -lt $headers.Length;$i++) {
        $hash.Add($headers[$i], $data[$i])
    }
    
    $obj = New-Object PSObject -Property $hash
    $obj | Export-Csv 'D:\outputdata.csv' -NoType
    Saturday, February 25, 2017 6:33 AM
  • Thanks Jrv, I am seeing an error and it has to do with the LANE_COM object which is actually a string.

    Get-Content D:\TRHMIBS\UFM\3HRKPI\3HRKPI1.txt |
        ForEach-Object{
    		[pscustomobject]@{
    			LANE_COM = $_.SubString(0,8).Trim()
    			LAST_RUN = $_.SubString(10, 19).Trim()
    			ALERT = $_.SubString(30,10).Trim()
    	    }| select-object LANE_COM,LANEID,ALERT
    	}| export-csv D:\TRHMIBS\UFM\LaneReOpen\newfile.csv -notypeinformation
    
    Exception calling "Substring" with "2" argument(s): "Index and length must refer to a location within the string.
    Parameter name: length"
    At D:\TRHMIBS\UFM\3HRKPI\3HRKPI.ps1:15 char:27
    +             LANE_COM = $_.SubString <<<< (0,8).Trim()
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException
    
    
    
    
    
    

    Saturday, February 25, 2017 1:10 PM
  • If your file is not exactly what you posted it to be you will get errors.  No blank lines.  If the file has blank lines or other weird things then you wil have to modify the code to account for this.

    The substrings work just like a substring in any other language.  They cut out a portion of a line. (0,8) is the first 8 characters of the line.  A blank line will cause this error.


    \_(ツ)_/

    Saturday, February 25, 2017 1:15 PM
  • LANE_COM LAST_RUN                 ALERT                                         
    -------- ------------------- ----------                                         
    Xerox    02/15/2017 06:00 AM          1                                         
    

    I have verified and the contents seem to be right. The file only has three lines as displayed above.

    Thanks

    Saturday, February 25, 2017 1:43 PM
  • Off by one this time so this works.

    D:\scripts> Get-Content test.txt |
    >>     ForEach-Object{
    >> [pscustomobject]@{
    >> LANE_COM = $_.SubString(0,8).Trim()
    >> LAST_RUN = $_.SubString(9, 20).Trim()
    >> ALERT = $_.SubString(30,10).Trim()
    >>     }
    >> }|fl
    
    
    LANE_COM : LANE_COM
    LAST_RUN : LAST_RUN
    ALERT    : ALERT
    
    LANE_COM : --------
    LAST_RUN : -------------------
    ALERT    : ---------
    
    LANE_COM : Xerox
    LAST_RUN : 02/15/2017 06:00 AM
    ALERT    : 1
    


    \_(ツ)_/

    Saturday, February 25, 2017 1:49 PM
  • Unfortunately still the same error. It keeps complaining about LANE_COM. I checked the field positions and it is the same as what the script has.

    Get-Content D:\TRHMIBS\UFM\3HRKPI\3HRKPI1.txt |
       ForEach-Object{
    [pscustomobject]@{
    LANE_COM = $_.SubString(0,8).Trim()
    LAST_RUN = $_.SubString(9,20).Trim()
    ALERT = $_.SubString(30,10).Trim()
       }| select-object LANE_COM,LAST_RUN,ALERT
    }| export-csv D:\TRHMIBS\UFM\LaneReOpen\newfile.csv -notypeinformation

    Exception calling "Substring" with "2" argument(s): "Index and length must refer to a location within the string.
    Parameter name: length"
    At D:\TRHMIBS\UFM\3HRKPI\3HRKPI.ps1:13 char:27
    +             LANE_COM = $_.SubString <<<< (0,8).Trim()
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    Saturday, February 25, 2017 2:02 PM
  • Without a full copy of your file we cannot know why you are getting that error.  It is very likely that the file has nulls.  If it came from a mainframe then that is likely what is happening.


    \_(ツ)_/

    Saturday, February 25, 2017 5:41 PM
  • This will help to skip bad characters in the header:

    $file = Get-Content test.txt
    2..($file.Count-1)|
    ForEach-Object{
    	[pscustomobject]@{
    		LANE_COM = $file[$_].SubString(0,8).Trim()
    		LAST_RUN = $file[$_].SubString(9, 20).Trim()
    		ALERT = $file[$_].SubString(30,9).Trim()
    	}
    }


    \_(ツ)_/

    Saturday, February 25, 2017 5:58 PM
  • The output you are seeing is a CSV file.  Open it in Excel or import it to see how it works.

    Import-Csv  D:\TRHMIBS\BIN\TRH_NUM_ETC_SWF.csv

    System.Object[] is because one field i a compound field.

    You will have to do a more complex conversion. This can also be caused by nulls in the file.

    You also only have 11 fields and there are 15 in the file if split on a space.


    \_(ツ)_/



    • Edited by jrv Wednesday, March 08, 2017 9:32 PM
    Wednesday, March 08, 2017 9:28 PM
  • As already mentioned by jrv, you only have 11 fields and if you split by space you get 15. The last 5 end up as an array in PLAZA_NAME.

    This will show you the result of splitting the first line by space:

    (gc TRH_NUM_ETC_SWF.txt)[0].Split(' ', [system.StringSplitOptions]::RemoveEmptyEntries)

    And if you want your fields in a specific order in your csv you can do this:

    $p = [ordered] @{}

    Wednesday, March 08, 2017 9:58 PM
  • Hi,

    I have a script which reads the text file below and converts into csv. The script runs without any issues however the csv is not generated. Can you point out as to what I am missing? Any help will be greatly appreciated. Thanks and Regards.

    My script is below

    $inputData = get-content "D:\TRHMIBS\VPS\RTN\RTNMissingImageAlert1.txt"
    & {
      for ( $row = 2; $row -lt $inputData.Count; $row++ ) {
        if ( $inputData[$row] ) {
          $inputData[$row] | select-string '^(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)+(\d+)' | foreach-object {
            new-object PSObject -property @{
              "PLAZA_" = $_.Matches[0].Groups[1].Value
              "LAN" = $_.Matches[0].Groups[2].Value
              "LA" = $_.Matches[0].Groups[3].Value
    	  "LANE_TYP" = $_.Matches[0].Groups[4].Value
    	  "LANE_COM" = $_.Matches[0].Groups[5].Value
              "CURRENT_VA" = $_.Matches[0].Groups[6].Value
              "TYPICAL_VA" = $_.Matches[0].Groups[7].Value
              "UFM_COUNT" = $_.Matches[0].Groups[8].Value
            } | select-object PLAZA_,LAN,LA,LANE_TYP,LANE_COM,CURRENT_VA,TYPICAL_VA,UFM_COUNT
          }
        }
      }
    } | export-csv D:\TRHMIBS\VPS\RTN\newfile.csv -notypeinformation

    Monday, April 17, 2017 9:21 PM
  • $lines = get-content D:\TRHMIBS\VPS\RTN\RTNMissingImageAlert1.txt
    $results = foreach($line in $lines){
     	$line -match '^(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)+(\d+)'
    	[pscustomobject]@{
    			PLAZA_ = $_.Matches[0].Groups[1].Value
    			LAN = $_.Matches[0].Groups[2].Value
    			LA = $_.Matches[0].Groups[3].Value
    			LANE_TYP = $_.Matches[0].Groups[4].Value
    			LANE_COM = $_.Matches[0].Groups[5].Value
    			CURRENT_VA = $_.Matches[0].Groups[6].Value
    			TYPICAL_VA = $_.Matches[0].Groups[7].Value
    			UFM_COUNT = $_.Matches[0].Groups[8].Value
    	}
    }
    $results | export-csv D:\TRHMIBS\VPS\RTN\newfile.csv -notypeinformation


    \_(ツ)_/



    • Edited by jrv Monday, April 17, 2017 10:30 PM
    Monday, April 17, 2017 9:28 PM
  • Hi jrv,

    I am seeing this message when I run the code.

    Line 24 --> $results = | export-csv D:\TRHMIBS\VPS\RTN\newfile.csv -notypeinformation

    powershell .\RTNMissingImageAlert.ps1
    An empty pipe element is not allowed.
    At D:\TRHMIBS\VPS\RTN\RTNMissingImageAlert.ps1:24 char:13
    + $results = | <<<<  export-csv D:\TRHMIBS\VPS\RTN\newfile.csv -notypeinformati
    on
        + CategoryInfo          : ParserError: (:) [], ParseException
        + FullyQualifiedErrorId : EmptyPipeElement

    Monday, April 17, 2017 10:26 PM
  • Sorry - typo:

    $results | export-csv D:\TRHMIBS\VPS\RTN\newfile.csv -notypeinformation


    \_(ツ)_/

    Monday, April 17, 2017 10:30 PM
  • Hi Jrv,

    I am seeing a different error this time.

    PLAZA_ = $_.Matches[0].Groups[1].Value

    Cannot index into a null array.

    At D:\TRHMIBS\VPS\RTN\RTNMissingImageAlert.ps1:14 char:24
    +             PLAZA_ = $_.Matches[ <<<< 0].Groups[1].Value
        + CategoryInfo          : InvalidOperation: (0:Int32) [], RuntimeException
        + FullyQualifiedErrorId : NullArray

    Monday, April 17, 2017 10:41 PM
  • You will have to adjust you "matched" to extract the correct values.

    You would be far better off using "SubString".


    \_(ツ)_/

    Monday, April 17, 2017 10:48 PM
  • Based on this input data:


     PLAZA_ LAN LA LANE_TYP LANE_COM UFM_COUNT CURRENT_VA TYPICAL_VA
     ------ --- -- -------- -------- --------- ---------- ----------
     101320 02  01 COAPM    Xerox         1097 93.2482    99.71
     007620 60  01 ORT      Raytheon       226 76.1062    99.84
     101320 02  01 COAPM    Xerox          967 70.2194    99.6
    

    This script will produce the requested CSV output file.


    Get-Content "D:\TRHMIBS\VPS\RTN\RTNMissingImageAlert1.txt" |
      Select-Object -Skip 2 |
      Select-String '^\s*(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)' |
      ForEach-Object {
        [PSCustomObject] @{
        "PLAZA_"     = $_.Matches[0].Groups[1].Value -as [Int]
        "LAN"        = $_.Matches[0].Groups[2].Value -as [Int]
        "LA"         = $_.Matches[0].Groups[3].Value -as [Int]
        "LANE_TYP"   = $_.Matches[0].Groups[4].Value
        "LANE_COM"   = $_.Matches[0].Groups[5].Value
        "UFM_COUNT"  = $_.Matches[0].Groups[6].Value -as [Int]
        "CURRENT_VA" = $_.Matches[0].Groups[7].Value -as [Double]
        "TYPICAL_VA" = $_.Matches[0].Groups[8].Value -as [Double]
        }
      } | Export-Csv "D:\TRHMIBS\VPS\RTN\newfile.csv" -NoTypeInformation
    


    -- Bill Stewart [Bill_Stewart]



    Tuesday, April 18, 2017 2:32 PM
    Moderator
  • Hi Bill,

    Can you explain what the upper case S and lower case s indicate?

    (\S+)\s

    Thanks,

    Arnab

    Wednesday, April 19, 2017 5:36 PM
  • I also get this error when I try to run this script.

    Wednesday, April 19, 2017 5:48 PM
  • Can you explain what the upper case S and lower case s indicate?

    (\S+)\s

    PS C:\> help about_Regular_Expressions
    


    -- Bill Stewart [Bill_Stewart]

    Wednesday, April 19, 2017 6:31 PM
    Moderator
  • I also get this error when I try to run this script.

    That is not an error. It is unexpected output.

    It is an indication that either 1) the sample data you posted before is not representative of the actual data you are using, or 2) you are not running the script I posted.

    Look carefully at the exact data and script I put in my post. It is an exact copy of what you posted, and the script does exactly what you requested.


    -- Bill Stewart [Bill_Stewart]


    Wednesday, April 19, 2017 6:33 PM
    Moderator
  • Hi Bill,

    Nevermind, I was able to resolve that error. Thanks for all your help and support

    Regards

    Wednesday, April 19, 2017 6:40 PM