awk/sed/something else for csv file

Shell Programming and Scripting
1

Hi,

I have a filename.csv in which there are 3 colums, ie:

Name ; prefixnumber ; number
root ; 020 ; 1234567
user1,2,3 ; 070 ; 7654321

What I want is to merge colum 2 and 3 that it becomes 0201234567 or even better +31201234567 so the country number is used and drop the leading 0.
Names that have commas in their name (yes, it happens...) need those commas replaced with dashes like user1-2-3.

I've been trying with sed and awk, splitting the file up and then join it again, but my knowledge is just unsufficient :frowning:

Thanks in advance,

Niels

2

An awk:

awk '{gsub(/ /,"");gsub(/,/,"-",$1);sub(/^0/,"+31",$2);print $1":"$2$3}' FS=";" file
1 Like
3

Hero! Thanks a million :slight_smile:
The only thing I changed is the

print $1":"$2$3}

I replaced the : with ; so it's still a ; seperated file for the import.

Oh one thing though, I noticed the spaces in the first colum are missing now as well, for instance:

Just A Name ;06 ;12346578 ;

Became:

JustAName;+3161234578

When I remove the first gsub it leaves the spaces in the names alone, but the number output gets spaced as well, like:

Just A Name ;+316 1234578

Anyway to leave the spaces in the first colum alone, but remove in the 2nd and 3rd?

4

You can use the FS:

 awk '{gsub(/ /,"");gsub(/,/,"-",$1);sub(/^0/,"+31",$2);print $1FS$2$3}' FS=";" file
5

I've solved it this way:

awk '{gsub(/ /,"",$2);gsub(/,/,"-",$1);sub(/^0/,"+31",$2);print $1FS$2$3}' FS=";" file

If you have a better solution let me know, but this works fine too.

6

Hi,

Here a 'Perl' script:

$ perl -F";" -lane 'next if $. == 1 ; ($num = join "", @F[1,2]) =~ s/\s*//g ;$F[0] =~ tr/,/-/ ; $num =~ s/^0/+31/; print (join "; ", $F[0], $num)' infile

Regards,
Birei

7

Hi Birei,

Appriciated, but that would involve installing perl (not default on FreeBSD) and since awk is 'base' I'll stick to that.

8 
skrynesaver@busybox ~/tmp$ cat tmp.data
Name ; prefixnumber ; number
root ; 020 ; 1234567
user1,2,3 ; 070 ; 7654321
skrynesaver@busybox ~/tmp$ awk -F' ; ' '{print $1 " ; +"$2$3}' tmp.data
Name ; +prefixnumbernumber
root ; +0201234567
user1,2,3 ; +0707654321

Works for gnu awk which can take multi-char delimiters

9 
BEGIN {
    FS=" ; ";
    OFS=";";
    getline;
    print;
}
{
    gsub(/,/, "-", $1);
    sub(/^0/, "+31", $2);
    print $1,$2$3;
}

Should work with any version of awk. The getline and print make sure that the header line is unaltered.

10

Quite close:

 awk '{a=$1;gsub(/ /,"");gsub(/,/,"-",a);sub(/^0/,"+31",$2);print a""FS$2$3}' FS=";" file
1 Like