Migrating scripts from Python to Powershell - looking for rstrip equivalent

Question

question

SethWH asked Aug 25, '20 RichMatheisen-8856 edited Aug 26, '20

Migrating scripts from Python to Powershell - looking for rstrip equivalent

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() + "|" +

windows-server-powershell

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2020-08-25T13:38:24.52Z

js2010 answered Aug 25, '20 SethWH commented Aug 25, '20

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()

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SethWH · Aug 25, 2020 at 03:06 PM

I'll have to take a look at PSNullConditionalOperators. I could see that being useful in the future. I couldn't find any documentation on trim doing anything other than trimming white space or characters that you specify. In this case I need to trim everything after a certain length if necessary on the column length constraint.

0 ·

SethWH SethWH · Aug 25, 2020 at 06:38 PM

I was wrong. Looking at the original vendor file, rstrip is removing extra spacing on the right of the fields so your answer is correct. The print function is merely printing the character length of the field constraints.

0 ·

Answer 2 · 2020-08-25T06:27:06.843Z

SethWH answered Aug 25, '20 SethWH commented Aug 25, '20

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SethWH · Aug 25, 2020 at 03:13 PM

I need to research this line but it seems to do the trick:

 ($_.1).Substring(0,[Math]::Min(10,($_.1).Length))

0 ·

Answer 3 · 2020-08-25T18:40:25.32Z

SethWH answered Aug 25, '20

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(),
    .....
    .....

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2020-08-25T18:56:50.017Z

RichMatheisen-8856 answered Aug 25, '20 RichMatheisen-8856 edited Aug 26, '20

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

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SethWH · Aug 25, 2020 at 09:47 PM

Thank you, I'll take a look at this strategy as well.

0 ·

RichMatheisen-8856 SethWH · Aug 26, 2020 at 02:52 PM

You can even eliminate the additional indexing by eliminating line #14 and changing line #8 to $f = ($_.split(\|).TrimEnd().

0 ·

SethWH RichMatheisen-8856 · Aug 26, 2020 at 04:35 PM

I had to use $f = ($_ -split '\|').TrimEnd() but your example worked great with what seems to be less code. Thank you - Seth

0 ·

Show more comments

question