Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post

    Powershell Target Column D - Output file CSV Export

    Hello folks,

    good friday to all.

    I use ps script below to extract lines from text files that contain the word Number to a CSV file.



    Code:
     $oTxtFiles = "C:\Users\PB\Desktop\Text\*.txt"
    
     $OutputFile ="C:\Users\PB\Desktop\txt.csv"        
    
    
     $Pat1 = 'Number'
    
    
        Remove-Item "$OutputFile"
    
            Get-ChildItem -Path      "$oTxtFiles" |
    
        Get-Content  | Select-String -Pattern $Pat1 -AllMatches >> "$OutputFile"
    Is there a way for me to extract it to a specific column
    Ive looked at the Export-CSV cmdlet and cant figure it out.

    At the moment it is extracted to column A.

    How can I target Column D,

    thanks as always for any advice

    pb

  2. #2
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,572
    Thanks
    5
    Thanked 1,057 Times in 926 Posts
    You should be able to use Export-CSV by outputting a comma separated list such as

    get-process wmiprvse | select-object basePriority,ID,SessionID,WorkingSet | export-csv -path data.CSV

    This outputs four objects from the get-process directive. Column a is basePriority, b is ID, c is SessionId, d is WorkingSet.

    You want three blank objects.
    Joe

  3. #3
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post
    Hi Joe,

    thanks for your quick reply.

    Pardon my ignorance but where do I put this line as below underneath,


    Get-Content | Select-String -Pattern $Pat1 -AllMatches >> "$OutputFile"

    get-process wmiprvse | select-object basePriority,ID,SessionID,WorkingSet | export-csv -path data.CSV

    thanks

    pb

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    PB,

    Here's some code you should be able to adjust to your needs.
    Code:
    #Version: 1.2
    
    $Headers = "A","B","C","D","E"        #One letter for each column!
    $FileLocation = "G:\BEKDocs\Scripts"  #Change to your location!
    $FileFilter = "overall*.csv"          #Change to your filename filter
    
    $FileList = Get-ChildItem $FileLocation -Filter $FileFilter 
    
    ForEach ($FSpec in $FileList) {
      #--- Load File as CSV and add Headers to allow Sorting ---
      (import-csv -Path $FSpec.FullName  -Delimiter '|' -Header $Headers) | `
      Sort-Object C | `
      Export-csv -Path $FSpec.FullName -Delimiter '|' -Force -NoTypeInformation
    
      # Reload File as Text to delete Headers and quotes around values
      # that were added by the CSV processing!
    
      $a = (get-content $FSpec.FullName)
      $a[1..($a.count -1)] -replace '"' | Set-Content $FSpec.FullName
    }
    Here's another one:
    Code:
    <#+-----------------------------------------------------------------------+
      | Read CSV file, reorder columns, pad one column to 10 spaces in length |
      | using leading zeros.                                                  | 
      |                                                                       |
      | Version      : 1.3                                                    |
      | Programmed by: RetiredGeek of WSL                                     |
      | Date         : 11 Nov 2014                                            |
      +-----------------------------------------------------------------------+
    #>
    
    $Headers = "A","B","C","D","E","F","G","H","I","J","K" #1 letter per column!
    $FileLocation = "G:\BEKDocs"    #Change to your location!
    $FileFilter = "PSTestFile.csv"  #Change to your filename filter
    
    # ---- Test code for regenerating the test file       -----
    $FSpec = "$FileLocation\$FileFilter"
    $y = Get-Content "$FileLocation\Base-$FileFilter"
    Set-Content $FSpec $y
    # ---- End of Test code for regenerating the test file -----
    
    $FileList = Get-ChildItem $FileLocation -Filter $FileFilter 
    
    ForEach ($FSpec in $FileList) {
      #--- Load File as CSV and add Headers to allow Sorting ---
      $x = import-csv -Path $FSpec.FullName  -Delimiter '|' -Header $Headers
      $x | Select-Object A,B,@{label='C';expression={$_.C.PadRight(30,' ')}},D, `
           @{label='I';expression={$_.I.PadLeft(10,'0')}},J,E,F,G,H,K | `
      Export-csv -Path $FSpec.FullName -Delimiter '|' -Force -NoTypeInformation
    
      # Reload File as Text to delete Headers and quotes around values
      # that were added by the CSV processing!
    
      $a = (get-content $FSpec.FullName)
      $a =  $a[1..($a.count - 1)]    #--- Get Rid of Header ---
      $Final = $a.replace('"',$null) #--- Get Rid of double quotes  ---
      Set-Content $FSpec.FullName $Final 
    }
    
    <#--------------- Test File: ---------------------------------------------
    10|455|omyselfff (white space)|2378|32|zzzzzzzzzz|abc|212|11|7474|
    89|356|7saturday(white space)|1256|44|zzzzzzzzzz|def|394|23|8959|
    99|467|2monday (white space)|9358|66|zzzzzzzzzz|gha|847|94|9588|
    
      -------------  Result File:  --------------------------------------------
    10|455|omyselfff (white space)       |2378|0000000011|7474|32|zzzzzzzzzz|abc|212|
    89|356|7saturday(white space)        |1256|0000000023|8959|44|zzzzzzzzzz|def|394|
    99|467|2monday (white space)         |9358|0000000094|9588|66|zzzzzzzzzz|gha|847|
    #>
    HTH
    Last edited by RetiredGeek; 2016-06-03 at 17:21.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    pb89 (2016-06-03)

  6. #5
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post
    Thanks RG,

    let me go away and digest it all.
    I'll post back - should I not be able to make it work.

    I'm not experienced in csv matters - still working with basic text files

    cheers
    pb

  7. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 978 Times in 908 Posts
    You could give us a sample of the input and output data?

    cheers, Paul

  8. #7
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post
    Hi Paul,

    I have a folder full of text files.

    From Each Text file I am extracting the Number Line - outputting to csv.
    It works great
    I wanted to extract it to Column D instead of A

    -------------SAMPLE .txt file

    Lorem ipsum dolor sit amet,
    Number: 839723
    consectetur adipiscing elit,

    sed do eiusmod tempor

    incididunt ut labore et dolore
    ---------------------------

    pb

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    PB,

    Just add these lines at the end of your working script.
    Code:
      $a = (get-content $OutputFile)
      $a -replace 'Number', ',,,Number' | Set-Content $OutputFile
    Output file from your existing code:
    pbBefore.PNG

    Output after running the two lines above:
    pbAfter.PNG

    File loaded into Excel:
    pbExcel.PNG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    pb89 (2016-06-04)

  11. #9
    Star Lounger
    Join Date
    Jan 2016
    Posts
    77
    Thanks
    18
    Thanked 1 Time in 1 Post

    Smile

    Hello RG,

    wow thats unbelievable and you made me go through 2 scripts he he


    I can't believe 2 lines did it

    Well you can't mess with a tech ninja,

    ahh thanks ever so much

    The other scripts i have dissected and tested them with my broken code - they are licking their wounds -
    I know they will come in handy when i get to that level.

    I can target any column now! That's really valuable!


    Have a great Saturday!

    much appreciation for your folks talents especially RG

    pb

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    PB,

    I was trying to lead you to the solution rather than providing it.

    If you look at those scripts again you'll see the -replace operator in both and the first one contains the two lines I used with some minor changes and a little thinking about .csv file structure.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •