Removing spaces from columns of a CSV file in bash

Question

I have a CSV file in which every column contains unnecessary spaces(or tabs) after the actual value. I want to create a new CSV file removing all the spaces using bash.

For example

One line in input CSV file

abc def pqr             ;valueXYZ              ;value PQR              ;value4

same line in output csv file should be

abc def pqr;valueXYZ;value PQR;value4

I tried using awk to trim each column but it didnt work. Can anyone please help me on this ?

Thanks in advance :)

I edited my test case, since the values here can contain spaces.

Sorry to add up in the problem, The values here can contain spaces also (For ex a value1 can be "blah blah blah"). But I would like to maitain those spaces, I just want to remove whitespaces between two values. — vikas ramnani, Jun 27 '12 at 14:46

vergenzt · Accepted Answer · 2012-06-28T12:12:03.763

4

$ cat cvs_file | awk 'BEGIN{ FS=" *;"; OFS=";" } {$1=$1; print $0}'

Set the input field separator (FS) to the regex of zero or more spaces followed by a semicolon.
Set the output field separator (OFS) to a simple semicolon.
$1=$1 is necessary to refresh $0.
Print $0.

$ cat cvs_file
abc def pqr             ;valueXYZ              ;value PQR              ;value4

$ cat cvs_file | awk 'BEGIN{ FS=" *;"; OFS=";" } {$1=$1; print $0}'
abc def pqr;valueXYZ;value PQR;value4

edited Jun 28 '12 at 12:12

answered Jun 27 '12 at 20:28

vergenzt

9,669
4
40
47

Thank you very much for this one @vergenzt ! This took care of all the cases :) – vikas ramnani Jun 28 '12 at 08:03

score 3 · Answer 2 · answered Jun 27 '12 at 14:48

3

If the values themselves are always free of spaces, the canonical solution (in my view) would be to use tr:

$ tr -d '[:blank:]' < CSV_FILE > CSV_FILE_TRIMMED

answered Jun 27 '12 at 14:48

unwind

391,730
64
469
606

score 1 · Answer 3 · answered Jun 27 '12 at 14:51

1

This will replace multiple spaces with just one space:

sed -r 's/\s+/ /g'

answered Jun 27 '12 at 14:51

amaksr

7,555
2
16
17

score 0 · Answer 4 · answered Jun 27 '12 at 15:12

If you know what your column data will end in, then this is a surefire way to do it:

sed 's|$.*[a-zA-Z0-9]$ *|\1|g'

The character class would be where you put whatever your data will end in.

Otherwise, if you know more than one space is not going to come in your fields, then you could use what user1464130 gave you.

If this doesn't solve your problem, then get back to me.

score 0 · Answer 5 · answered Sep 06 '19 at 08:05

0

I found one way to do what I wanted that is remove blank line and remove trailing newline of a file in an efficient way. I do this with :

grep -v -e '^[[:space:]]*$' foo.txt

from Remove blank lines with grep

answered Sep 06 '19 at 08:05

utopman

581
4
13

Removing spaces from columns of a CSV file in bash

5 Answers5