CODEWITHSUNDEEP
awksedremoving-whitespace
R_S_C
R_S_C

Reputation: 75

Modify CSV file using sed or any other command

I am trying to remove spaces and tabs from csv file. (In Linux via command line)

sed "s/[[:blank:]]\{1,\}//g" ./TRIM.csv

By using the above command, the space between value/item of a column is removed.

(Eg. Hello World is becoming HelloWorld)

Also I want to replace '|' with '~'

SAMPLE FILE:

   0 |         null |               0 |              0 |             0 |             null |  null |
   1 |         Hello World |               0 |              0 |             0 |             null |  null |

Desired output :

0~null~0~0~0~null~null~
1~Hello World~0~0~0~null~null~

All spaces have been removed except the space between 'hello world' which is value of column that has space.

Upvotes: 2

Views: 517

Answers (4)

potong
potong

Reputation: 58420

This might work for you (GNU sed):

sed 'y/|/\n/;s/.*/echo "&"|sed '\''s#^\\s*\\|\\s*$##g'\''/e;y/\n/~/' file

Translate |'s to \n's.

Take the multiline result and apply a second invocation of sed to trim white space from the start and end of each line.

Translate the result turning \n's back to ~'s

Alternative:

sed -E 's/^\s*|\s*(\|)\s*|\s*$/\1/g;y/|/~/' file

Upvotes: 1

RavinderSingh13
RavinderSingh13

Reputation: 133518

With your shown samples, could you please try following. Written and tested in GNU awk should work in any awk.

awk -F'[[:blank:]]*\\|[[:blank:]]*' -v OFS="~" '{sub(/^ +/,"");$1=$1}1' Input_file

Upvotes: 5

Raman Sailopal
Raman Sailopal

Reputation: 12877

 sed -r 's/[[:space:]]+\|/~/g;s/~[[:space:]]+/~/g' file

Enable regular expression interpretation with -r or -E and then replace one or more spaces and then "|" with "~". After this, replace "~" followed by one or more spaces with "~"

Upvotes: 4

choroba
choroba

Reputation: 241868

sed -e 's/[ \t]*|[ \t]*/~/g' -e 's/^ *\| *$//g'
#               ^                     ^
#               |                     |
#  Remove whitespace around     Replace leading and
#  | and replace it with ~      trailing whitespace

Upvotes: 1

Related Questions