Removing extra white spaces from .csv and replace with "|"

I typoed an extra "/" at the end of the code

perl -i -pe 's/\s+/,/g' yourfilename.csv
                                              

1:

Ah....I didnt see the spaces between the first name and the last name. So that simple one-liner goes doesnt work. Also, this is only one column that we are talking about here and not the whole file. The first column can be obtained using Text::CSV.

Is there always only one space between first name and last name, and always more than one space between fields?? Is there any special characters there??
In other words, how do we differentiate betwen the space in the Name, and the space between the fields.

You can use this:
    perl -i.bak -pe 's/\s{3,}/\|/g'  <filename>

Replace <filename> with the actual filename you want changed.  This will create a backup file, with the same name as <filename> but with .bak added.
The 3 means at least three spaces will be considered the seperator.  This works fine on your example, but if your actual data is different, you can change the 3 to another number.

One more thing..... If you are on windows, replace the single quotes with double quotes:
    perl -i.bak -pe "s/\s{3,}/\|/g" <filename>

>>I am trying to remove the white spaces but when I use the trim feature in the DTS package I lose some data<<
Post your code.  There is no way the Trim function can truncate non-blank data.

Is the perl in the code in reference to the language,  If so I have never used that kind of programming

It is a command line perl program.  Instead of the perl code being placed in a file, you run it directly from the command line.

What it means:
    perl               Use the perl interpreter to run the code
    -i.bak            Edit the file in-place (use the named file as both input and output)
                         and create a backup with the extension .bak
    -p                  assume "while (<>) { ... }" loop around program
                         print line at end of while loop
    -e                  The actual perl code.  It is placed in quotes so the shell doesn't do anything
                          unix shells use single-quotes, windows shell use double-quote
    filename        The filename to edit


So, in long form, it would be this:

#!/usr/bin/perl
use File::Copy;
my $filename=shift;
copy($filename, "$filename.bak");
open(IN, "<$filename.bak") or die "input: $!\n";
open(OUT, ">$filename") or die "output: $!\n";
while(my $Line = <IN>) {
    $Line =~ s/\s{3,}/\|/g;
    print OUT $Line;
}
close(OUT);
close(IN);
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

You need to find the exact specification for this file.

Is it delimited (columns seperated by known token(s) )
or is it fixed width (columns appear at fixed positions)?

Once you know that you can write some bulletproof code to parse it.

thanks for your suggestion, but i was not able to implement it.  Have never done anything in that language and had to take this project to someone else