awk problem 
Author Message
 awk problem

Hi,

just beginning wit awk and have some problem with it.

I have to put all users placed in /etc/passwd into a Database.

Now I experimented a bit with awk and got something like that:

cat /etc/passwd | awk 'BEGIN {FS=":"}{nlines++}{print NR":" $1":" $3":"
$4":" $6":" $7":"}'>npwd

---npwd-file-----
1:root:0:0:/root:/bin/bash
2:bin:1:1:/bin:/bin/bash
3:daemon:2:2:/sbin:/bin/bash
4:lp:4:7:/var/spool/lpd:/bin/bash
5:mail:8:12:/var/spool/clientmqueue:/bin/false
6:games:12:100:/var/games:/bin/bash
7:wwwrun:30:65534:/var/lib/wwwrun:/bin/bash
8:named:44:44:/var/named:/bin/bash
9:nobody:65534:65533:/var/lib/nobody:/bin/bash
10:ftp:40:49:/srv/ftp:/bin/bash
11:man:13:62:/var/cache/man:/bin/bash

13:uucp:10:14:/etc/uucp:/bin/bash
14:at:25:25:/var/spool/atjobs:/bin/bash
15:mysql:60:2:/var/lib/mysql:/bin/false
16:sshd:71:65:/var/lib/sshd:/bin/false
17:postfix:51:51:/var/spool/postfix:/bin/false
18:ntp:74:65534:/var/lib/ntp:/bin/false
-----snip---

How can I now put these data into a DB. How to separate the first line.I
want to declare variables like
username=`cat npwd | awk '{print $2}'`

but all 18 users are now in username?

Thanks for your help.

Chris

--
Schaut doch einfach mal vorbei. Das Neue Informationsportal.
www.bietet.info



Tue, 22 Nov 2005 22:00:52 GMT  
 awk problem


% Now I experimented a bit with awk and got something like that:
%
% cat /etc/passwd | awk 'BEGIN {FS=":"}{nlines++}{print NR":" $1":" $3":"
% $4":" $6":" $7":"}'>npwd

I would write this as
  awk 'BEGIN { FS = OFS = ":" } { print NR, $1, $3, $4, $6, $7 }' \
          /etc/passwd > npwd

OFS is the output field separator. It replaces , in the print statement.

[...]

% How can I now put these data into a DB.

Depends on the database, but let's say you're writing to a SQL database.
You can have awk generate an insert statement and send it to the database's
terminal interface. For instance:

 BEGIN {
    # for Oracle
    sqlcmd = "sqlplus me/mypw"

    # for Sybase
    #sqlcmd = "isql -Ume -Pmypw"

    # for sqlite
    #sqlcmd = "sqlite mydb.data"

    # for postsgresql
    #sqlcmd = "psql"
 }

 BEGIN { FS = ":"; OFS = "," }

 {
    # all the databases above but Sybase want ; at the end of their
    # commands. Sybase wants a separate line with "go" on it, but
    # I'll save that for the end
    print "insert into usertable (uname,uid,gid,homedir,shell) values(" \
      $1, $3, $4, $6, $7 ");" | sqlcmd
 }  

 END {
    # uncomment for Sybase
    # print "go" | sqlcmd
    close(sqlcmd)
 }
--

Patrick TJ McPhee
East York  Canada



Tue, 22 Nov 2005 22:25:33 GMT  
 awk problem

Quote:

> Hi,

> just beginning wit awk and have some problem with it.

> I have to put all users placed in /etc/passwd into a Database.

> Now I experimented a bit with awk and got something like that:

> cat /etc/passwd | awk 'BEGIN {FS=":"}{nlines++}{print NR":" $1":" $3":"
> $4":" $6":" $7":"}'>npwd

1) You don't use "nlines" so get rid of it.
2) Just set OFS to ":" then you don't need the quoted ":"s in the print, just
commas
3) Instead of cat-ing to awk, just tell awk the file name

i.e.:

awk 'BEGIN{FS=":";OFS=":"}{print NR,$1,$3,$4,$6,$7}' /etc/passwd > npwd

If you didn't  need to introduce line numbers, then "cut" would've been a better
choice of tool than "awk"

Quote:
> ---npwd-file-----
> 1:root:0:0:/root:/bin/bash
> 2:bin:1:1:/bin:/bin/bash
> 3:daemon:2:2:/sbin:/bin/bash
> 4:lp:4:7:/var/spool/lpd:/bin/bash
> 5:mail:8:12:/var/spool/clientmqueue:/bin/false
> 6:games:12:100:/var/games:/bin/bash
> 7:wwwrun:30:65534:/var/lib/wwwrun:/bin/bash
> 8:named:44:44:/var/named:/bin/bash
> 9:nobody:65534:65533:/var/lib/nobody:/bin/bash
> 10:ftp:40:49:/srv/ftp:/bin/bash
> 11:man:13:62:/var/cache/man:/bin/bash

> 13:uucp:10:14:/etc/uucp:/bin/bash
> 14:at:25:25:/var/spool/atjobs:/bin/bash
> 15:mysql:60:2:/var/lib/mysql:/bin/false
> 16:sshd:71:65:/var/lib/sshd:/bin/false
> 17:postfix:51:51:/var/spool/postfix:/bin/false
> 18:ntp:74:65534:/var/lib/ntp:/bin/false
> -----snip---

> How can I now put these data into a DB. How to separate the first line.I
> want to declare variables like
> username=`cat npwd | awk '{print $2}'`

> but all 18 users are now in username?

This would solve that problem:

username=`head -1 npwd | cut -d':' -f2`

or if you want just awk:

username=`awk 'BEGIN{FS=":"}NR == 1{print $2}' npwd`

but what do you now plan to do with that and how did creating npwd make things
any easier than just doing this on /etc/passwd:

username=`head -1 /etc/passwd | cut -d':' -f3`

Regards,

    Ed.



Tue, 22 Nov 2005 22:37:06 GMT  
 awk problem

Hi Patrick

I did this:

cat /etc/passwd | awk 'BEGIN {sqlcmd="mysql -u $user -p$pass -D $db"}
                        BEGIN {FS=":";OFS=","}{print
 "insert into $table (username,uid,gid,homedir,shell) values("$1, $3, $4, $6, $7");"|sqlcmd}
                        END {close(sqlcmd)}'

But then I got an error (1064):
You have an error in your SQL syntax near'/root,/bin/bash)' at line 1

Is it a problem with '/'?

Thanks for your help.

Chris

--
Schaut doch einfach mal vorbei. Das Neue Informationsportal.
www.bietet.info



Tue, 22 Nov 2005 22:52:45 GMT  
 awk problem

Quote:

> awk 'BEGIN{FS=":";OFS=":"}{print NR,$1,$3,$4,$6,$7}' /etc/passwd > npwd

OK, just recognized by other posting

Quote:

> If you didn't  need to introduce line numbers, then "cut" would've been a
> better choice of tool than "awk"

I thought I need for a loop. Somthing like 'for 1 to nlines do'

Quote:

> This would solve that problem:

> username=`head -1 npwd | cut -d':' -f2`

> or if you want just awk:

> username=`awk 'BEGIN{FS=":"}NR == 1{print $2}' npwd`

> but what do you now plan to do with that and how did creating npwd make
> things any easier than just doing this on /etc/passwd:

Don't know. There are in this /etc/passwd 18 users and these users should
now be ported to a database where every information about this user is
stored for the case that I have to resetup the box. In that case I could
get all the user data out of the db and create the account automatically
via script.

Quote:

> username=`head -1 /etc/passwd | cut -d':' -f3`

> Regards,

>     Ed.

In the database are fields for quota and backup.tar-files from their home
dir.

Hope I could make clear what I want to do.

Thanks Chris

--
Schaut doch einfach mal vorbei. Das Neue Informationsportal.
www.bietet.info



Tue, 22 Nov 2005 23:02:01 GMT  
 awk problem

Quote:


> Hi Patrick

> I did this:

> cat /etc/passwd | awk 'BEGIN {sqlcmd="mysql -u $user -p$pass -D $db"}
>                         BEGIN {FS=":";OFS=","}{print
>  "insert into $table (username,uid,gid,homedir,shell) values("$1, $3, $4, $6, $7");"|sqlcmd}
>                         END {close(sqlcmd)}'

> But then I got an error (1064):
> You have an error in your SQL syntax near'/root,/bin/bash)' at line 1

> Is it a problem with '/'?

Assuming that "$user", etc. are shell variables, you need to do this to pass their values to the
awk script:

cat /etc/passwd | awk 'BEGIN {sqlcmd="mysql -u" user " -p" pass " -D" db}
                BEGIN {FS=":";OFS=","}{print
                     "insert into " table " (username,uid,gid,homedir,shell) values("$1, $3, $4,
$6, $7");"|sqlcmd}
                        END {close(sqlcmd)}'
        }' user=$user, pass=$pass, db=$db, table=$table

Note that I'm closing the strings before all the variables and re-opening afterwards.

By the way, from the contents of your password file it looks like you may be on a NIS system, if
so then instead of "cat" you need to use "ypcat" to cat your password file, i.e.:

ypcat passwd | awk ....

Regards,

    Ed.



Wed, 23 Nov 2005 00:05:53 GMT  
 awk problem

Quote:

> cat /etc/passwd | awk 'BEGIN {sqlcmd="mysql -u" user " -p" pass " -D" db}
>                 BEGIN {FS=":";OFS=","}{print
>                      "insert into " table "
>                      (username,uid,gid,homedir,shell) values("$1, $3, $4,
> $6, $7");"|sqlcmd}

double_quotes inside brackets do not work >> parse error ,,,,

Quote:
>                         END {close(sqlcmd)}'
>         }' user=$user, pass=$pass, db=$db, table=$table

> Note that I'm closing the strings before all the variables and re-opening
> afterwards.

> By the way, from the contents of your password file it looks like you may
> be on a NIS system, if so then instead of "cat" you need to use "ypcat" to
> cat your password file, i.e.:

Thanks for this info.

Quote:
> ypcat passwd | awk ....

> Regards,

>     Ed.

Chris

--
Schaut doch einfach mal vorbei. Das Neue Informationsportal.
www.bietet.info



Wed, 23 Nov 2005 00:31:22 GMT  
 awk problem

Quote:


> > cat /etc/passwd | awk 'BEGIN {sqlcmd="mysql -u" user " -p" pass " -D" db}
> >                 BEGIN {FS=":";OFS=","}{print
> >                      "insert into " table "
> >                      (username,uid,gid,homedir,shell) values("$1, $3, $4,
> > $6, $7");"|sqlcmd}

> double_quotes inside brackets do not work >> parse error ,,,,

Yes they do, get rid of the commas that I put between the variable list. i.e.:

    user=$user pass=$pass $db table=$table

Regards,

    Ed.



Wed, 23 Nov 2005 00:57:47 GMT  
 awk problem

values("$1, $3, $4, $6, $7");"|sqlcmd}

my problem is here

values("$1, $3, $4, $6, $7");"|sqlcmd}

it doesn't matter what quotes I use i get everytime an error.

values("$1", "$3", "$4", "$6", "$7");"|sqlcmd}
values('$1', '$3', '$4', '$6', '$7');"|sqlcmd}

I think I tested everything but nothing works.With ',' and without them.

I don't know what to test else.[very frustrated]

Quote:

>     user=$user pass=$pass $db table=$table

The vars for user, table,.. I placed because I don't want to show real user
and passwd.

Thanks for your help.

Chris

--
Schaut doch einfach mal vorbei. Das Neue Informationsportal.
www.bietet.info



Wed, 23 Nov 2005 01:13:01 GMT  
 awk problem

Quote:


> values("$1, $3, $4, $6, $7");"|sqlcmd}

> my problem is here

> values("$1, $3, $4, $6, $7");"|sqlcmd}

> it doesn't matter what quotes I use i get everytime an error.

> values("$1", "$3", "$4", "$6", "$7");"|sqlcmd}

Just turns the , output-file separators into a bunch of "," strings.

Quote:

> values('$1', '$3', '$4', '$6', '$7');"|sqlcmd}

Can't use 's in awk.

Quote:

> I think I tested everything but nothing works.With ',' and without them.

> I don't know what to test else.[very frustrated]

> >     user=$user pass=$pass $db table=$table

> The vars for user, table,.. I placed because I don't want to show real user
> and passwd.

Oh. The "$"s threw me. Then ignore my stuff about setting variables (unless you
actually do want to be able to specify variables at some point) and instead try
putting double-quotes around each value inside the values expression, i.e.:

awk 'BEGIN {sqlcmd="mysql -u $user -p$pass -D $db"}
                        BEGIN {FS=":";OFS=","}{print
 "insert into $table (username,uid,gid,homedir,shell) values(\""$1"\"",
"\""$3"\"", "\""$4"\"", "\""$6"\"","\""$7"\");"|sqlcmd}
                        END {close(sqlcmd)}'

Regards,

    Ed.



Wed, 23 Nov 2005 02:05:22 GMT  
 awk problem


Quote:

>values("$1, $3, $4, $6, $7");"|sqlcmd}

>my problem is here

>values("$1, $3, $4, $6, $7");"|sqlcmd}

>it doesn't matter what quotes I use i get everytime an error.

>values("$1", "$3", "$4", "$6", "$7");"|sqlcmd}
>values('$1', '$3', '$4', '$6', '$7');"|sqlcmd}

>I think I tested everything but nothing works.With ',' and without them.

>I don't know what to test else.[very frustrated]

>>     user=$user pass=$pass $db table=$table

>The vars for user, table,.. I placed because I don't want to show real user
>and passwd.

If you're trying to print a double quote, pass it to awk as a variable

awk -v dq='"' '{print dq "text" dq }'

for example will print:

"text"

Chuck Demas

--
  Eat Healthy        |   _ _   | Nothing would be done at all,

  Die Anyway         |    v    | That no one could find fault with it.



Wed, 23 Nov 2005 02:55:42 GMT  
 awk problem

Quote:

> awk 'BEGIN {sqlcmd="mysql -u $user -p$pass -D $db"}
>                         BEGIN {FS=":";OFS=","}{print
>  "insert into $table (username,uid,gid,homedir,shell) values(\""$1"\"",
> "\""$3"\"", "\""$4"\"", "\""$6"\"","\""$7"\");"|sqlcmd}
>                         END {close(sqlcmd)}'

> Regards,

>     Ed.

Great, Great, Great, Great, Great, it works. Your so great.
Thanks a lot.
I would never had tested this version.

Thanks
Chris

--
Das Neue Informationsportal.   ->  http://www.bietet.info
Fr Vereine kostenloser Eintrag mit Veranstaltungen bis Ende 2004 !!

Alle Formulare/Vertr?ge sind online als PDF nachlesbar.
Feature fr Orte:  -> http://deidesheim.bietet.info



Wed, 23 Nov 2005 02:53:57 GMT  
 awk problem
<ver?ffentlicht & per Mail versendet>

Quote:




>>values("$1, $3, $4, $6, $7");"|sqlcmd}

>>my problem is here

>>values("$1, $3, $4, $6, $7");"|sqlcmd}

>>it doesn't matter what quotes I use i get everytime an error.

>>values("$1", "$3", "$4", "$6", "$7");"|sqlcmd}
>>values('$1', '$3', '$4', '$6', '$7');"|sqlcmd}

>>I think I tested everything but nothing works.With ',' and without them.

>>I don't know what to test else.[very frustrated]

>>>     user=$user pass=$pass $db table=$table

>>The vars for user, table,.. I placed because I don't want to show real
>>user and passwd.

> If you're trying to print a double quote, pass it to awk as a variable

> awk -v dq='"' '{print dq "text" dq }'

> for example will print:

> "text"

Thanks for your help, will give it a try and put it to my archive.

Chris

--
Das Neue Informationsportal.   ->  http://www.bietet.info
Fr Vereine kostenloser Eintrag mit Veranstaltungen bis Ende 2004 !!
Alle Formulare/Vertr?ge sind online als PDF nachlesbar.
Feature fr Orte:  -> http://deidesheim.bietet.info



Wed, 23 Nov 2005 02:56:39 GMT  
 awk problem

Quote:

> By the way, from the contents of your password file it looks like you may
> be on a NIS system, if so then instead of "cat" you need to use "ypcat" to
> cat your password file, i.e.:

> ypcat passwd | awk ....

There is no NIS system. May I 'cat' the password and store it in the
database, too. And use it out of database when I later
"recreate" user with 'useradd -p $password_from_database'

Is this possible?

Is it for this better to install NIS?

Thanks for your help.

Chris

--
Das Neue Informationsportal.   ->  http://www.bietet.info
Fr Vereine kostenloser Eintrag mit Veranstaltungen bis Ende 2004 !!
Alle Formulare/Vertr?ge sind online als PDF nachlesbar.
Feature fr Orte:  -> http://deidesheim.bietet.info



Wed, 23 Nov 2005 04:15:39 GMT  
 awk problem


% cat /etc/passwd | awk 'BEGIN {sqlcmd="mysql -u $user -p$pass -D $db"}
%                         BEGIN {FS=":";OFS=","}{print
%  "insert into $table (username,uid,gid,homedir,shell) values("$1, $3,
% $4, $6, $7");"|sqlcmd}
%                         END {close(sqlcmd)}'

Sorry, I goofed in my example. You need to put quotes around the
string values, which is a bit of a pain on the command-line, but
no trouble in a file

 pwtodb.awk:

 BEGIN {sqlcmd="mysql -u $user -p$pass -D $db"}
 BEGIN {FS=":";OFS=","}
 {print "insert into $table (username,uid,gid,homedir,shell) "\
        values('"$1 "'", $3, $4, "'"$6 "'", "'"$7"');"|sqlcmd}
 END {close(sqlcmd)}

run as
  awk -f pwtodb.awk /etc/passwd

Try printing without the |sqlcmd if there are still syntax errors here...
--

Patrick TJ McPhee
East York  Canada



Fri, 25 Nov 2005 05:25:59 GMT  
 
 [ 15 post ] 

 Relevant Pages 

1. Awk Problem: Changing Input Data Format

2. awk problem sorting and deduping file

3. simple awk problem

4. help with a tricky awk problem...

5. multiple regression using AWK Problem

6. AWK: Problem processing first line

7. awk problem

8. solved awk-problems

9. Small awk problem

10. Sharing a subtle awk problem

11. A shell/awk problem

12. awk problem

 

 
Powered by phpBB® Forum Software