user58602 user58602 - 4 months ago 26
PowerShell Question

Limit csv column length using PowerShell

I'm trying to adjust a tab-delimited textfile without headers using PowerShell and have it write the output to the same tab-delimited textfile. Currently source data is as follows;


  • AAA \t BBBBBB \t CCCCCCCCCCC

  • AAAA \t BBBB \t AAAABBBBBCCCCCCC



Now column number three should be limited to the first 5 characters only, making the output look as follows;


  • AAA \t BBBBBB \t CCCCC

  • AAAA \t BBBB \t AAAAB



How can I accomplish this?

Answer

The following code will import a CSV file using Import-Csv and then loop through all the lines, creating a substring (5 in length) of the 3rd column before writing the line into a new CSV file.

$oldCSV = "T:\OLD.csv"
$newCSV = "T:\New.csv" 

Import-Csv -Delimiter "`t" -Path $oldCSV -Header "1","2","3" | ForEach-Object { 
    "{0}`t{1}`t{2}" -f $_.1,$_.2,($_.3).Substring(0,[Math]::Min(5,($_.3).Length)) >> $newCSV 
}