locked
Convert txt file to csv file. RRS feed

  • Question

  • Hi,

    I have a txt file which I need to read, convert to a csv file and take certain actions. I am running into issue converting the txt file to a csv file. I have attached the txt file which I am working on.

    "A,B,C" | Set-Content C:\TEMP.csv
    Get-Content C:\TEMP.txt | where {$_ -ne ""} > C:\temp1.txt
    foreach ($s1 in Get-Content C:\temp1.txt | Select-String "1")
    {
        $s2=Get-Content C:\temp1.txt
        $s3=$s2[0]+" "+$s2[1]+","+$s2[2]
        $s3 | Add-Content C:\TEMP.csv
    }

    Text File

    A             B     C

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

    9900 25S          1  

    99452 65S         29 

    Thanks in advance.

    Wednesday, June 17, 2015 4:02 PM

Answers

  • If you are using version 2 which is now obsolete then that is what will happen so replace the output line with this:

             New-Object PsObject -Property @{A=$a;B=$b;C=$c}


    \_(ツ)_/

    Thursday, June 18, 2015 4:46 PM

All replies

  • Exactly what does you text file look like. Open in notepad and copy first few lines.

    $file=Get-Content C:\TEMP.txt | where {$_ -ne ""} 
    1..$file.Count -1 | 
         ForEach-Object{
             $a=$_.Split(' ')
            [psobject]@{A=$a;B=$b;C=$c}
        } |
        Export-Csv temp.csv -NoType
    
    


    \_(ツ)_/


    • Edited by jrv Wednesday, June 17, 2015 4:24 PM
    Wednesday, June 17, 2015 4:24 PM
  • Hi,

    This is how the file looks like, where A, B and C are the headers.

         A B  C                                                       
    ---------- --- ----------                                                       
          1200 22S          1                                                       
          6110 02D          1                                                       
          6120 05M          1 

    Thanks,

    Arnab

    Wednesday, June 17, 2015 6:16 PM
  • Hi,

    This is how the file looks like, where A, B and C are the headers.

         A B  C                                                       
    ---------- --- ----------                                                       
          1200 22S          1                                                       
          6110 02D          1                                                       
          6120 05M          1 

    Thanks,

    Arnab

    Is that what it looks like when you open it in notepad?


    \_(ツ)_/

    Wednesday, June 17, 2015 6:25 PM
  • That is correct.
    Wednesday, June 17, 2015 6:39 PM
  • Change this line: 1..$file.Count -1 |

    to this: 2..$file.Count -1 |


    \_(ツ)_/

    Wednesday, June 17, 2015 6:54 PM
  • $file=Get-Content C:\LEGACY\Legacy_Image1.txt | where {$_ -ne ""} 
    2..$file.Count -1 | 
         ForEach-Object{
             $a=$_.Split(' ')
            [psobject]@{PLAZA=$a;LAN=$b;PEND_IMGS=$c}
        } |
        Export-Csv C:\LEGACY\temp.csv -NoType

    I updated the code and I get this error. 

    Method invocation failed because [System.Object[]] doesn't contain a method named 'op_Subtraction'.
    At line:2 char:17
    + 2..$file.Count - <<<< 1 | 
        + CategoryInfo          : InvalidOperation: (op_Subtraction:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Wednesday, June 17, 2015 7:11 PM
  • Sorry:

    2..($file.Count -1)|


    \_(ツ)_/

    Wednesday, June 17, 2015 8:36 PM
  • The csv got created, however I don't see the desired output. 

    "IsReadOnly","IsFixedSize","IsSynchronized","Keys","Values","SyncRoot","Count"
    "False","False","False","System.Collections.Hashtable+KeyCollection","System.Collections.Hashtable+ValueCollection","System.Object","3"
    "False","False","False","System.Collections.Hashtable+KeyCollection","System.Collections.Hashtable+ValueCollection","System.Object","3"

    Wednesday, June 17, 2015 9:57 PM
  • Oops.

    Change this:

     [psobject]@{A=$a;B=$b;C=$c}

    To this:

     [pscustomobject]@{A=$a;B=$b;C=$c}


    \_(ツ)_/

    Wednesday, June 17, 2015 10:29 PM
  • Got some errors again. Not sure what the issue is. I just changed the column names,

    $file=Get-Content C:\LEGACY\Legacy_Image.txt | where {$_ -ne ""} 
    2..($file.Count -1) | 
         ForEach-Object{
             $Plaza=$_.Split(' ')
            [pscustomobject]@{PLAZA=$Plaza;LAN=$LAN;PEND_IMGS=$PEND_IMGS}
        } |
        Export-Csv C:\LEGACY\temp.csv -NoType

    Got this error.

    Method invocation failed because [System.Int32] doesn't contain a method named 'Split'.
    At line:4 char:21
    +          $a=$_.Split <<<< (' ')
        + CategoryInfo          : InvalidOperation: (Split:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound
     

    Wednesday, June 17, 2015 10:43 PM
  • Why did you change it?


    \_(ツ)_/

    Wednesday, June 17, 2015 10:49 PM
  • Hi jrv,

    I updated the code to use the same column headers (A, B, C) and it's still the same. I get the same error. The txt file is generated from a SQL query.

    A   B   C                                                       
    ---------- --- ----------                                                       
          1200 22S          1                                                       
          6110 02D          1                                                       
          6120 05M          1                                                       
          6121 07D          1   

    10 rows selected.

    $file=Get-Content C:\LEGACY\Legacy_Image.txt | where {$_ -ne ""} 
    2..($file.Count -1) | 
         ForEach-Object{
             $a=$_.Split('')
            [pscustomobject]@{A=$a;B=$b;C=$c}
        } |
        Export-Csv C:\LEGACY\temp.csv -NoType

    Method invocation failed because [System.Int32] doesn't contain a method named 'Split'.
    At line:4 char:21
    +          $a=$_.Split <<<< ('')
        + CategoryInfo          : InvalidOperation: (Split:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Thursday, June 18, 2015 12:45 PM
  • Hi,

    replace $_.Split('')

    with (file.item($_)).split('')


    This post is provided AS IS with no warranties or guarantees, and confers no rights.
    ~~~
    Questo post non fornisce garanzie e non conferisce diritti

    Thursday, June 18, 2015 12:58 PM
  • Hi,

    replace $_.Split('')

    with (file.item($_)).split('')


    This post is provided AS IS with no warranties or guarantees, and confers no rights.
    ~~~
    Questo post non fornisce garanzie e non conferisce diritti

    Absolutely wrong.  It is in a pipeline sp do this:

    $file=Get-Content C:\LEGACY\Legacy_Image.txt | where {$_ -ne ""} 
    2..($file.Count -1) | 
         ForEach-Object{
             $a=$file[$_].Split('')
            [pscustomobject]@{A=$a;B=$b;C=$c}
        } |
        Export-Csv C:\LEGACY\temp.csv -NoType
    


    \_(ツ)_/


    • Edited by jrv Thursday, June 18, 2015 1:46 PM
    Thursday, June 18, 2015 1:45 PM
  • Hi,

    replace $_.Split('')

    with (file.item($_)).split('')



    Absolutely wrong.  It is in a pipeline sp do this:

     

    Oh, really?! I might have forgotten the dollar sign but it works.

    $file=Get-Content C:\scripts\credentials.csv
    2..($file.Count -1) | 
         ForEach-Object{
            $a=(($file.item($_)).split(','))
            $a[0];$a[1];$a[2];
    		write-host "+++++++++++++++"
    		
        } 
        


    Input file:

    FieldA1,fieldA2,fieldA3
    FieldB1,fieldB2,fieldB3
    FieldC1,fieldC2,fieldC3
    FieldD1,fieldD2,fieldD3
    FieldE1,fieldE2,fieldE3
    FieldF1,fieldF2,fieldF3

    Output:

    PS C:\scripts> .\jrv.ps1
    FieldC1
    fieldC2
    fieldC3
    +++++++++++++++
    FieldD1
    fieldD2
    fieldD3
    +++++++++++++++
    FieldE1
    fieldE2
    fieldE3
    +++++++++++++++
    FieldF1
    fieldF2
    fieldF3
    +++++++++++++++


    This post is provided AS IS with no warranties or guarantees, and confers no rights.
    ~~~
    Questo post non fornisce garanzie e non conferisce diritti

    Thursday, June 18, 2015 2:05 PM
  • Still doesn't work. Getting this message :-(

    "IsReadOnly","IsFixedSize","IsSynchronized","Keys","Values","SyncRoot","Count"
    "False","False","False","System.Collections.Hashtable+KeyCollection","System.Collections.Hashtable+ValueCollection","System.Object","3"

    $file=Get-Content C:\LEGACY\Legacy_Image.txt | where {$_ -ne ""} 
    2..($file.Count -1) | 
         ForEach-Object{
            $a=$file[$_].Split('')
            [pscustomobject]@{A=$a;B=$b;C=$c}
        } |
        Export-Csv C:\LEGACY\temp.csv -NoType

    Thursday, June 18, 2015 2:17 PM
  • Please post the whole error message.

    $file=Get-Content C:\LEGACY\Legacy_Image.txt | where {$_ -ne ""} 
     2..($file.Count -1) | 
          ForEach-Object{
             $a,$b,$c-=$file[$_].Split(' ')
             [pscustomobject]@{A=$a;B=$b;C=$c}
         } |
         Export-Csv C:\LEGACY\temp.csv -NoType 

    This has a space which you keep loosing:

    $a,$b,$c-=$file[$_].Split(' ') <---- space character not blank.


    \_(ツ)_/

    Thursday, June 18, 2015 2:20 PM
  • Thanks jrv, I am getting this error.

    $file=Get-Content D:\LEGACY\Legacy_Image.txt | where {$_ -ne ""} 
    2..($file.Count -1) | 
         ForEach-Object{
            $a,$b,$c-=$file[$_].Split(' ')
            [pscustomobject]@{A=$a;B=$b;C=$c}
        } |
        Export-Csv D:\LEGACY\temp.csv -NoType

    You can only use the '=' operator when assigning to a list of variables.
    At D:\LEGACY\Script.ps1:4 char:19
    +         $a,$b,$c-= <<<< $file[$_].Split(' ')
        + CategoryInfo          : ParserError: (:) [], ParseException
        + FullyQualifiedErrorId : EqualsNotSupported

    Thursday, June 18, 2015 3:07 PM
  • There is an extra dash in this line:

     $a,$b,$c-=$file[$_].Split(' ')

    Should be:

    $a,$b,$c=$file[$_].Split(' ')


    \_(ツ)_/

    Thursday, June 18, 2015 3:16 PM
  • This should work:

    $file=Get-Content  D:\LEGACY\Legacy_Image.txt | where {$_ -ne ""} 
    $CsvFile="D:\LEGACY\temp.csv"
    if (Test-Path $CsvFile) {Rename-Item $CsvFile $CsvFile".bak" -Force}
    $file.item(0).trim(' ') -replace '\s+', "," |add-content $CsvFile 
    2..($file.Count -1) | 
         ForEach-Object{ ($file.item($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 	


    This post is provided AS IS with no warranties or guarantees, and confers no rights.
    ~~~
    Questo post non fornisce garanzie e non conferisce diritti

    Thursday, June 18, 2015 3:34 PM
  • I had tried that earlier and that did not work.
    Thursday, June 18, 2015 3:50 PM
  • Got these errors.

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:4 char:11
    + $file.item <<<< (0).trim(' ') -replace '\s+', "," |add-content $CsvFile 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

    Method invocation failed because [System.Object[]] doesn't contain a method named 'item'.
    At line:6 char:33
    +      ForEach-Object{ ($file.item <<<< ($_)).trim(' ') -replace '\s+', ","  | add-content $CsvFile } 
        + CategoryInfo          : InvalidOperation: (item:String) [], RuntimeException
        + FullyQualifiedErrorId : MethodNotFound

             
    Thursday, June 18, 2015 3:56 PM
  • I can run my script on the file you posted and it works.  I apologize for the bad copy I posted but it is correct.

    Your file mayy7 not be space delimited.

    There is no such method as Item on a string or array object.


    \_(ツ)_/

    Thursday, June 18, 2015 4:03 PM
  • earlier? I did it minutes ago, your call . I also tested it.

    This post is provided AS IS with no warranties or guarantees, and confers no rights.
    ~~~
    Questo post non fornisce garanzie e non conferisce diritti

    Thursday, June 18, 2015 4:06 PM
  • Hi jrv,

    That's correct the txt file is not space delimited and that's where the problem is.

    Thanks

    Thursday, June 18, 2015 4:08 PM
  • I added a trim incase you have multiple spaces at the end of your lines.

    $fname='C:\scripts\file.txt '
    $file=Get-Content $fname | where {$_ -ne ""} 
     2..($file.Count -1) | 
          ForEach-Object{
             $a,$b,$c=$file[$_].Trim().Split(' ',[stringsplitoptions]::RemoveEmptyEntries)
    [pscustomobject]@{A=$a;B=$b;C=$c} }

    Here is the exact test file:

    A   B   C
     ---------- --- ----------                                                      
           1200 22S          1                                                       
           6110 02D          1                                                       
           6120 05M          1                                                       
           6121 07D          1   
    

    Note that there are many spaces in the file.


    \_(ツ)_/

    Thursday, June 18, 2015 4:11 PM
  • Here is the results


    \_(ツ)_/

    Thursday, June 18, 2015 4:13 PM
  • You can name the columns anything you want.

             [pscustomobject]@{Tom=$a;Dick=$b;Harry=$c}

    The labels are the columns the variables the values.

    It is important that you understand hwo this works if you are going to try and modify it.


    \_(ツ)_/

    Thursday, June 18, 2015 4:14 PM
  • Hi jrv,

    That's correct the txt file is not space delimited and that's where the problem is.

    Thanks

    No it has to be space delimited if it displays the way it does., It just has too many spaces because of the numeric right justification and the end of the lines have many spaces.


    \_(ツ)_/

    Thursday, June 18, 2015 4:17 PM
  • This is the output, I get. Is this to do with the version of pwershell I am running. I am not sure what's wrong.

    Thursday, June 18, 2015 4:28 PM
  • If you are using version 2 which is now obsolete then that is what will happen so replace the output line with this:

             New-Object PsObject -Property @{A=$a;B=$b;C=$c}


    \_(ツ)_/

    Thursday, June 18, 2015 4:46 PM
  • Hi jrv,

    That worked great. Thank you very much for your help. I had one last question. 

    Is there a way, I can remove the last line from this file "x rows selected"

    I am trying to run this cmd at the end of the code. What  I found out, is if I have the line "x rows selected", this cmd does not return the desired results.

    PS D:\LEGACY> Import-Csv 'D:\Legacy\newfile.csv' | Where-Object {[int]$_.C -gt 5} | Export-Csv -Path D:\Legacy\newfile_1.csv -NoTypeInformation

    Thursday, June 18, 2015 8:03 PM
  • I asked you for the file and you gave only part of the story.  Now we have to go back and redesign.

    If you were building a skyscraper we would be tearing it down and starting again.  Not a good thing.


    \_(ツ)_/

    Thursday, June 18, 2015 8:23 PM
  • Sorry about that, Column C for the last line will always default to selected. I was hoping that when I do my comparison Where-Object {[int]$_.C -gt 5}, powershell will automatically ignore that, however that did not work.

    I tried Where-Object {$_.C -gt 5} and that did not work either. No worries.

    "x rows selected"

    Thursday, June 18, 2015 8:36 PM
  • No idea what you are talking about.  You need to get rid of the lines that are not consistent.

    \_(ツ)_/

    Thursday, June 18, 2015 9:16 PM