Text file to Text file String Conversion Using Powershell CSV Objects

Question

I can change the column order of text in a file:

001 AALTON Alan 25 Every Street 
002 BROWN James 101 Browns Road
003 CAMPBELL Colin 57 Camp Avenue

to have the first names listed before surnames using the following:

$Data = Import-CSV C:\test\test6\sqlInsertList.txt -Delimiter ' ' -Header "Col1","Col2","Col3","Col4","Col5","Col6"

# attempt to output with Surname/FirstName (columns 2 & 3) reversed 
[array]$fileStore = $Data | Select-Object Col1,Col3,Col2,Col4,Col5,Col6 | Export-CSV C:\test\test6\newSqlInsertList.txt -append

The output being:

"Col1","Col3","Col2","Col4","Col5","Col6"
"001","Alan","AALTON","25","Every","Street"
"002","James","BROWN","101","Browns","Road"
"003","Colin","CAMPBELL","57","Camp","Avenue"

But if I'm to process this output to a string format I am stuck trying to expand each Colx property like:

$Data = Import-CSV C:\test\test6\sqlInsertList.txt -Delimiter ' ' -Header "Col1","Col2","Col3","Col4","Col5","Col6"

    # attempt to output with Surname/FirstName (columns 2 & 3) reversed 
    [array]$fileStore = $Data | Select-Object Col1,Col3,Col2,Col4,Col5,Col6 
    
    # first attempt worked 
    $test = $fileStore[1] | Select-Object -ExpandProperty Col1
    $test += ' '
    $test += $fileStore[1] | Select-Object -ExpandProperty Col3
    $test += ' '
    $test += $fileStore[1] | Select-Object -ExpandProperty Col2
    $test += ' '
    $test += $fileStore[1] | Select-Object -ExpandProperty Col4
    $test += ' '
    $test += $fileStore[1] | Select-Object -ExpandProperty Col5
    $test += ' '
    $test += $fileStore[1] | Select-Object -ExpandProperty Col6
    $test

To output:

002 James BROWN 101 Browns Road

I can't store the objects Colx in an array to loop through them. So the solution I have so far is cumbersome and simply doesn't seem right.

The solutions seem to be:

Somehow intercept the Export-CSV C:\test\test6\newSqlInsertList.txt -append to remove double quotes & commas
Or find a way to expand all the column properties in a better way than I have shown

I would appreciate if anyone has a suggestion.

Santiago Squarzon · Accepted Answer · 2022-06-11 04:46:26Z

2

Here is an easy way to get what you're looking for, to be honest, I don't understand why would you want to export the output the way you want it instead of a proper Csv.

$headers  = "A","B","C","D","E","F"
$newOrder = "A","C","B","D","E","F"

Import-CSV C:\test\test6\sqlInsertList.txt -Delimiter ' ' -Header $headers | ForEach-Object {
    $(foreach($i in $newOrder) { $_.$i }) -join ' '
} | Set-Content newfile.someextension

To give context on what the code is doing:

The outer ForEach-Object loop is enumerating each object from the collection (the Csv).
The inner foreach loop is getting the property values of each object from the collection (in the desired order).
All the output from the inner loop is being collected with $(...) and after the enumeration is done, all captured output is being joined by a space ' ' with the -join operator.

edited Jun 11, 2022 at 4:46

answered Jun 10, 2022 at 21:05

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

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

2 Comments

Dave Over a year ago

Thank you @Santiago. Success. Yes I take your point re: outputting other than CSV file type. But my priority is understanding how your example works. I take it that ForEach-Object {$(foreach($i in $newOrder) { $_.$i }) -join ' '} The object being a group of space delimited text arranged in columns. Where each member of a new order is assigned as a property of the original object { $_.$i } Same text but new order. That's the best I can do for now.

Santiago Squarzon Over a year ago

@Dave happy to help. I added some explanation to the code, hope is easy to follow

Collectives™ on Stack Overflow

Text file to Text file String Conversion Using Powershell CSV Objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related