Please help how to temove spaces from CSV file

[hayesb2]

I need to remove spaces from fields in a CSV file...
Using the following command works, but it does not remove the spaces from any fields in the CSV that are within double quotes -

perl -pi.bak -e "s/\s+,/,/g" file.csv

How would I expand that command so that it also would remove the trailing spaces that are within double quotes?

FYI - I got that command from searching which resulted in this thread -

http://www.tek-tips.com/viewthread.cfm?qid=1308534&page=154

 

[feherke]

Hi

perl -pi.bak -e 's/\s*("?)\s+,/\1,/g' file.csv

Feherke.

http://free.rootshell.be/~feherke/

 

[hayesb2]

Hi there,
Thank you for the response, I tried that but unfortunately it did not work...I got the following error -

C:\perlscripts>perl -pi.bak -e 's/\s*("?)\s+,/\1,/g' test.csv
Bareword found where operator expected at -e line 1, near "'s/\s*(?)\s+,/\1,/g'
test"
(Missing operator before test?)
syntax error at -e line 1, near "'s/\s*(?)\s+,/\1,/g' test"
Execution of -e aborted due to compilation errors.


To give an example of what I am needing, here is an example of the CSV layout -

XXXXX ,"X", ," "," ","test "
abc123 ,1234," "," ","test "



And the end result I would need is to look like this -

XXXXX,"X",,"","","test"
abc123,1234,"","","test"

 

[feherke]

Hi

Oops, I changed the double quotes to single quotes. Windows can not handle that. Change them [highlight]back[/highlight] and [highlight pink]escape the quotes[/highlight] :

perl -pi.bak -e [highlight]'[/highlight]s/\s*([highlight pink]\[/highlight]"?)\s*,/\1,/g;s/[ \t]*([highlight]\[/highlight]"?)[ \t]*$/\1/[highlight pink]'[/highlight] file.csv

Note that you removed the second substitution ( [tt]s///[/tt] ) which handled the last field. Either put it back, or [highlight palegreen]include it[/highlight] in the same expression :

perl -pi.bak -e "s/[highlight paleturquoise][ \t][/highlight]*(\"?)[highlight paleturquoise][ \t][/highlight]*[highlight palegreen](,|$)[/highlight]/\1\2/g" file.csv

Note that the whitespace character class ( [tt]\s[/tt] ) includes carriage return ( [tt]\r[/tt] ) and line feed ( [tt]\n[/tt] ) too, so they would be removed and the lines would get concatenated. The substitution which handles the last field you have to [highlight paleturquoise]explicitly specify[/highlight] space ( ' ' ) and horizontal tab ( [tt]\t[/tt] ) as [tt][ \t][/tt].


Feherke.

http://free.rootshell.be/~feherke/

 

[MillerH]

As an alternative, one can always just write a script since that will work the same regardless of the shell it's run on.

[gray]#!/usr/bin/perl -w[/gray]

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]File::Copy[/green] [red]qw([/red][purple]move[/purple][red])[/red][red];[/red]

[black][b]use[/b][/black] [green]strict[/green][red];[/red]
[black][b]use[/b][/black] [green]warnings[/green][red];[/red]

[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$csvfile[/blue] = [red]'[/red][purple]file.csv[/purple][red]'[/red][red];[/red]
[black][b]my[/b][/black] [blue]$bakfile[/blue] = [blue]$csvfile[/blue] . [red]'[/red][purple].bak[/purple][red]'[/red][red];[/red]

[maroon]move[/maroon][red]([/red][blue]$csvfile[/blue], [blue]$bakfile[/blue][red])[/red][red];[/red]

[url=http://perldoc.perl.org/functions/open.html][black][b]open[/b][/black][/url] [black][b]my[/b][/black] [blue]$ih[/blue], [red]'[/red][purple]<[/purple][red]'[/red], [blue]$bakfile[/blue] or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]Cannot open file, [blue]$bakfile[/blue]: [blue]$![/blue][/purple][red]"[/red][red];[/red]
[black][b]open[/b][/black] [black][b]my[/b][/black] [blue]$oh[/blue], [red]'[/red][purple]>[/purple][red]'[/red], [blue]$csvfile[/blue] or [black][b]die[/b][/black] [red]"[/red][purple]Cannot open file, [blue]$csvfile[/blue]: [blue]$![/blue][/purple][red]"[/red][red];[/red]

[olive][b]while[/b][/olive] [red]([/red]<[blue]$ih[/blue]>[red])[/red] [red]{[/red]
	[red]s/[/red][purple][purple][b]\s[/b][/purple]*("?)[purple][b]\s[/b][/purple]*(,|[purple][b]\n[/b][/purple])[/purple][red]/[/red][purple][blue]$1[/blue][blue]$2[/blue][/purple][red]/[/red][red]g[/red][red];[/red]
	[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [blue]$oh[/blue] [blue]$_[/blue][red];[/red]
	[gray][i]#print STDOUT $_;[/i][/gray]
[red]}[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.10.0) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[li]warnings - Perl pragma to control optional warnings[/li]
[/ul]
Core (perl 5.10.0) Modules used :
[ul]
[li]File::Copy - Copy files or filehandles[/li]
[/ul]
[/tt]- Miller