0

I am looking to create a batch file that will search a .csv for a specific number, and then use the 2nd value in the csv as the input back into the batch file.

Example:

csv name= IP.csv

.csv example

Store,IP
1000,192.168.1.1
2000,192.168.1.2
3000,192.168.1.3
4000,192.168.1.4
5000,192.168.1.5

Batch example

Set /p Store=Enter the Store number:

**Search the .csv for the store number, then reply with the IP address.**

I know this is extremely vague, but any guidance would be greatly appreciated.

Thank you!

3
  • findstr should get you started. – Der Hochstapler Aug 6 '13 at 19:12
  • Ewww, batch? Is there a specific reason batch? PowerShell really is the Windows best practice in 2013. – Austin T French Aug 6 '13 at 19:35
  • I guess there is no reason I would have to use batch, powershell would be a better alternative. But I don't have a single clue on where to start for powershell and I have ~650 lines written in the current batch that I am trying to bake this into – user2596575 Aug 6 '13 at 20:02
3

Very easily done with batch.

@echo off
setlocal
set /p "store=Enter the store number: "
for /f "tokens=2 delims=," %%A in ('findstr /r "^%store%," "ip.csv"') do echo %%A

The problem could become significantly more complicated for batch if the layout of your csv changes. For example, commas in column values creates issues that require more code to solve.

I've written a hybrid JScript/batch utility called REPL.BAT that could also make short work of this problem. It performs a regex search and replace on stdin and writes the result to stdout. It is pure script that runs on any Windows machine from XP onward - no exe download required. REPL.BAT is available here.. Full documentation is embedded within the script.

REPL.BAT has loads of options, including one that only writes out lines that are altered, making it ideal for this problem. Assuming REPL.BAT is in your current directory, or better yet, somewhere in your PATH:

@echo off
setlocal
set /p "store=Enter the store number: "
type ip.csv|repl "^%store%,(.*)" $1 a

REPL.BAT eliminates many complexities of working with text files in batch. But dealing with commas within csv column values is still tricky.

1
  • Thank you so much! I went with your REPL.BAT recommendation just for ease of being able to implement it into my current batch file. – user2596575 Aug 7 '13 at 12:42
1

Personally I think you should switch from batch to powershell to do it, parsing a csv is as simple as using the Import-CSV command

$storeTable = Import-CSV IP.csv

#build a hashtable from the data we imported so we can do easy lookups.
$storeLookup= @{}
foreach($r in $storeTable)
{
    $storeLookup[$r.Store] = $r.IP
}

$storeNumber= Read-Host "Enter the Store number:"
$storeIp = $storeLookup[$storeNumber]

#stop the script if no valid IP was provided
if($storeIp -eq $null)
    Return

#use $storeIp in the rest of the script to reference the ip address.
2
  • This was exactly what I would have posted, and *if $_.Store -eq $Store# – Austin T French Aug 6 '13 at 21:09
  • @user2596575 Now understand what you are using Set for, it acts just like your script now, prompting for the store number. – Scott Chamberlain Aug 6 '13 at 21:39
0

You might get some interesting answers using batch and PowerShell, so I'll take a shot at VBS. Name this file test.vbs and place it in the same directory as your files with Store and IP. In my case I called it test.csv. To run it

c:\> cscript /nologo test.vbs 2000
192.168.1.2
c:\> cscript /nologo test.vbs 1000
192.168.1.1

Here's the script. It looks long because of all the comments.

' test.csv sample:
'Store,IP
'1000,192.168.1.1
'2000,192.168.1.2
'3000,192.168.1.3
'4000,192.168.1.4
'5000,192.168.1.5

' Usage: 
' g:\> cscript /nologo test.vbs 1000
' will return 192.168.1.1

option explicit

' let's create a file system object to read file
dim fs
set fs = CreateObject("Scripting.FileSystemObject")

' let's define where the file sits
dim fil
set fil = fs.OpenTextFile("test.csv")

' let's count line numbers. Knowing that first line is for headers
' we know that we have to skip that line
dim counter, line, arr
counter = 0

' let's read line by line of the file
do while not fil.AtEndOfStream

    ' capture line and change the counter 
    line = fil.ReadLine
    counter = counter + 1

    ' only process data if we are past the first line. First line
    ' contains header anyway
    if counter > 1 then

        ' break the line into pieces. We know that each piece is separated by a comma
        ' e.g. 1000, 127.0.0.1
        arr = split(line, ",")

        ' Now arg will have two pieces. Assuming the example of 1000, 127.0.0.1
        ' arr(0) will be 1000 and
        ' arr(1) will be 127.0.0.1
        ' Let's compare arr(0) to the first command line argument to this program
        ' and return the corresponding arr(1) if there's a match
        if arr(0) = WScript.Arguments.Item(0) then
            WScript.Echo arr(1)
        end if

    end if
loop

' cleanup
fil.close
set fil = nothing
set fs = nothing

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.