none
Trim text in CSV to first 50 characters RRS feed

  • Question

  • Hi,

    Hoping to get a little help with this.

    I have a CSV file which is generated by another script. But i need to make sure that none of the values in the CSV file are greater than 50 characters long. Can someone help me compile a powershell script that reads the contents of the CSV and deletes any excess characters in a  value in the CSV which is greater then 50 characters long.

    Thanks


    Regards,

    Denis Cooper

    MCITP EA - MCT

    Help keep the forums tidy, if this has helped please mark it as an answer

    Blog: http://www.windows-support.co.uk  Twitter:   LinkedIn:

    Friday, August 22, 2014 6:56 AM

Answers

  • Something like the below should work, you will need to change the paths and header to match your CSV file:

    ($imp = import-csv C:\whatever.csv) | % { if ($_.header.length -gt 50) {
    
    $_.header = $_.header.Substring(0,49)
    
    
    
    
    
    
    }
    }
    $imp  | export-csv C:\new.csv -Force -NoTypeInformation
    

    • Marked as answer by Denis Cooper Friday, August 22, 2014 9:19 AM
    Friday, August 22, 2014 8:55 AM

All replies

  • Hoping to get a little help with this.

    Can someone help me compile a powershell script?

    Did you mean "write the script for you" or "help me with the script"? If it is the latter then please post the script you have written so far and describe where you get stuck.
    Friday, August 22, 2014 8:33 AM
  • Something like the below should work, you will need to change the paths and header to match your CSV file:

    ($imp = import-csv C:\whatever.csv) | % { if ($_.header.length -gt 50) {
    
    $_.header = $_.header.Substring(0,49)
    
    
    
    
    
    
    }
    }
    $imp  | export-csv C:\new.csv -Force -NoTypeInformation
    

    • Marked as answer by Denis Cooper Friday, August 22, 2014 9:19 AM
    Friday, August 22, 2014 8:55 AM
  • Amazing that you marked that as a solution when it does nothing at all.  It just reads and write the same exact file.

    Take the solution in smallpieces and analyze what is happening.  What is "header"?


    ¯\_(ツ)_/¯

    Friday, August 22, 2014 12:52 PM
  • It does exactly what he asked for. Replace header with the actual column header from a csv file and it will trim all values underneath to 50 characters if they are longer... I tested it before posting.
    Sunday, August 24, 2014 8:05 PM
  • It does exactly what he asked for. Replace header with the actual column header from a csv file and it will trim all values underneath to 50 characters if they are longer... I tested it before posting.

    What is "header".  He asked how to replace any column that exceeds 50 characters.

    ".... i need to make sure that none of the values in the CSV file are greater than 50 "

    I see no reference to a column called "header"


    ¯\_(ツ)_/¯

    Sunday, August 24, 2014 8:21 PM
  • I see what you mean and it appears i misread slightly but the basic logic is there. "Header" is merely a substitue for the column name, it could be anything.
    Sunday, August 24, 2014 8:32 PM
  • I see what you mean and it appears i misread slightly but the basic logic is there. "Header" is merely a substitue for the column name, it could be anything.

    In case you are interested this is the only way it can be reliable done.

    $csv=Import-Csv $filename 
    $csv |
         ForEach-Object{
              $_.PsObject.Properties|
                   ForEach-Object{
                        if($_.Value.Length -gt 50){
                             Write-Host 'Trimming...' -fore green
                             $_.Value=$_.Value.SubString(0,49)
                        }
                   }
          }
    $csv | Export-Csv $newfile -NoType
    

    Your solution will throw errors on all fields that are less than 50 characters so even if you put in good column names it will not work well.


    ¯\_(ツ)_/¯

    Sunday, August 24, 2014 9:13 PM
  • Are you.sure? I tested it against a csv file containing information extracted from Vsphere. One of the colums contained notes (which i used in place of header), plenty of which were under 50 characters, no errors were thrown.
    Sunday, August 24, 2014 10:11 PM
  • Sorry - I forgot you had the "if" in there.

    The one I posted shows you how to generalize a solution using PowerShell components.  It will work on any CSV file without modification.

    You don't need parens and it would be easier to understand if you formatted it correctly.

    import-csv apps.csv | 
         %{
             if($_.DisplayName.length -gt 50){
                  Write-Host 'Shrinkin' -fore green
                  $_.DisplayName = $_.DisplayName.Substring(0,49)
             }
             $_
          } |
          Export-Csv newfile.csv -NoType
         
    
    

    OR

    $imp=import-csv apps.csv
    
    $imp | 
         %{
             if($_.DisplayName.length -gt 50){
                  Write-Host 'Shrinkin' -fore green
                  $_.DisplayName = $_.DisplayName.Substring(0,49)
             }
          }
    
    $imp | Export-Csv newfile.csv -NoType
         
    
    

    Mixing both methods may make the script harder to understand and may make it prone to edit/update errors.


    ¯\_(ツ)_/¯

    Sunday, August 24, 2014 10:29 PM