LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
Reload this Page AWK: change a particular field in a csv file help help help!!!!
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
Page 1 of 2 1 2
 
LinkBack Search this Thread
Old 08-01-2008, 09:30 AM   #1
haydar68
Member
 
Registered: Jul 2008
Posts: 35

Rep: Reputation: 15
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
 
Old 08-01-2008, 09:39 AM   #2
druuna
LQ Veteran
 
Registered: Sep 2003
Location: the Netherlands
Distribution: lfs, debian, rhel
Posts: 8,705
Blog Entries: 8

Rep: Reputation: 1616Reputation: 1616Reputation: 1616Reputation: 1616Reputation: 1616Reputation: 1616Reputation: 1616Reputation: 1616Reputation: 1616Reputation: 1616Reputation: 1616
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.
 
Old 08-01-2008, 10:22 AM   #3
haydar68
Member
 
Registered: Jul 2008
Posts: 35

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by druuna View Post
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
 
Old 08-01-2008, 10:39 AM   #4
burschik
Member
 
Registered: Jul 2008
Posts: 159

Rep: Reputation: 31
Code:
awk -F: -v OFS=: '{sub(/^cn=[^,]+/, "cn=" $4); print;}'
 
Old 08-01-2008, 10:41 AM   #5
radoulov
Member
 
Registered: Apr 2007
Location: Milano, Italia/Варна, България
Distribution: Ubuntu, Open SUSE
Posts: 212

Rep: Reputation: 34
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
 
Old 08-01-2008, 10:43 AM   #6
colucix
Moderator
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.4 OpenSuSE 12.2
Posts: 9,897

Rep: Reputation: 1682Reputation: 1682Reputation: 1682Reputation: 1682Reputation: 1682Reputation: 1682Reputation: 1682Reputation: 1682Reputation: 1682Reputation: 1682Reputation: 1682
Quote:
Originally Posted by burschik View Post
Code:
awk -F: -v OFS=: '{sub(/^cn=[^,]+/, "cn=" $4); print;}'
Good call!
 
Old 08-01-2008, 11:01 AM   #7
haydar68
Member
 
Registered: Jul 2008
Posts: 35

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by burschik View Post
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
 
Old 08-01-2008, 11:05 AM   #8
haydar68
Member
 
Registered: Jul 2008
Posts: 35

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by radoulov View Post
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
 
Old 08-01-2008, 11:28 AM   #9
ghostdog74
Senior Member
 
Registered: Aug 2006
Posts: 2,695
Blog Entries: 5

Rep: Reputation: 238Reputation: 238Reputation: 238
Code:
awk 'BEGIN{FS=":"}
{ 
 old=FS; a=$NF
 FS=","; $0=$0
 $1="cn="a; OFS=","
 $1=$1; 
 FS=old 
}1'  file
 
Old 08-01-2008, 11:56 AM   #10
radoulov
Member
 
Registered: Apr 2007
Location: Milano, Italia/Варна, България
Distribution: Ubuntu, Open SUSE
Posts: 212

Rep: Reputation: 34
Quote:
Originally Posted by haydar68 View Post
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%
Last edited by radoulov; 08-01-2008 at 12:09 PM.
 
Old 08-01-2008, 02:13 PM   #11
haydar68
Member
 
Registered: Jul 2008
Posts: 35

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by radoulov View Post
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
 
Old 08-01-2008, 02:15 PM   #12
radoulov
Member
 
Registered: Apr 2007
Location: Milano, Italia/Варна, България
Distribution: Ubuntu, Open SUSE
Posts: 212

Rep: Reputation: 34
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
Last edited by radoulov; 08-01-2008 at 02:16 PM.
 
Old 08-01-2008, 02:18 PM   #13
haydar68
Member
 
Registered: Jul 2008
Posts: 35

Original Poster
Rep: Reputation: 15
Smile
Quote:
Originally Posted by ghostdog74 View Post
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
 
Old 08-01-2008, 02:28 PM   #14
haydar68
Member
 
Registered: Jul 2008
Posts: 35

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by radoulov View Post
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
 
Old 08-01-2008, 05:38 PM   #15
haydar68
Member
 
Registered: Jul 2008
Posts: 35

Original Poster
Rep: Reputation: 15
Smile
Quote:
Originally Posted by radoulov View Post
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
 
  


Reply
Page 1 of 2 1 2


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
awk question on handling *.CSV "text fields" in awk jschiwal Programming 8 05-27-2010 06:23 AM
Comparing two csv files and write different record in third CSV file irfanb146 Linux - Newbie 3 06-30-2008 09:15 PM
deleting a field using awk jkeertir Linux - Newbie 5 04-13-2008 10:55 PM
Split CSV, field as filename richmur Programming 2 10-24-2006 08:39 AM
awk and change file content Ayman.mashal Programming 4 06-02-2005 06:03 AM


All times are GMT -5. The time now is 10:43 PM.

Contact Us - Advertising Info - Rules - LQ Merchandise - Donations - Contributing Member - LQ Sitemap -
Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: @linuxquestions
Open Source Consulting | Domain Registration