PowerShell remove last column of pipe delimited text file

user_4396493 0
Viewed 84

I have a folder of pipe delimited text files that I need to remove the last column on. I'm not seasoned in PS but I found enough through searches to help. I have two pieces of code. The first creates new text files in my destination path, keeps the pipe delimiter, but doesn't remove the last column. There are 11 columns. Here is that script:

$OutputFolder = "D:\DC_Costing\Vendor Domain\CostUpdate_Development_Load_To_IMS"

ForEach ($File in (Get-ChildItem "D:\DC_Costing\Vendor Domain\CostUpdate_Development_Stage_To_IMS\*.txt")) 
{
(Get-Content $File) | Foreach-Object { $_.split()[0..9] -join '|' } | Out-File $OutputFolder\$($File.Name)
}

Then this second code I tried creates the new text files on my destination path, it DOES get rid of the last column, but it loses the pipe delimiter. Ugh.

$OutputFolder = "D:\DC_Costing\Vendor Domain\CostUpdate_Development_Load_To_IMS"

ForEach ($File in (Get-ChildItem "D:\DC_Costing\Vendor Domain\CostUpdate_Development_Stage_To_IMS\*.txt")) 
{
Import-Csv $File -Header col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11 -Delimiter '|' | 
Foreach-Object {"{0} {1} {2} {3} {4} {5} {6} {7} {8} {9}" -f $_.col1,$_.col2,$_.col3,$_.col4,$_.col5,$_.col6,$_.col7,$_.col8,$_.col9,$_.col10} | Out-File $destination\$($File.Name)
}

I have no clue on what I'm doing wrong. I have no preference in which way I get this done but I need to keep the delimiter and the have the last column removed. Any help would be greatly appreciated.

powershell
2 Answers

In your plain-text processing attempt with Get-Content, you simply need to split each line by | first (.Split('|')), before extracting the fields of interest with a range operation (..) and joining them back with |:

Get-Content $File | 
  Foreach-Object { $_.Split('|')[0..9] -join '|' } |
    Out-File $OutputFolder\$($File.Name)

In your Import-Csv-based attempt, you can take advantage of the fact that it will only read as many columns as you supply column names for, via -Header:

# Pass only 10 column names to -Header
Import-Csv $File -Header (0..9).ForEach({ 'col' + $_ }) -Delimiter '|' | 
  ConvertTo-Csv -Delimiter '|' | # convert back to CSV with delimiter '|'
    Select-Object -Skip 1 |  # skip the header row
      Out-File $destination\$($File.Name)

Note that ConvertTo-Csv, just like Export-Csv by default double-quotes each field in the resulting CSV data / file.

In Windows PowerShell, you cannot avoid this, but in PowerShell (Core) 7+ you can control this behavior with -UseQuotes Never, for instance.

user_453750

You can give this a try, should be more efficient than using Import-Csv, however note, this should always exclude the last column of your files no matter how many columns they have and assuming they're pipe delimited:

$OutputFolder = "D:\DC_Costing\Vendor Domain\CostUpdate_Development_Load_To_IMS"

foreach ($File in (Get-ChildItem "D:\DC_Costing\Vendor Domain\CostUpdate_Development_Stage_To_IMS\*.txt")) {
    [IO.File]::ReadAllLines($File.FullName) | & {
        process{
            -join ($_ -split '(?=\|)' | Select-Object -SkipLast 1)
        }
    } | Set-Content (Join-Path $OutputFolder -ChildPath $File.Name)
}
user_153395440
Related