locked
Converting txt file to csv RRS feed

  • Question

  • Hi,

    I have a piece of script which I am using to convert a txt file to csv. The script is not working and it does not throw any errors.

    "P,L,Total_UFM,Other" | Set-Content C:\PowerShell\AB.csv
    $inputData = get-content "C:\PowerShell\AB.txt"
    & {
      for ( $row = 0; $row -lt $inputData.Count; $row++ ) {
        if ( $inputData[$row] ) {
          $inputData[$row] | select-string '^(\d+),(\d+),(\d+),(\d+),(\d+)' | foreach-object {
            new-object PSObject -property @{
              "P" = $_.Matches[0].Groups[1].Value
              "L" = $_.Matches[0].Groups[2].Value
              "Total_UFM" = $_.Matches[0].Groups[3].Value
              "Other" = $_.Matches[0].Groups[4].Value
            } | select-object P,L,Total_UFM,Other
          }
         }
       }
     } Add-Content C:\PowerShell\AB.csv
    I have attached the text file. Please advise. I appreciate your assistance on this.

    Tuesday, September 15, 2015 5:01 PM

Answers

  • Get-Content yourfile |
    	ForEach-Object{
    		$p = @{ }
    		$p.P, $p.L, $p.Total_UFM, $p.Other = $_.Split(' ', [system.StringSplitOptions]::RemoveEmptyEntries)
    		[pscustomobject]$p
    	} | Export-Csv yourcsv.csv -notype



    \_(ツ)_/


    • Edited by jrv Tuesday, September 15, 2015 6:00 PM
    • Marked as answer by PowerShell_Novice Tuesday, September 15, 2015 10:09 PM
    Tuesday, September 15, 2015 5:59 PM
  • Get-Content space.txt|
    	ForEach-Object{
    		$p = @{ }
    		$p.P, $p.L, $p.Total_UFM, $p.Other = $_.Split(' ', [system.StringSplitOptions]::RemoveEmptyEntries)
    		New-Object PsObject -Property $p
    	} | 
            Export-Csv yourcsv.csv -notype


    \_(ツ)_/

    Tuesday, September 15, 2015 8:24 PM

All replies

  • No idea what your text file looks like.


    \_(ツ)_/

    Tuesday, September 15, 2015 5:10 PM
  • Sorry about that, I don't see an option to attach it. So I copied the text.

    005403         51              1          0
    003410          5              2          0
    003420          2              3          0
    001841          1              1          0
    005120          9              2          0
    003110          2              9          0
    001821          4              2          0
    003120          7              2          0
    003140          6              3          0

    Tuesday, September 15, 2015 5:16 PM
  • Get-Content yourfile |
    	ForEach-Object{
    		$p = @{ }
    		$p.P, $p.L, $p.Total_UFM, $p.Other = $_.Split(' ', [system.StringSplitOptions]::RemoveEmptyEntries)
    		[pscustomobject]$p
    	} | Export-Csv yourcsv.csv -notype



    \_(ツ)_/


    • Edited by jrv Tuesday, September 15, 2015 6:00 PM
    • Marked as answer by PowerShell_Novice Tuesday, September 15, 2015 10:09 PM
    Tuesday, September 15, 2015 5:59 PM
  • Offtopic (sorry)

    jrv - are you picking these threads up via RSS? The thread listing via the web interface seems to have frozen up as of 3 hours ago (at least for me).

    I've reached out to the forums team, but I figured I'd ask the one person I can find who's actually responding to threads in the last few hours.


    Tuesday, September 15, 2015 6:10 PM
  • Messages are coming in intermittently and an unusual number of "internal errors"


    \_(ツ)_/

    Tuesday, September 15, 2015 6:15 PM
  • Messages are coming in intermittently and an unusual number of "internal errors"


    \_(ツ)_/

    Okay, thanks sir. We'll see what the forums team says when they get back to me.


    Tuesday, September 15, 2015 6:18 PM
  • Hi Jrv,

    I am seeing this on the csv file when I run the code. Please advise. I believe this has to do with the version of powershell I am using.

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

    Get-Content "D:\TRHMIBS\audit_bad_ufm.txt" |
    	 ForEach-Object{
    		$p = @{ }
    		$p.P, $p.L, $p.Total_UFM, $p.Other = $_.Split(' ', [system.StringSplitOptions]::RemoveEmptyEntries)
    		[pscustomobject]$p
    	    }|Export-Csv D:\TRHMIBS\audit_bad_ufm.csv -notype
    

    Tuesday, September 15, 2015 8:09 PM
  • Get-Content space.txt|
    	ForEach-Object{
    		$p = @{ }
    		$p.P, $p.L, $p.Total_UFM, $p.Other = $_.Split(' ', [system.StringSplitOptions]::RemoveEmptyEntries)
    		New-Object PsObject -Property $p
    	} | 
            Export-Csv yourcsv.csv -notype


    \_(ツ)_/

    Tuesday, September 15, 2015 8:24 PM
  • Thank you sir, that worked great. I appreciate your help and support.
    Tuesday, September 15, 2015 10:09 PM
  • You are welcome.

    I wanted to show you and others coming here that, with a little learning, this kind of thing is very easy in PowerShell.

    Using RegEx is a good way but in the case that we have a clear delimiter the "split" method is very useful.  Also using the array to variable capability of PowerShell makes this a cinch.

    $a=1,2,3,4,5

    redistribute to variables

    $p,$q,$r,$s,$t=$a

    This is only one of hundreds of things that PowerShell can do and other systems cannot.


    \_(ツ)_/

    Tuesday, September 15, 2015 10:19 PM
  • Hi Jrv,


    I got this message in the output csv when I tried to run the powershell script System.Object[]". Any ideas's

    "Plaza","Lane","Other","Total_UFM"
    "006220","60","System.Object[]","1"
    "007350","50","System.Object[]","1"

    I believe the issue is, if I have two entries (row) in the input txt file.

    Thanks,

    Arnab

    Monday, September 21, 2015 4:07 PM
  • I have no idea what your scrip looks like.  YOu apparently have an error in how you are using it.


    \_(ツ)_/

    Monday, September 21, 2015 4:15 PM
  • Hi Jrv,

    This is the script I am working on.

    Get-Content "D:\TRHMIBS\audit_bad_ufm.txt" |
    	 ForEach-Object{
    		$p = @{ }
    		$p.Plaza, $p.Lane, $p.Total_UFM, $p.Other = $_.Split(' ', [system.StringSplitOptions]::RemoveEmptyEntries)
    		New-Object PsObject -Property $p
    	    }|Export-Csv D:\TRHMIBS\audit_bad_ufm.csv -notype

    This is what the txt file looks like.

    006220         60              1          1          1          1
    007350         50              1          1          1          1

    This is the output csv file which is created running the powershell script.

    Plaza Lane Other Total_UFM
    6220 60 System.Object[] 1
    7350 50 System.Object[] 1

    Monday, September 21, 2015 6:16 PM
  • You added two columns to the file so now you have to add two columns to the code.


    \_(ツ)_/

    Monday, September 21, 2015 6:26 PM
  • Try:
    Get-Content "D:\TRHMIBS\audit_bad_ufm.txt" |
      ForEach-Object{ ($_.Trim() -split ' +') -join ','} |
        Out-File "D:\TRHMIBS\audit_bad_ufm.csv"


    • Edited by LarryWeiss Monday, September 21, 2015 8:23 PM
    Monday, September 21, 2015 8:22 PM
  • Hi Jrv,

    My bad, Thank you once again.

    Regards,

    Arnab

    Tuesday, September 22, 2015 4:21 PM
  • What do you want the output to look like?

    I have some code for you, but I wanted to make sure it produces the exact output you need.

    The latest example of an input file you provided was:

    006220         60              1          1          1          1
    007350         50              1          1          1          1

    But I also need an example of what the output file will look like for that input file.
    Tuesday, September 22, 2015 5:24 PM