Removing spaces from specific column in CSV file

Question

I have a csv file with lines like this:

300001;Company Name;023-1 2 3 4 5 6 7;023-3 2 4 6 43 4;[email protected];;;;Street Name;184;;Postal Code;City

I want to strip the spaces from column 3 and 4 (phone and fax number)so that it looks like this:

300001;Company Name;023-1234567;023-3246434;[email protected];;;;Street Name;184;;Postal Code;City

I tried these awk-codes (for column 3) I found on Stackoverflow with no luck:

awk -v FS=OFS=";" '{gsub(/ /,"",$3)} 1' file.csv > test.csv
awk -F, '$3 ~ / / { OFS= ";"; $3 = ""; } 1' file.csv > test.csv

I've been googling for half a day now, but can't find anything that works.

Can you expand the "with no luck" sentence? What was your output? — fedorqui
– fedorqui, Commented Jan 14, 2015 at 8:43
by no luck I mean that the output was the same as the input. But A.M.D.'s answer seems to have solved my problem! — wtrdk
– wtrdk, Commented Jan 14, 2015 at 8:44
Well the problem lies in the -v FS=OFS=";" expression, the gsub() part is fine. You cannot set two variables in the same expression, so you needed -v FS=";" -v OFS=";". — fedorqui
– fedorqui, Commented Jan 14, 2015 at 8:47

Arjun Mathew Dan · Accepted Answer · 2015-01-14 09:43:35Z

1

Try this:

awk 'BEGIN{FS=OFS=";"} {gsub(/ /,"",$3); gsub(/ /,"",$4)}1' File

We set the input and output field seperators as ;. Then substitute the spaces in the 3rd and 4th fields with nothing. Hope it helps.

As others have already mentioned, setting both FS and OFS at one shot (FS=OFS=";") with -v is the reason why it didn't work in your case. I moved the same to BEGIN block. Thats it.

edited Jan 14, 2015 at 9:43

answered Jan 14, 2015 at 8:42

Arjun Mathew Dan

5,3281 gold badge18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wtrdk Over a year ago

Wow, that was fast! Your answer seems to work, I'm gonna test it now on some files. Thanks!

Arjun Mathew Dan Over a year ago

i'll wait for your results :-)

wtrdk Over a year ago

Aaaaand..... the results are in! It works like a charm. Although the other solutions metioned in this thread also work, I'll mark yours as you're the first ;-) Thanks!

nu11p01n73R · Accepted Answer · 2015-01-14 08:46:32Z

1

The issue is that you cannot set FS and OFS in one go. Seperate the two as

$ awk  -vFS=";" -vOFS=";" '{gsub(" ","",$3); gsub(" ", "", $4) }1' input
300001;Company Name;023-1234567;023-3246434;[email protected];;;;Street Name;184;;Postal Code;City

answered Jan 14, 2015 at 8:46

nu11p01n73R

26.8k3 gold badges42 silver badges52 bronze badges

Comments

Kent · Accepted Answer · 2015-01-14 08:48:19Z

1

your cause of problem is -v FS=OFS=";" you can either use:

awk -F';' -v OFS=";" '...'

or

awk 'BEGIN{FS=OFS=";"} ...'

Other codes look ok, except that you forgot the column4. this line should work:

awk -F';' -v OFS=";" 'gsub(/ /,"",$3)+gsub(/ /,"",$4)+7' file

answered Jan 14, 2015 at 8:48

Kent

197k36 gold badges248 silver badges317 bronze badges

1 Comment

Ed Morton Over a year ago

'gsub(/ /,"",$3)+gsub(/ /,"",$4)+7' is one character briefer than '{gsub(/ /,"",$3);gsub(/ /,"",$4)}7'. Not sure it's worth the tradeoff in clarity or the time it took me thinking about it to convince myself it'd always succeed!

Collectives™ on Stack Overflow

Removing spaces from specific column in CSV file

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related