csv file to array, match field1, replace in flat file field1 to field2

Hello, i am bit stuck with making script for automatic procedure.

Case:

Two files. One is flat file, other is csv file.
csv file has two column/fields with comma delimited data.

Here is what i need (explained way)

CSV file:

field1 | field2
"hello","byebye"
"hello2","byebye2"
"hello3","byebye3"

Flat file has matching data:

"hello"
"hello2"
"hello3"

If found:
"hello"
"hello2"
"hello3"
..in flat file, then it must be replaced from csv file with FIELD2 according
to search match.

But i have to search "hello" from csv file field1 data with flat file together and if found match in
flat file, it must be replaced from csv file with field2 data.

I know i could split csv file to two files, one containing field1 data and
second file field2 data but that would be not so dynamic way.
Though then i could easily use loop with cat + sed

Idea is to make nagios mass updating hosts naming changes in config.
So im wondering could i do it with awk or sed more universal way?

Any examples would be great help.

Thanks in advance..

Frankie

$ cat csv
field1 | field2
"hello","byebye"
"other","data"
"hello2","byebye2"
"hello3","byebye3"
"other","data"

$ cat flat
"hello"
"hello2"
"hello3"

 awk -F, 'NR==FNR { a[$0]; next } $1 in a { print $2 }' flat csv

Output:

"byebye"
"byebye2"
"byebye3"

grep -f flat csv | cut -d, -f2

Hello, thanks for reply but this only prints csv field2 data.

I need to search from csv file first field1 and same time from
flat file and if both csv and flat file have a match, then replace
founded match in flat file with csv field2 data.

here is what csv file contains:

cat test.csv

"hello","byebye"
"other","data"
"hello2","byebye2"
"hello3","byebye3"
"other","data"

Now flat file called nagios.cfg contains data:

define host{
        use                             generic-host
        host_name                    hello
        alias                             hello.hello
        address                         127.0.0.1
        contact_groups               mymail
        parents                         hello3
}

So if i found match "hello" in test.csv and nagios.cfg
then in nagios.cfg it needs to be replaced dynamic way
to "byebye" and "hello3" to "byebye3" from test.csv field2/colum

Need that way.. kinda hard to explain even:)

something to start with.
nawk -f fran.awk test.csv nagios.cfg

BEGIN {
  FScsv=","
  FScfg=FS

  qq=sprintf("%c", 034)
}
FNR==NR && FNR==1 {FS=FScsv;$1=$1}
FNR!=NR && FNR==1 {FS=FScfg; $1=$1}
FNR==NR {gsub(qq, "", $1);gsub(qq, "", $2); csv[$1]=$2; next}

!/{/ { if ($2 in csv) $2=csv[$2] }
1

Seems we are getting there:

did run your code.
output:

 nawk -f fran.awk test.csv nagios.cfg
define host{
        use                          generic-host
        host_name                    hello
        alias                        hello.hello
        address                      127.0.0.1
        contact_groups               mymail
parents byebye3
}

So parents which was hello3 got changed to right one from csv
field2. But other in host_name hello and in alias hello.hello didn't

Can you help me more with it, why didn't change?

thanks very much for any help..

frankie

this is what I got back given your sample test.csv file from above:

nawk -f fran.awk fran.csv fran.cfg
define host{
        use                             generic-host
host_name byebye
        alias                             hello.hello
        address                         127.0.0.1
        contact_groups               mymail
parents byebye3
}

The 'alias hello.hello' didn't change because you had no matching entry in 'test.csv' file for it.

Hmm then im stuck with new problem. In my case host_name didn't change but it had match in test.csv Did you made some changes in your code?

In alias field.. I am kinda bad with all english words..
Lets assume alias has name hello.someothername then hello needs to be changed as well.And i have issue also with commas in nagios use field.

They could be also hello,hello3,dontchange,hello and need to be replaced
but not "dontchange"

Frankie

No, I didn't change anything - make sure you don't have any extraneous spaces/tabs and your test.csv looks like this:

"hello","byebye"
"other","data"
"hello2","byebye2"
"hello3","byebye3"
"other","data"

Use code-tags when posting data/code samples.

Try the code below:

BEGIN {
  FScsv=","
  FScfg=FS

  SEPdom="."

  qq=sprintf("%c", 034)
}
FNR==NR && FNR==1 {FS=FScsv;$1=$1}
FNR!=NR && FNR==1 {FS=FScfg; $1=$1}
FNR==NR {gsub(qq, "", $1);gsub(qq, "", $2); csv[$1]=$2; next}

!/{/ {
    n=split($2, _a, SEPdom)
    for(i=1; i<=n; i++)
      if (_a in csv) _a=csv[_a]
    for(i=1; i<=n; i++)
      $2=(i==1) ? _a : $2 SEPdom _a
}
1

I don't understand the above. Please provide a sample and a desired output using code-tags.

This way:

cat test.csv

"hello","byebye"
"other","data"
"hello2","byebye2"
"hello3","byebye3"
"other","data"

cat nagios.cfg

define host{
        use                          generic-host
        host_name                    hello
        alias                        hello.hello
        address                      127.0.0.1
        contact_groups               mymail
        parents                      hello3
}

cat service.cfg

define service{
        use                                  generic-service
        service_description             TCP_80
        host_name                        hello,hello2,hello3,nochangematch
        contact_groups                  
        check_command                 
}

Everything what matches from test.csv (column1) with nagios.cfg or services.cfg must be replaced from test.csv column2 data but as you see in services.cfg can be host_name with many hosts. Those much be
matched as well and be replaced and cases with hello.nomatch must
be matched to byebye.nomatch

Last code what you provided still printed only change in parents field.
For you to be sure, i attached my files. Had rename them to .txt
files.

nawk -f fran2.awk test.csv nagios.cfg

define host{
use generic-host
host_name hello
alias hello.hello
address 127.0.0.1
contact_groups mymail
parents byebye3
}

Think we are we close. I just have to explain better.

Thanks again

try this:
# for 'nagios.cfg'
nawk -v sep='.' -f fran.awk test.txt nagios.cfg

# for 'services.cfg'
nawk -v sep=',' -f fran.awk test.txt services.cfg

fran.awk:

BEGIN {
  FScsv=","
  FScfg=FS

  if (sep="") sep="."

  qq=sprintf("%c", 034)
}
FNR==NR && FNR==1 {FS=FScsv;$1=$1}
FNR!=NR && FNR==1 {FS=FScfg; $1=$1}
FNR==NR {gsub(qq, "", $1);gsub(qq, "", $2); csv[$1]=$2; next}

!/{/ {
    n=split($2, _a,sep)
    for(i=1; i<=n; i++)
      if (_a in csv) _a=csv[_a]
    for(i=1; i<=n; i++)
      $2=(i==1) ? _a : $2 sep _a
}
1

Odd, now changes nothing

nawk -v sep='.' -f fran3.awk test.csv.txt nagios.cfg.txt

define host{
use generic-host
host_name hello
alias hello.hello
address 127.0.0.1
contact_groups mymail
parents hello3
}

nawk -v sep=',' -f fran3.awk test.csv.txt services.txt

 
define service{
use generic-service
service_description TCP_80
host_name hello,hello2,hello3,nochangematch
        contact_groups                  
        check_command                
}

sorry - my bad:

BEGIN {
  FScsv=","
  FScfg=FS

  if (sep=="") sep="."

  qq=sprintf("%c", 034)
}
FNR==NR && FNR==1 {FS=FScsv;$1=$1}
FNR!=NR && FNR==1 {FS=FScfg; $1=$1}
FNR==NR {gsub(qq, "", $1);gsub(qq, "", $2); csv[$1]=$2; next}

!/{/ {
    n=split($2, _a,sep)
    for(i=1; i<=n; i++)
      if (_a in csv) _a=csv[_a]
    for(i=1; i<=n; i++)
      $2=(i==1) ? _a : $2 sep _a
}
1

Almost works:

nawk -v sep=',' -f fran4.awk test.csv.txt services.txt

define service{
use generic-service
service_description TCP_80
host_name hello,byebye2,byebye3,nochangematch
        contact_groups                  
        check_command                
}

First hello didn't get changed.

And this one:

nawk -v sep='.' -f fran4.awk test.csv.txt nagios.cfg.txt

define host{
use generic-host
host_name hello
alias hello.hello
address 127.0.0.1
contact_groups mymail
parents byebye3
}

Changed only parents field. Could you help me a bit more to get it full working. thank you very much for so far..

F.

given fran.csv:

"hello","byebye"
"other","data"
"hello2","byebye2"
"hello3","byebye3"
"other","data"

and services.txt:

define service{
use generic-service
service_description TCP_80
host_name hello,hello2,hello3,nochangematch
        contact_groups
        check_command
}

nawk -v sep=',' -f fran.awk fran.csv services.txt
produces:

define service{
use generic-service
service_description TCP_80
host_name byebye,byebye2,byebye3,nochangematch
        contact_groups
        check_command
}

given nagios.cfg

define host{
use generic-host
host_name hello
alias hello.hello
address 127.0.0.1
contact_groups mymail
parents hello3
}

running:
nawk -v sep='.' -f fran.awk fran.csv nagios.cfg
produces:

define host{
use generic-host
host_name byebye
alias byebye.byebye
address 127.0.0.1
contact_groups mymail
parents byebye3
}

Check all your files against my samples - it all works for me.

Very weird. Checked, proof:

cat fran4.awk

BEGIN {
  FScsv=","
  FScfg=FS

  if (sep=="") sep="."

  qq=sprintf("%c", 034)
}
FNR==NR && FNR==1 {FS=FScsv;$1=$1}
FNR!=NR && FNR==1 {FS=FScfg; $1=$1}
FNR==NR {gsub(qq, "", $1);gsub(qq, "", $2); csv[$1]=$2; next}

!/{/ {
    n=split($2, _a,sep)
    for(i=1; i<=n; i++)
      if (_a in csv) _a=csv[_a]
    for(i=1; i<=n; i++)
      $2=(i==1) ? _a : $2 sep _a
}
1

cat test.csv

"hello","byebye"
"other","data"
"hello2","byebye2"
"hello3","byebye3"
"other","data"

cat services.txt

define service{
        use                             generic-service
        service_description             TCP_80
        host_name                       hello,hello2,hello3,nochangematch
        contact_groups                  
        check_command                
}

Running code:

nawk -v sep=',' -f fran4.awk test.csv services.txt

define service{
use generic-service
service_description TCP_80
host_name hello,byebye2,byebye3,nochangematch
        contact_groups                  
        check_command                
}

Didn't change first hello

cat nagios.cfg.txt

define host{
        use                          generic-host
        host_name                    hello
        alias                        hello.hello
        address                      127.0.0.1
        contact_groups               mymail
        parents                      hello3
}

Running code:

nawk -v sep='.' -f fran4.awk test.csv nagios.cfg.txt

define host{
use generic-host
host_name hello
alias hello.hello
address 127.0.0.1
contact_groups mymail
parents byebye3
}

Changed only parents field.

Is fran4.awk latest code, matches yours?

Just in case did for all files dos2unix as well and:

file -i fran4.awk test.csv nagios.cfg.txt services.txt

fran4.awk:      text/plain charset=us-ascii
test.csv:       text/plain charset=us-ascii
nagios.cfg.txt: text/plain charset=us-ascii
services.txt:   text/plain charset=us-ascii

strange, your awk code is the same as mine.
hmmm... - it must be something in the data files.
couxld you 'cat -vet nagios.cfg.txt' and 'cat -vet test.csv' and post the results here within the code tags, pls.

cat -vet nagios.cfg.txt

define host{$
        use                          generic-host$
        host_name                    hello$
        alias                        hello.hello$
        address                      127.0.0.1$
        contact_groups               mymail$
        parents                      hello3$
}$

cat -vet test.csv

"hello","byebye"$
"other","data"$
"hello2","byebye2"$
"hello3","byebye3"$
"other","data"$

cat -vet services.txt

 
define service{$
        use                             generic-service$
        service_description             TCP_80$
        host_name                       hello,hello2,hello3,nochangematch$
        contact_groups                  $
        check_command                $
}$

cat -vet fran4.awk

 
BEGIN {$
  FScsv=","$
  FScfg=FS$
$
  if (sep=="") sep="."$
$
  qq=sprintf("%c", 034)$
}$
FNR==NR && FNR==1 {FS=FScsv;$1=$1}$
FNR!=NR && FNR==1 {FS=FScfg; $1=$1}$
FNR==NR {gsub(qq, "", $1);gsub(qq, "", $2); csv[$1]=$2; next}$
$
!/{/ {$
    n=split($2, _a,sep)$
    for(i=1; i<=n; i++)$
      if (_a in csv) _a=csv[_a]$
    for(i=1; i<=n; i++)$
      $2=(i==1) ? _a : $2 sep _a$
}$
1$

hmm...... this is bizarre - works just fine here. Looks like you're having issues with the lookup of "hello" in the test.csv file.
could change nagios.cfg.txt like so:

define host{
        use                          generic-host
        host_name                    other
        alias                        other.other
        address                      127.0.0.1
        contact_groups               mymail
        parents                      hello3
}

and:
nawk -v sep='.' -f fran4.awk test.csv nagios.cfg.txt

Yep, if changing to "other" then works

cat nagios.cfg.txt

define host{
        use                          generic-host
        host_name                    other
        alias                        other.other
        address                      127.0.0.1
        contact_groups               mymail
        parents                      hello3
}

nawk -v sep='.' -f fran4.awk test.csv nagios.cfg.txt

define host{
use generic-host
host_name data
alias data.data
address 127.0.0.1
contact_groups mymail
parents byebye3
}

Works, like a champ

Then again with this concept:

cat nagios.cfg.txt

define host{
        use                          generic-host
        host_name                    hello
        alias                        other.hello2
        address                      127.0.0.1
        contact_groups               mymail
        parents                      hello3
}

nawk -v sep='.' -f fran4.awk test.csv nagios.cfg.txt

define host{
use generic-host
host_name hello
alias data.byebye2
address 127.0.0.1
contact_groups mymail
parents byebye3
}

You see, hello in host_name field didn't change.
all other fields okey.

This is like my awk hates "hello". It want's it to be "hell'
Just joking.. but seriously.. Very weird case.