Migrating scripts from Python to Powershell - looking for rstrip equivalent

Question

Hello, I have a pipe delimited data file that I need to read and strip all fields (if necessary) to meet SQL column restraints (for import). In Python, I use rstrip and it works even when a column, for example - Company Name(30) or FirstName(16), LastName(16), etc is null. I could use substring but a number of "IF" tests would be needed for each variable. Any better ideas? Here's an example of my python code:

    print(fields[0][:10].rstrip() + "|" +
          fields[1][:30].rstrip() + "|" +
          fields[2][:16].rstrip() + "|" +
          fields[3][:2].rstrip() + "|" +
          fields[4][:16].rstrip() + "|" +

Answer 1

Sounds like you want to use .trim(). If it's an empty string '' there won't be an error. If it's really $null, you can enable an experimental feature in powershell 7 like this:

Enable-ExperimentalFeature PSNullConditionalOperators

Restart powershell and then if $a is null, there's still no error. The same goes for any method.

${a}?.trim()

Answer 2

I'll toss in another example (although it's nice to have a built-in language feature to something!):

# just some test data
$l =    'field[0]9012345|field[1]90123456789012345678901234567890123456789|field[2]901|field[3]90123|field[4]9012345',
        'field[0]9012345||field[2]901|field[3]90123|field[4]9012345'

$ColumnWidths = 10,30,16,2,16
$l |
    ForEach-Object{
        $f = $_ -split '\|'
        if ($f.length -ne $ColumnWidths.length){Throw "Mismatched columns"}
        for ($cnt=0; $cnt -lt $f.length; $cnt++){
            if ($f[$cnt].length -gt $ColumnWidths[$cnt]){
                $f[$cnt] = $f[$cnt].substring(0,$ColumnWidths[$cnt])  # replace with shortened string
            }
            $f[$cnt] = $f[$cnt].TrimEnd()                               # trim trailing spaces
        }
        $f -join '|'
    } | Out-File c:\junk\Slice.txt

Answer 3

Looks like this could be a solution:

$oldCSV = "c:\Test\OLD.csv"
$newCSV = "c:\Test\New.csv" 

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

Stolen from here:
limit-csv-column-length-using-powershell

OLD CSV:

F0000001011|My Company Name is really Long, Inc|Jones
F0000001022|My Company Name is also really Long, Inc|Smith
F0000001033||NoCompanyNameJustLName

New CSV:

F000000101|My Company Name is really Long|Jones
F000000102|My Company Name is also really|Smith
F000000103||NoCompanyNameJus

Answer 4

With @js2010 suggestion of using Trim(), this was the solution:

Import-Csv -Delimiter "|" -Path $oldCSV -Header "1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21" | ForEach-Object {   
    "{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}|{8}|{9}|{10}|{11}|{12}|{13}|{14}|{15}|{16}|{17}|{18}|{19}|{20}" -f  
    ($_.1).Substring(0,[Math]::Min(10,($_.1).Length)).Trim(),  
    ($_.2).Substring(0,[Math]::Min(30,($_.2).Length)).Trim(),  
   .....  
   .....