SCRIPT

[vmsserbo]

I need to write a script to define over 200 logicals from an excel spread sheet. Without me doing it manually, is there a script I can use or write to do this faster.

I know I have to use a:
define/system remote$chk11-100 remotechk11


I have attached the actual request from one of our users.

[John Gillings · Accepted solution]

Miles,

Since the values are already in Excel, I'd suggest you use Excel to generate your commands, then save the file as tab delimited text. For example, working from your spread sheet:

1) Create a new worksheet
2) select the cells remote$chk11-100 to remote$chk11-980 and COPY
3) PASTE to B1 in the new, empty sheet
4) COPY & PASTE or just type remote$chk11 into C1 in the new sheet
5) replicate the cell to the same length as the column B data (select the cell and drag the little square)
6) type "$ DEFINE/SYSTEM" into column A and replicate it to the end of column B

Now repeat those steps for each of the other columns.

You might be able to speed things up by creating an Excel macro to chop out the logical name lists (rows 4 through 28 of your spreadsheet)

Save the file as TAB delimited text, and transfer to OpenVMS for execution. TAB is a perfectly valid delimiter in DCL.

That said, I'd recommend against putting all those logical names in the SYSTEM table. Maybe you should place them in a shareable table of their own and place it in LNM$FILE_DEV *after* the system table. That way you won't impact normal logical name translations (at least successful ones).

Another possibility is to write a DCL loop listing your 100, 150, 170... sequence with a loop variable from 14 through to 30

$ v=14
$ loop:
$ DEFINE/SYSTEM remote$chk'v'-100 remote$chk'v'
$ DEFINE/SYSTEM remote$chk'v'-150 remote$chk'v'
$ DEFINE/SYSTEM remote$chk'v'-170 remote$chk'v'
...
$ DEFINE/SYSTEM remote$chk'v'-980 remote$chk'v'
$ v=v+1
$ IF v.LT.30 THEN GOTO loop

(there doesn't seem to be any simple pattern to the tag values, otherwise it would just be a nested loop)

[vmsserbo]

Thanks!

[Klaes-Göran Carlsson]

Why spend days on excel when you can do it in some milliseconds in vms?

So, copy/paste the info into a vms file and then use vms edit. Something like:

edit file.com

learn
adjust the line to your needs
ctrl+R


...then just press f9 200 times or


repeat
no of times: 200

[IFX_1]

You can use the "concatenate" function of EXCEL to help write the script faster.

I attach the sample spreadsheet. Once you completed it, you can just do the following:

1. do EDIT a new file ie. $ edit logic.com
2. Copy and paste the data from excel sheet which you did using "concatenate".
3. Save the logic.com
4. Execute it. $@logic.com

regards,
ronald

[Robert Gezelter]

Miles,

I have taken a look at your attached spreadsheet, and I would suggest taking a step backward here.

First, to do a brute force definition of the logical names mentioned, I would export the Excel file into either a tab-delimited or comma delimited (CSV) form, then export that text file to the OpenVMS system. I would then process the file as a data file (OPEN/READ; READ; ...) parse the lines and work that way. I would not recommend transforming it using the editor since this type of request tends to change over time.

What I do question is why these names need to be available globally. Your posting is somewhat thin as to how these names are to be used. For example, if the names are to be used as printers by different groups, then a different structure is very much more appropriate.

More information would help you get a more effective approach.

- Bob Gezelter, http://www.rlgsc.com