Batch file to read from CSV and output multiple files

[ShaneC]

Hello all, and a first poster here.  I'll be upfront and say I haven't really messed with batch files since editing Autoexec.bat in the 90s. 

Yes, the project I'm working on is technically "for work" but its to make my own life easier, more so than being for anyone's profit.  I'm a man of many hats, so I should be out pulling wire today, but I really need to make some progress on my little project so I'm ready for next week.

Anyway, there's my intro, here's what I'm trying to accomplish:

list.csv contains about 120 lines.   Column A is a text string defining a location.  Column B is an IP address.

What I'm looking for as an end result is to create two files.

1.   filename.bat that has the string:  "tst10.exe /r:filename.txt /o:bench.txt"  Where filename of both the .bat and the .txt are generated from Column A.

2.   filename.txt that has a unique IP string inside that's generated from column B.


Filename.txt will contain the following:

Code: [Select]

192.168.1.2 20000
WAIT "username:"
SEND "name\m"
WAIT "password:"
SEND "pass\m"
WAIT ":"
SEND "command\m"

Where the filename is created from CSV column A, and the IP inside the file is generated from Column B.  Everything else is static and will be the same throughout.

What I have so far:

Code: [Select]

SET filename=
For /F "delims=, tokens=2" %%G IN (list.txt) DO SET filename=%%G
echo %filename% > "%filename%.bat"

This outputs Column A of each line on the screen, and only saves a .bat of the last entry of the CSV.

Obviously, I'm missing alot, and scripting is not my forte.

Any help is GREATLY appreciated.  I'm a bit out of my depth on this one, and don't want to have to do all 120ish connections long-form/by hand or try to entrust someone else to do the remote connections and risk breaking anything.

[Rob Pomeroy]

Have you come across PowerShell?  Much more powerful string parsing capabilities.

[ShaneC]

Hadn't even heard of it, but after looking at the wikipedia page for it, I think I might look into it more in depth.

[Squashman]

Am I undestanding you correctly that you want a batch file that creates another batch file as well as a text file?

I am not really following your logic but if you could post a few lines of the list.txt file and show me the intended output I am sure we could figure it out.

[Rob Pomeroy]

Hadn't even heard of it, but after looking at the wikipedia page for it, I think I might look into it more in depth.

Well worth it.  Once you've got your head around its peculiar syntax, you'll appreciate that power.  It blows the command prompt out of the water.  Here are a couple of quick-starts for string functions:

http://technet.microsoft.com/en-us/library/ee692804.aspx
http://powershell.com/cs/blogs/tips/archive/2009/06/08/using-string-functions.aspx

[Squashman]

A couple of years ago I started working on PowerShell for a few solid months and then got to caught up in life and stopped.  It definitely has its advantages but I did run into a few problems that still could only be solved with a batch file.  But I did like that I could create graphical Interfaces for my powershell scripts.  That was pretty cool.  I had actually started to try and convert some of my batch files into PowerShell scripts.  We had even figured out how to do drag and drop input onto a powershell script.

[ShaneC]

Am I undestanding you correctly that you want a batch file that creates another batch file as well as a text file?

I am not really following your logic but if you could post a few lines of the list.txt file and show me the intended output I am sure we could figure it out.

Correct on that I'm wanting to use a batch file to create additional batch files and text files.  Essentially I have 121 specialized systems that I need to migrate from a routed network to a VLAN.  Before I can change the IPs, I need to change the encryption keys. 

To do so, I need to Telnet into each device, login to it, issue some special commands, then leave the connection open while sending another batch of commands from a central server.  Once the commands from the central device are processed, then I can close out the remote connections.

So the inital batch file would read from list.csv

(list.txt & list.csv are the same file, just different extensions, both using a , for the delims. - I typo'd the original post)

Code: [Select]

Bldg 123 Rm 456,10.1.28.171
Bldg 789 Rm 12,10.1.50.20

and create Bldg 123 Rm 456.bat  and Bldg 123 Rm456.txt

Bldg 123 Rm 456.bat would contain the following:

Code: [Select]

"tst10.exe /r:Bldg 123 Rm456.txt /o:bench.txt"

(note:  /o:bench.txt is the output log of the tst10 program, but the log file itself isn't necessary for me to keep, though I believe the switch needs to be there)

and  Bldg 123 Rm456.txt would contain:

Code: [Select]

10.1.28.171 20000
WAIT "username:"
SEND "name\m"
WAIT "password:"
SEND "pass\m"
WAIT ":"
SEND "command\m"
IP is the only thing that changes here.  All other text is the same across all devices.

So in the end, I'd end up with 121 batch files, each with the TST10.exe shortcut/parameters, and 121 text files as the command script that will be executed on the remote devices.


I did look into Polyscript for this, but it didn't seem to work on the bench test.  TST10 worked, so it looks like that's the route i'm going.

[Squashman]

Code: [Select]

For /F "tokens=1,2 delims=," %%G IN (list.txt) DO (
echo "tst10.exe /r:%%G.txt /o:bench.txt">"%%G.bat"
echo %%H 20000>"%%G.txt"
echo WAIT "username:">>"%%G.txt"
echo SEND "name\m">>"%%G.txt"
echo WAIT "password:">>"%%G.txt"
echo SEND "pass\m">>"%%G.txt"
echo WAIT ":">>"%%G.txt"
echo SEND "command\m">>"%%G.txt"
)

[ShaneC]

SWEET!  That looks perfect!

I'll test it out later today to make sure.  Now as long as I didn't fat finger anything on my end, I should be golden.

Thank you so, so much!

[ShaneC]

Oh, I have to ask, cuz this is the one big thing that I've had a hard time wrapping my brain around:



%%G is column A, and %%H is Column B....but why?  What defines it?


My guess at this point would be that the first line with %%G IN (list.txt) sets it up so that %%G = A1, therefore %%H would be A2 and it just works its way down the line, but.....well, is that correct?

[ShaneC]

one flaw, now fixed:

Code: [Select]

For /F "tokens=1,2 delims=," %%G IN (list.txt) DO (
   echo tst10.exe /r:%%G.txt /o:bench.txt>"%%G.bat"
   echo %%H 20000>"%%G.txt"
   echo WAIT "username:">>"%%G.txt"
   echo SEND "name\m">>"%%G.txt"
   echo WAIT "password:">>"%%G.txt"
   echo SEND "pass\m">>"%%G.txt"
   echo WAIT ":">>"%%G.txt"
   echo SEND "command\m">>"%%G.txt"
)


Was:  echo "tst10.exe /r:%%G.txt /o:bench.txt">"%%G.bat"
The quotes inside the batch file didn't play nice.  =D

[Salmon Trout]

%%G is column A, and %%H is Column B....but why?  What defines it?

What defines it is the "tokens=1,2" part of the tokens/delims block of the FOR statement. If you use tokens you can create potential "implicit" variables that follow the letter you choose for the explicit FOR variable. They are called implicit variables because they are implied by the tokens= section. This part: "tokens=1,2 delims=," %%G tells FOR: "Consider each line read in from list.txt to be a series of tokens separated (delimited) by commas. Assign token number 1 to %%G and assign token number 2 to a variable consisting of two percent signs and the next letter after G (i.e. %%H).

"tokens=1-2" means the same as "tokens=1,2".

It's all in the FOR documentation which you can access by typing FOR /? at the prompt.

tokens   1     2      3       4
string horse,tiger,elephant,sheep
         ^     ^
         |     |
        %%G   %%H