DosTips.com

Posted: **29 Feb 2012 08:29**

I need to combine 2 files into one new one based on a common part, the IP address.

file1 : iplist.txt
"10.10.1.52","Person U","Location a"
"10.10.1.53","Person Q","Location a"
"10.10.1.54","Person X","Location b"
"10.10.1.55","Person Z","Location c"

file2 : sh_arp.txt
inside,10.10.1.52,0023.70eb.5315,3
inside,10.10.1.53,0023.71eb.3312,4
inside,10.10.1.54,0024.91c2.32b0,5
inside,10.10.1.55,39e4.dec3.234d,9

The code I used is this

Code: Select all

@echo off & setlocal enabledelayedexpansion

for /f "tokens=1,2,3 delims=," %%a in (iplist.txt) do (
    set ipa=%%a
    set nam=%%b
    set loc=%%c
    for /f "useback tokens=*" %%a in ('!ipa!') do set ipa=%%~a

    set ^"$CMD=find ",%%~a," sh_arp.txt^
    "
    for /f "tokens=1,2,3 delims=," %%a in ( '!$CMD!' ) do set mac=%%c
:    set nmac=%mac:~0,2%-%mac:~2,2%-%mac:~5,2%-%mac:~7,2%-%mac:~10,2%-%mac:~12,2%

    if not "!mac!" == "" echo !mac!,!nam!,!loc! > newlist.txt
)
goto:EOF

The remaining problem is that the mac address has the wrong format hence my inital attempt to turn the acbd.1234.5678 into ac-bd-12-34-56-78

Code: Select all

 set nmac=%mac:~0,2%-%mac:~2,2%-%mac:~5,2%-%mac:~7,2%-%mac:~10,2%-%mac:~12,2%

But right now I cant touch these vars, I need to use the ! to get to them in the echo statement

How could I get to the variable in the %var% notation again?

Thanks

Posted: **29 Feb 2012 18:12**

mich wrote:I need to combine 2 files into one new one based on a common part, the IP address.

file1 : iplist.txt
"10.10.1.52","Person U","Location a"
"10.10.1.53","Person Q","Location a"
"10.10.1.54","Person X","Location b"
"10.10.1.55","Person Z","Location c"

file2 : sh_arp.txt
inside,10.10.1.52,0023.70eb.5315,3
inside,10.10.1.53,0023.71eb.3312,4
inside,10.10.1.54,0024.91c2.32b0,5
inside,10.10.1.55,39e4.dec3.234d,9

What output format do you need?

Posted: **01 Mar 2012 06:05**

The output format should be like this.

00-23-70-eb-53-15,Person U,Location a

I solved my problem by doing the conversion of the mac address in a function.
Maybe/probably this is not optimal.

Code: Select all

@echo off
setlocal enabledelayedexpansion

REM set name of source files
set ipname=ip.csv
set macip=sh_arp.txt

for /f "tokens=1,2,3 delims=," %%a in (%ipname%) do (
    set ipa=%%a
    set nam=%%b
    set loc=%%c
    for /f "useback tokens=*" %%a in ('!ipa!') do set ipa=%%~a

    set ^"$CMD=find ",!ipa!," %macip%^
    "
    set mac=
    if NOT "!nam!" == "" for /f "tokens=1,2,3 delims=," %%d in ( '!$CMD!' ) do  set mac=%%f
    if NOT "!mac!" == "" call:funct1 !mac! !nam! !loc!

)
goto:EOF


:funct1
set bmac=%~1
set nam=%~2
set loc=%~3
set mac=%bmac:~0,2%-%bmac:~2,2%-%bmac:~5,2%-%bmac:~7,2%-%bmac:~10,2%-%bmac:~12,2%
echo %mac%,%nam%,%loc%
goto:EOF

Posted: **01 Mar 2012 08:35**

Optimal doesn't matter if it gets the job done.

Here's an alternate solution.

Code: Select all

@echo off
setlocal EnableExtensions EnableDelayedExpansion
for /f "tokens=1-3 delims=," %%a in (iplist.txt) do (
for /f "tokens=3 delims=," %%z in ('find %%a ^<"sh_arp.txt"') do (
set mac=%%z
set mac=!mac:~0,2!-!mac:~2,2!-!mac:~5,2!-!mac:~7,2!-!mac:~10,2!-!mac:~12,2!
echo !mac!,%%~b,%%~c
)
)
pause

Posted: **02 Mar 2012 00:50**

Thanks,

Need for speed is given by the length of the files....

I can't just match on the IP, it needs to be ",%ip%," as parameter for the find to get the matching done right, otherwise .1 matches .1, .10-10 and 100-199

Cheers,

Posted: **02 Mar 2012 01:45**

mich wrote:Thanks,

Need for speed is given by the length of the files....

I can't just match on the IP, it needs to be ",%ip%," as parameter for the find to get the matching done right, otherwise .1 matches .1, .10-10 and 100-199

ok,

In that last requirement you can change

find %%a

to

find ",%%~a,"

DosTips.com

need to work variables blocked by delayedexpansion (I think)

need to work variables blocked by delayedexpansion (I think)

Re: need to work variables blocked by delayedexpansion (I th

Re: need to work variables blocked by delayedexpansion (I th

Re: need to work variables blocked by delayedexpansion (I th

Re: need to work variables blocked by delayedexpansion (I th

Re: need to work variables blocked by delayedexpansion (I th