Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Move Columns in Text File with POWERSHELL

    Hello,

    Newbie Needs Help.

    I want to move columns of text file (pipe delimited) Move 9th Column to 5th.
    Modify / Format 10th column as string of 10 [0000000000] and move to column 6th.
    Maintain the text file structure and output to new file.

    Example::
    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|

    Output::
    10|455|omyselfff (white space)|2378|11|0000007474|abc|212|
    89|356|7saturday(white space)|1256|23|0000008959|def|394|
    99|467|2monday (white space)|9358|94|0000009588|gha|847|

    Any helps will be appreciated.
    Last edited by shakir; 2014-11-09 at 21:34.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Shakir,

    This should do the trick.
    Code:
    $Headers = "A","B","C","D","E","F","G","H","I","J","K" #One letter for each column!
    $FileLocation = "G:\BEKDocs"    #Change to your location!
    $FileFilter = "PSTestFile.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 ---
      $x = import-csv -Path $FSpec.FullName  -Delimiter '|' -Header $Headers
      $x | ForEach-Object {$_.I = $_.I.padleft(10,'0')} 
      $x | Select-Object A,B,C,D,I,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 
    }
    Note: this was developed using this thread post #6 as a base. You should always search other posts for Ideas first, it's a good way to learn.

    Results:
    Code:
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hello,

    Thanks you RG.

    One issue the text file column 3rd contains (name of clients) and its width is 30 charaters,
    the script trims the width, how to maintain that width of 30 characters in 3rd column.

    Else is perfect.
    Thanks.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Shakir,

    Ok, here's the fixed file with a change thanks to the help of Cliff.H from another post in this forum. You really should look at the other posts!
    Code:
    <#+-----------------------------------------------------------------------+
      | Read CSV file, reorder columns, pad one column to 10 spaces in length |
      | using leading zeros.                                                  | 
      |                                                                       |
      | Version      : 1.2                                                    |
      | 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
    
    $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
    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:

    shakir (2014-11-17)

Posting Permissions

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