powershell script to change column position in text file

Question

Do you know how to use powershell to move column DocumentNo to last column ?

| D|Ref.Doc.  |Row|DocumentNo     |CoCd|Pstng Date
| W|5007534739|  1|65713191       |STCD|01/17/2016
| W|5007534739|  1|65713191       |STCD|01/17/2016
| W|5007534739|  1|65713191       |STCD|01/17/2016

Expected output

| D|Ref.Doc.  |Row|CoCd|Pstng Date|DocumentNo       
| W|5007534739|  1|STCD|01/17/2016|65713191       
| W|5007534739|  1|STCD|01/17/2016|65713191       
| W|5007534739|  1|STCD|01/17/2016|65713191

Here the command that I've tried

(get-content $file -ReadCount 0) |
foreach  {
'{0}|{1}|{2}|{3}|{5}|{6}|{4}' -f $_.split('|')
} | Set-Content $file2

The code works properly, but in case DocumentNo containt a pipe as data, how to handle it ?

How to implement the command in power script ? How actually it works ? thanks — user664481
– user664481, Commented Feb 20, 2016 at 11:21
I've tried something, but in case pipe is a data instead of delimiter, how can we handle this ? — user664481
– user664481, Commented Feb 20, 2016 at 11:52
If there is a leading pipe why not just hardcode it in? A "proper" CSV like file would not use the delimeter as data. — Matt
– Matt, Commented Feb 20, 2016 at 14:17
% {$Match=[System.Text.RegularExpressions.Match]::Empty} {if(-not $Match.Success) {$Match=[Regex]::Match($_,'\|DocumentNo\s*(?=\|)')} $_.Remove($Match.Index,$Match.Length) + $_.Substring($Match.Index,$Match.Length)} — user4003407
– user4003407, Commented Feb 20, 2016 at 14:39
I tired to understand the code but failed, would you please share what it did ? — user664481
– user664481, Commented Feb 20, 2016 at 15:08

Frode F. · Accepted Answer · 2016-02-21 08:56:10Z

2

A delimeter is no good if it's used inside the data itself as it breaks the csv. I see that the data is fixed width (though the length and index may change from file to file), so I would take a different apporach.

Find the location for the DocumentNo-column. You can use regex for this (https://regex101.com/r/pH2oL9/1). I use [Regex]::Match() because it returns index (start position) and length (number of characters) of a match (the column).
Create a regex that finds the content at the same position and length on every line. It makes groups for "before", "content", "after" by counting "any character" until the start position I've decided, then the length of the column, then "until end of line". I use subexpressions $() in the regex to insert the index and length from step 2 since they might not be the same for every file.
Use -replace with the generated regex to modify the text on each line (since $text is an array). -replace finds the groups, and by using $1$3$2 I can say which order I want the groups inserted in the result. (https://regex101.com/r/vE6vO9/1)

Solution:

#Sample text
$text = Get-Content .\Test.txt

#Analyze header (find DocumentNo placement in fixed-width file) and create regex
$regex = [regex]::Match($text[0], '\|DocumentNo\s+') | ForEach-Object { "^(.{$($_.Index)})(.{$($_.Length)})(.*)$" }

#Modify text
$text -replace $regex, '$1$3$2' | Set-Content .\TestOut.txt

TestOut.txt

| D|Ref.Doc.  |Row|CoCd|Pstng Date|DocumentNo     
| W|5007534739|  1|STCD|01/17/2016|65713191       
| W|5007534739|  1|STCD|01/17/2016|65713191       
| W|5007534739|  1|STCD|01/17/2016|65713191

You might want to trim the trailing whitespaces. Use Trim() for this:

$text -replace $regex, '$1$3$2' | ForEach-Object { $_.Trim() } | Set-Content .\TestOut.txt

edited Feb 21, 2016 at 8:56

answered Feb 20, 2016 at 17:48

Frode F.

55.4k9 gold badges104 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user664481 Over a year ago

Excellent. Thanks a lot. Appreciate if you could explain how the analyze header works and how it use in modify test.

Frode F. Over a year ago

I've added a description and regex101 links which will explain the regexes.

Harald F. · Accepted Answer · 2016-02-20 12:38:57Z

2

You could do the following:

$delimiter = "|"
$data = Get-Content "c:\tmp\test.csv";
$newCsv= ($data|Foreach-object { ($_ -split $delimiter)[@(0..3;5;6;4)] -join  $delimiter})

# Set the new-ordered column content to the new-file

$newCsv|Set-Content C:\tmp\test2.csv

If you want all of this into one line you can do the following:

Get-Content "c:\tmp\test.csv"|Foreach-object { ($_ -split '|')[@(0..3;5;6;4)] -join '|'}|Set-Content C:\tmp\test2.csv

Note that you cannot use set-content if you have get-content as a part of the pipeline, because the file will be open and cannot replace it's content as a part of the stream.

You'd have to use either the first approach (read the content first, then pass it to the stream) or set the content into a different file.

answered Feb 20, 2016 at 12:38

Harald F.

4,8431 gold badge29 silver badges30 bronze badges

1 Comment

user664481 Over a year ago

Thanks. I've tried and it works but I've case where pipe delimiter can be a data instead of delimiter.

Collectives™ on Stack Overflow

powershell script to change column position in text file

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related