LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   AWK: change a particular field in a csv file help help help!!!! (http://www.linuxquestions.org/questions/programming-9/awk-change-a-particular-field-in-a-csv-file-help-help-help-659755/)

haydar68 08-01-2008 10:30 AM

AWK: change a particular field in a csv file help help help!!!!
 
Hi,

I have a csv file, it contains 4 columns or fields where these fields are separated by ":"
first fied is cn=10,ou=work,o=dom.com
second field is last name as vezina
third field is first name as denis
fourth field is a mail address as denis.vezina@dom.com

here is the csv file:

cn=10,ou=work,o=dom.com:vezina:denis:denis.vezina@dom.com
cn=20,ou=work,o=dom.com:saul:jean:jean.saul@dom.com
cn=30,ou=work,o=dom.com:didi:isabelle:isabelle.didi@dom.com
cn=40,ou=work,o=dom.com:kapa:alain:alain.kapa@dom.com

I hope to put the mail address instead cn,
example
cn=denis.vezina@dom.com instead cn=10

and I want to get this file result:

cn=denis.vezina@dom.com,ou=work,o=dom.com:vezina:denis:denis.vezina@dom.com
cn=jean.saul@dom.com,ou=work,o=dom.com:saul:jean:jean.saul@dom.com
cn=isabelle.didi@dom.com,ou=work,o=dom.com:didi:isabelle:isabelle.didi@dom.com
cn=alain.kapa@dom.com,ou=work,o=dom.com:kapa:alain:alain.kapa@dom.com

I tried this command:
awk -F: -v OFS=: '{$1 = $4; print }' file

but I get this result:

cn=denis.vezina@dom.com:vezina:denis:denis.vezina@dom.com
cn=jean.saul@dom.com:saul:jean:jean.saul@dom.com
cn=isabelle.didi@dom.com:didi:isabelle:isabelle.didi@dom.com
cn=alain.kapa@dom.com:kapa:alain:alain.kapa@dom.com

I loose this data ",ou=work,o=dom.com" in each line.

Is it a way or a command awk/sed/cut to get like this result?

cn=denis.vezina@dom.com,ou=work,o=dom.com:vezina:denis:denis.vezina@dom.com
cn=jean.saul@dom.com,ou=work,o=dom.com:saul:jean:jean.saul@dom.com
cn=isabelle.didi@dom.com,ou=work,o=dom.com:didi:isabelle:isabelle.didi@dom.com
cn=alain.kapa@dom.com,ou=work,o=dom.com:kapa:alain:alain.kapa@dom.com

Thanks a lot for your help!

Haydar

druuna 08-01-2008 10:39 AM

Hi,

Is this what you are looking for?

awk -F: -v OFS=: '{$1 = $4; print "cn="$1",ou=work,o=dom.com", $2, $3, $4 }' infile

Only works if the cn= and ,ou=work,o=dom.com are the same for all lines.

Hope this helps.

haydar68 08-01-2008 11:22 AM

Quote:

Originally Posted by druuna (Post 3233296)
Hi,

Is this what you are looking for?

awk -F: -v OFS=: '{$1 = $4; print "cn="$1",ou=work,o=dom.com", $2, $3, $4 }' infile

Only works if the cn= and ,ou=work,o=dom.com are the same for all lines.

Hope this helps.

But if other lines contain a data different to ou=work,o=dom.com.
Is there another solution for different domains?

Thanks for this solution.

Haydar

burschik 08-01-2008 11:39 AM

Code:

awk -F: -v OFS=: '{sub(/^cn=[^,]+/, "cn=" $4); print;}'

radoulov 08-01-2008 11:41 AM

Given your original file format from your previous post, you could try something like this with your ldif file:
Code:

awk -F': |\n' '{
  sub(/cn=[^,]*/, "cn=" $10, $2)
  print $2,$4,$6,$8,$10
  }' RS= OFS=: file


colucix 08-01-2008 11:43 AM

Quote:

Originally Posted by burschik (Post 3233344)
Code:

awk -F: -v OFS=: '{sub(/^cn=[^,]+/, "cn=" $4); print;}'

Good call!

haydar68 08-01-2008 12:01 PM

Quote:

Originally Posted by burschik (Post 3233344)
Code:

awk -F: -v OFS=: '{sub(/^cn=[^,]+/, "cn=" $4); print;}'

Hi Burschik,

I ran this command that you provide to me and I got this result:

awk -F: -v OFS=: '{sub(/^cn=[^,]+/, "cn=" $4); print;}' jj.txt


cn=10,ou=work,o=dom.com:vezina:denis:denis.vezina@dom.com
cn=jean.saul@dom.com,ou=work,o=dom.com:saul:jean:jean.saul@dom.com
cn=isabelle.didi@dom.com,ou=work,o=dom.com:didi:isabelle:isabelle.didi@dom.com
cn=alain.kapa@dom.com,ou=work,o=dom.com:kapa:alain:alain.kapa@dom.com

If you check the first line, you will notice that cn=10 and it does not change to mail.

Thanks

haydar68 08-01-2008 12:05 PM

Quote:

Originally Posted by radoulov (Post 3233347)
Given your original file format from your previous post, you could try something like this with your ldif file:
Code:

awk -F': |\n' '{
  sub(/cn=[^,]*/, "cn=" $10, $2)
  print $2,$4,$6,$8,$10
  }' RS= OFS=: file


Hi Radoulov,

I ran your command that you provided to me :

awk -F': |\n' '{sub(/^cn=[^,]+/, "cn=" $10, $2) print $2,$4,$6,$8,$10,$12 }' RS= OFS=: book.ldif

and I got this error result:

awk: cmd. line:1: {sub(/^cn=[^,]+/, "cn=" $10, $2) print $2,$4,$6,$8,$10,$12 }
awk: cmd. line:1: ^ parse error



by the way, the book.ldif contains these data:

dn: cn=10,ou=work,o=dom.com
cn: denis.vezina@dom.com
sn: vezina
givenName: denis
mail: denis.vezina@dom.com
displayName: denis vezina

dn: cn=20,ou=work,o=dom.com
cn: jean.paul@dom.com
sn: Paul
givenName: jean
mail: jean.paul@dom.com
displayName: jean paul

dn: cn=30,ou=work,o=dom.com
cn: isabelle.didi@dom.com
sn: didi
givenName: isabelle
mail: isabelle.didi@dom.com
displayName: isabelle didi

dn: cn=40,ou=work,o=dom.com
cn: alain.papa@dom.com
sn: papa
givenName: alain
mail: alain.papa@dom.com
displayName: alain papa


Thanks for your help

ghostdog74 08-01-2008 12:28 PM

Code:

awk 'BEGIN{FS=":"}
{
 old=FS; a=$NF
 FS=","; $0=$0
 $1="cn="a; OFS=","
 $1=$1;
 FS=old
}1'  file


radoulov 08-01-2008 12:56 PM

Quote:

Originally Posted by haydar68 (Post 3233370)
Hi Radoulov,

I ran your command that you provided to me :

awk -F': |\n' '{sub(/^cn=[^,]+/, "cn=" $10, $2) print $2,$4,$6,$8,$10,$12 }' RS= OFS=: book.ldif
[...]

I did not post this command ... (you're missing the semicolon (or the new line) before the print statement).

Consider this:

Code:

zsh-4.3.4% cat file
dn: cn=10,ou=work,o=dom.com
cn: denis.vezina@dom.com
sn: vezina
givenName: denis
mail: denis.vezina@dom.com
displayName: denis vezina

dn: cn=20,ou=work,o=dom.com
cn: jean.paul@dom.com
sn: Paul
givenName: jean
mail: jean.paul@dom.com
displayName: jean paul

dn: cn=30,ou=work,o=dom.com
cn: isabelle.didi@dom.com
sn: didi
givenName: isabelle
mail: isabelle.didi@dom.com
displayName: isabelle didi

dn: cn=40,ou=work,o=dom.com
cn: alain.papa@dom.com
sn: papa
givenName: alain
mail: alain.papa@dom.com
displayName: alain papa

zsh-4.3.4% awk -F': |\n' '{
  sub(/cn=[^,]*/, "cn=" $10, $2)
  print $2,$4,$6,$8,$10
  }' RS= OFS=: file
cn=denis.vezina@dom.com,ou=work,o=dom.com:denis.vezina@dom.com:vezina:denis:denis.vezina@d
om.com
cn=jean.paul@dom.com,ou=work,o=dom.com:jean.paul@dom.com:Paul:jean:jean.paul@dom.com
cn=isabelle.didi@dom.com,ou=work,o=dom.com:isabelle.didi@dom.com:didi:isabelle:isabelle.di
di@dom.com
cn=alain.papa@dom.com,ou=work,o=dom.com:alain.papa@dom.com:papa:alain:alain.papa@dom.com
zsh-4.3.4%


haydar68 08-01-2008 03:13 PM

Quote:

Originally Posted by radoulov (Post 3233433)
I did not post this command ... (you're missing the semicolon (or the new line) before the print statement).

Consider this:

Code:

zsh-4.3.4% cat file
dn: cn=10,ou=work,o=dom.com
cn: denis.vezina@dom.com
sn: vezina
givenName: denis
mail: denis.vezina@dom.com
displayName: denis vezina

dn: cn=20,ou=work,o=dom.com
cn: jean.paul@dom.com
sn: Paul
givenName: jean
mail: jean.paul@dom.com
displayName: jean paul

dn: cn=30,ou=work,o=dom.com
cn: isabelle.didi@dom.com
sn: didi
givenName: isabelle
mail: isabelle.didi@dom.com
displayName: isabelle didi

dn: cn=40,ou=work,o=dom.com
cn: alain.papa@dom.com
sn: papa
givenName: alain
mail: alain.papa@dom.com
displayName: alain papa

zsh-4.3.4% awk -F': |\n' '{
  sub(/cn=[^,]*/, "cn=" $10, $2)
  print $2,$4,$6,$8,$10
  }' RS= OFS=: file
cn=denis.vezina@dom.com,ou=work,o=dom.com:denis.vezina@dom.com:vezina:denis:denis.vezina@d
om.com
cn=jean.paul@dom.com,ou=work,o=dom.com:jean.paul@dom.com:Paul:jean:jean.paul@dom.com
cn=isabelle.didi@dom.com,ou=work,o=dom.com:isabelle.didi@dom.com:didi:isabelle:isabelle.di
di@dom.com
cn=alain.papa@dom.com,ou=work,o=dom.com:alain.papa@dom.com:papa:alain:alain.papa@dom.com
zsh-4.3.4%


Hi Radoulov,

sorry for last comment. I tried the same command.

Now I tried it again and I get the same issue:

awk -F': |\n' '{sub(/cn=[^,]*/, "cn=" $10, $2) print $2,$4,$6,$8,$10 }' RS= OFS=: file
awk: cmd. line:1: {sub(/cn=[^,]*/, "cn=" $10, $2) print $2,$4,$6,$8,$10 }
awk: cmd. line:1: ^ parse error


Have you any idea what it cause this issue?

Thanks a lot for your help,

Haydar

radoulov 08-01-2008 03:15 PM

You should read more carefully my posts :) Anyway, as you insist to use the code on one line,
try this:
Code:

awk -F': |\n' '{sub(/cn=[^,]*/, "cn=" $10, $2);print $2,$4,$6,$8,$10 }' RS= OFS=: file

haydar68 08-01-2008 03:18 PM

Quote:

Originally Posted by ghostdog74 (Post 3233396)
Code:

awk 'BEGIN{FS=":"}
{
 old=FS; a=$NF
 FS=","; $0=$0
 $1="cn="a; OFS=","
 $1=$1;
 FS=old
}1'  file


Hi GhostDog74,

I tested this command and it works fine.

Thanks a lot for your great help,

Haydar

haydar68 08-01-2008 03:28 PM

Quote:

Originally Posted by radoulov (Post 3233583)
You should read more carefully my posts :) Anyway, as you insist to use the code on one line,
try this:
Code:

awk -F': |\n' '{sub(/cn=[^,]*/, "cn=" $10, $2);print $2,$4,$6,$8,$10 }' RS= OFS=: file

Hi Radoulov,

I tried this command on one line and I get this error message:

awk -F': |\n' '{sub(/cn=[^,]*/, "cn=" $10, $2);print $2,$4,$6,$8,$10 }' RS= OFS=: file
awk: cmd. line:1: (FILENAME=file FNR=1) fatal error: internal error
Aborted


I tried this command on many lines and I get this error message:

awk -F': |\n' '{
> sub(/cn=[^,]*/, "cn=" $10, $2)
> print $2,$4,$6,$8,$10
> }' RS= OFS=: file
awk: cmd. line:2: (FILENAME=file FNR=1) fatal error: internal error
Aborted

cat file
dn: cn=10,ou=work,o=dom.com
cn: denis.vezina@dom.com
sn: vezina
givenName: denis
mail: denis.vezina@dom.com
displayName: denis vezina

dn: cn=20,ou=work,o=dom.com
cn: jean.paul@dom.com
sn: Paul
givenName: jean
mail: jean.paul@dom.com
displayName: jean paul

dn: cn=30,ou=work,o=dom.com
cn: isabelle.didi@dom.com
sn: didi
givenName: isabelle
mail: isabelle.didi@dom.com
displayName: isabelle didi

dn: cn=40,ou=work,o=dom.com
cn: alain.papa@dom.com
sn: papa
givenName: alain
mail: alain.papa@dom.com
displayName: alain papa


Is it related to the Linux version? I use RedHat Enterprise AS 2.1

THanks for your great help,

Haydar

haydar68 08-01-2008 06:38 PM

Quote:

Originally Posted by radoulov (Post 3233583)
You should read more carefully my posts :) Anyway, as you insist to use the code on one line,
try this:
Code:

awk -F': |\n' '{sub(/cn=[^,]*/, "cn=" $10, $2);print $2,$4,$6,$8,$10 }' RS= OFS=: file

Hi Radoulov,

I run this command in two ways on RHEL AS4 and it works fine. I think that the previous problem is related to the awk version or the RedHat version 2.1.

THanks,

Haydar


All times are GMT -5. The time now is 06:18 PM.