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:

1. Somehow intercept the Export-CSV C:\test\test6\newSqlInsertList.txt -append to remove double quotes & commas
2. 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.

CodePudding user response：

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:

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