Best way to setup a "Dictionary" similar to VBA?

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Message
Author
SIMMS7400
Posts: 539
Joined: 07 Jan 2016 07:47

Best way to setup a "Dictionary" similar to VBA?

#1 Post by SIMMS7400 » 29 Jan 2021 11:36

Hi Folks -

I have a situation where I am loading files to a target Oracle system. The first two letters of the data file dictate the Target application.

Rather than extracting the first two letters of the data file then cycling through a bunch of IF ELSE IF logic, is there a way to create a mapping/dictionary that I could access dynamically based on the first two letters of a data file name?

For instance, if the first two letters of the data file name are AA, the Target application is "Application1". If the first two letters of the data file name are BB, the Target Application is "Application2". Basically looking for a way to setup a library that I can access it at any point in the script.

Thank you!

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Best way to setup a "Dictionary" similar to VBA?

#2 Post by Aacini » 29 Jan 2021 12:36

Simple. Just use an array:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Define the set of applications
set "app[AA]=Application1"
set "app[BB]=Application2"

set /P "dataFile=Enter data file: "
echo The application is: !app[%dataFile:~0,2%]!
Antonio

miskox
Posts: 553
Joined: 28 Jun 2010 03:46

Re: Best way to setup a "Dictionary" similar to VBA?

#3 Post by miskox » 29 Jan 2021 13:17

Another way would be to use a dictionary file:

dictionary.txt

Code: Select all

AA#application1
BB#application2
CC#application3
DD#application4
and then you could use FINDSTR /B:

Code: Select all

@echo off
set par=%1
findstr /B "%par%#" dictionary.txt>tmp.tmp
set /p "appname="<tmp.tmp
for /F "tokens=2 delims=#" %%f in ("%appname%") do set appname=%%f
echo appname=%appname%
del tmp.tmp
Start it with parameter AA to get app1.

Saso

SIMMS7400
Posts: 539
Joined: 07 Jan 2016 07:47

Re: Best way to setup a "Dictionary" similar to VBA?

#4 Post by SIMMS7400 » 29 Jan 2021 19:10

Aacini wrote:
29 Jan 2021 12:36
Simple. Just use an array:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Define the set of applications
set "app[AA]=Application1"
set "app[BB]=Application2"

set /P "dataFile=Enter data file: "
echo The application is: !app[%dataFile:~0,2%]!
Antonio
Thank you Antonio! This is great, didn't realize it was so easy.

One additional question - is there a way to set two value that can be accessed by the array? For instance, if the first two letters of the data file name are AA, the Target application is "Application1" and Target Entity is "Entity1". If the first two letters of the data file name are BB, the Target Application is "Application2" and Target Entity is "Entity2"? Or do I have to setup a new array?

Thanks!

ShadowThief
Expert
Posts: 1160
Joined: 06 Sep 2013 21:28
Location: Virginia, United States

Re: Best way to setup a "Dictionary" similar to VBA?

#5 Post by ShadowThief » 29 Jan 2021 21:23

Historically, I've done something like

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Define the set of applications
set "app[AA].target_application=Application1"
set "app[AA].target_entity=Entity1"
set "app[BB].target_application=Application2"
set "app[BB].target_entity=Entity2"

set /P "dataFile=Enter data file: "
echo The application is: !app[%dataFile:~0,2%].target_application!
echo The entity is: !app[%dataFile:~0,2%].target_entity!
But at that point it can get complicated if the list gets too long and it's easy to forget that if you do it this way then !app[AA]! by itself won't have a value, so it's your call as to whether or not you'd rather just have a second array.

T3RRY
Posts: 243
Joined: 06 May 2020 10:14

Re: Best way to setup a "Dictionary" similar to VBA?

#6 Post by T3RRY » 30 Jan 2021 02:25

I have a preference for using macro's for tasks like this.

Code: Select all

@Echo off
(For /F "Tokens=1,2 Delims==" %%I in ('Set "."')Do Set "%%I=") 2> nul
(Set \n=^^^

%= newline var Do Not Modify =%)
Set Def.Array=For %%n in (1 2)Do If %%n==2 (%\n%
 For /F "Tokens=1,2,3 Delims={}" %%1 in ("!Obj.List!")Do (%\n%
  Set Source="%%~1"%\n%
  If exist !Source! For /F "UsebackQ Delims=" %%R in (!Source!)Do (%\n%
   Set "Obj.List=%%R"%\n%
   For /F "Tokens=1,2,3 Delims={}" %%1 in ("!Obj.List!")Do For %%e in (%%3)Do (%\n%
    Set /A ".%%~1{i}+=1+0"%\n%
    Set ".App[%%~1][!.%%~1{i}!]=%%~2"%\n%
    Set ".Ent[%%~1][!.%%~1{i}!]=%%~e"%\n%
   )%\n%
  )Else (%\n%
   For %%e in (%%3)Do (%\n%
    Set /A ".%%~1{i}+=1+0"%\n%
    Set ".App[%%~1][!.%%~1{i}!]=%%~2"%\n%
    Set ".Ent[%%~1][!.%%~1{i}!]=%%~e"%\n%
   )%\n%
  )%\n%
 )%\n%
)Else Set Obj.List=

Set Extract=For %%n in (1 2)Do If %%n==2 (%\n%
 For /F "Tokens=1,2,3 Delims={}" %%1 in ("!Array.Item!")Do (%\n%
  If not "!.App[%%1][%%2]!" == "" (%\n%
   Echo/!.App[%%1][%%2]! !.Ent[%%1][%%2]!%\n%
   If not "%%~3" == "" (Set "%%~3=!.App[%%1][%%2]! !.Ent[%%1][%%2]!") %\n%
  )Else (Echo/Index not defined.)%\n%
 )%\n%
)Else Set Array.Item=

Setlocal EnableExtensions EnableDelayedExpansion

%Def.Array%{AA}{application1}{"entity 1" "entity 2" "entity 3"}
%Def.Array%{AB}{application2}{"entity 4" "entity 5" "entity 6" "entity 7"}
%Def.Array%{AC}{application3}{"entity 8" "entity 9"}
%Extract%{AB}{2}{.returnVar}
Set .
Endlocal
Goto :Eof

the above example can also be used to read source files such as:

Code: Select all

{AA}{application1}{"entity 1" "entity 2" "entity 3"}
{AB}{application2}{"entity 4" "entity 5" "entity 6" "entity 7"}
{AC}{application3}{"entity 8" "entity 9"}
by expanding Def.Array likle So:

Code: Select all

%Def.array%{sourcefilepath.ext}

SIMMS7400
Posts: 539
Joined: 07 Jan 2016 07:47

Re: Best way to setup a "Dictionary" similar to VBA?

#7 Post by SIMMS7400 » 30 Jan 2021 03:43

ShadowThief wrote:
29 Jan 2021 21:23
Historically, I've done something like

Code: Select all

@echo off
setlocal EnableDelayedExpansion

rem Define the set of applications
set "app[AA].target_application=Application1"
set "app[AA].target_entity=Entity1"
set "app[BB].target_application=Application2"
set "app[BB].target_entity=Entity2"

set /P "dataFile=Enter data file: "
echo The application is: !app[%dataFile:~0,2%].target_application!
echo The entity is: !app[%dataFile:~0,2%].target_entity!
But at that point it can get complicated if the list gets too long and it's easy to forget that if you do it this way then !app[AA]! by itself won't have a value, so it's your call as to whether or not you'd rather just have a second array.
Hi Shadow -

Thanks so much! I really that approach and will use it. I do have one question...I'm trying to do some string manipulation on the data file name to extra the 6 characters that represent date. What I try to add the array to the string manipulation, it just returns the positions, rather than the value. How would I go about doing this?

Thanks!

Code: Select all

::-- Definitions --::
    ::DLR = Data Management Load Rule
    ::ENTITY = FCCS Entity
    ::FDMEE_INBOX = File upload repository
    ::DSP = Position where YYYYMM starts in data file name
    ::DEP = Position where YYYYMM ends in data file name

SET "STR[AE].DLR=UAE DLR"
SET "STR[AE].ENTITY=AE10"
SET "STR[AE].FDMEE_INBOX=inbox/APAC-AE"
SET "STR[AE].DSP=9"
SET "STR[AE].DEP=4"

SET "DATAFILENAME=AEGLBAL_12008_030920172034.csv"

echo DLR:                    !STR[%DATAFILENAME:~0,2%].DLR!
echo Entity is:              !STR[%DATAFILENAME:~0,2%].ENTITY!
echo FMDEE Inbox is:         !STR[%DATAFILENAME:~0,2%].FDMEE_INBOX!
echo Date Start position is: !STR[%DATAFILENAME:~0,2%].DSP!
echo Date End position is:   !STR[%DATAFILENAME:~0,2%].DEP!

echo Date is : %DATAFILENAME:~9,4%
echo Date is : %DATAFILENAME:~!STR[%DATAFILENAME:~0,2%].DSP!,!STR[%DATAFILENAME:~0,2%].DEP!%

pause

T3RRY
Posts: 243
Joined: 06 May 2020 10:14

Re: Best way to setup a "Dictionary" similar to VBA?

#8 Post by T3RRY » 30 Jan 2021 05:02

SIMMS7400 wrote:
30 Jan 2021 03:43

Thanks so much! I really that approach and will use it. I do have one question...I'm trying to do some string manipulation on the data file name to extra the 6 characters that represent date. What I try to add the array to the string manipulation, it just returns the positions, rather than the value. How would I go about doing this?

Code: Select all

 %DATAFILENAME:~!STR[%DATAFILENAME:~0,2%].DSP!,!STR[%DATAFILENAME:~0,2%].DEP!%
Thr output is exactly what you should be expecting. Whenever you expand a variable, the expansion will terminate for that component of the string at the first matching expansion character.
In the above examples, the string expands as the following variables when DelayedExpansion is Enabled:

Code: Select all

variable: %DATAFILENAME:~!STR[% = no assigned value
string:  DATAFILENAME:~
variable: !STR[%DATAFILENAME:~0,2%].DSP! = !STR[AE].DSP! = 9
string: ,
variable: !STR[%DATAFILENAME:~0,2%].DEP! = !STR[AE].DEP! = 4
rem final resulting string:
DATAFILENAME:~9,4
To resolve this, the array variable must be expanded using delayed expansion, and each component variable of the Array Variable must be expanded with standard percent expansion or, when the component is itself an array variable by using a For metavariable:

Code: Select all

For %%E in ("!STR[%DATAFILENAME:~0,2%].DSP!,!STR[%DATAFILENAME:~0,2%].DEP!")Do (
 Echo Date is : !DATAFILENAME:~%%~E!
)

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Best way to setup a "Dictionary" similar to VBA?

#9 Post by Aacini » 31 Jan 2021 22:32

You may use this simpler approach:

Code: Select all

@echo off
setlocal EnableDelayedExpansion

::-- Definitions --::
    ::DLR = Data Management Load Rule
    ::ENTITY = FCCS Entity
    ::FDMEE_INBOX = File upload repository
    ::DSP = Position where YYYYMM starts in data file name
    ::DEP = Position where YYYYMM ends in data file name

rem Define the set of applications (one per line)
rem             DLR,  ENTITY,FDMEE_INBOX,DSP,DEP
for %%a in ("AE,UAE DLR,AE10,inbox/APAC-AE,9,4"
            "BB,UBB DLR,BB20,inbox/APAC-BB,9,4") do (
   for /F "tokens=1-6 delims=," %%A in (%%a) do (
      SET "STR[%%A].DLR=%%B"
      SET "STR[%%A].ENTITY=%%C"
      SET "STR[%%A].FDMEE_INBOX=%%D"
      SET "STR[%%A].DSP=%%E"
      SET "STR[%%A].DEP=%%F"
   )
)

SET "DATAFILENAME=AEGLBAL_12008_030920172034.csv"

echo DLR:                    !STR[%DATAFILENAME:~0,2%].DLR!
echo Entity is:              !STR[%DATAFILENAME:~0,2%].ENTITY!
echo FMDEE Inbox is:         !STR[%DATAFILENAME:~0,2%].FDMEE_INBOX!
echo Date Start position is: !STR[%DATAFILENAME:~0,2%].DSP!
echo Date End position is:   !STR[%DATAFILENAME:~0,2%].DEP!

echo Date is : %DATAFILENAME:~9,4%
call echo Date is : %%DATAFILENAME:~!STR[%DATAFILENAME:~0,2%].DSP!,!STR[%DATAFILENAME:~0,2%].DEP!%%

pause
Antonio

SIMMS7400
Posts: 539
Joined: 07 Jan 2016 07:47

Re: Best way to setup a "Dictionary" similar to VBA?

#10 Post by SIMMS7400 » 01 Feb 2021 03:02

HI Antonio -

Thank you for that suggest. I really do like how you're setting the array definition, nice and clean. One thing I'm running into is that I do have situation where there are multiple Entities which are comma delimited. How would I include them? Do I include them with another delimters and then do a "replace" when I need to access them?

"AE,UAE DLR,AE10,BE10,CE20,inbox/APAC-AE,9,4"

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Best way to setup a "Dictionary" similar to VBA?

#11 Post by Aacini » 02 Feb 2021 02:10

Just use a different delims char:

Code: Select all

for %%a in ("AE|UAE DLR|AE10,BE10,CE20|inbox/APAC-AE|9|4") do (
   for /F "tokens=1-6 delims=|" %%A in (%%a) do (
Output:

Code: Select all

DLR:                    UAE DLR
Entity is:              AE10,BE10,CE20
FMDEE Inbox is:         inbox/APAC-AE
Date Start position is: 9
Date End position is:   4
Date is : 2008
Date is : 2008
Antonio

PS - Why you didn't state the real specifications from the very beginning? :(
foxidrive wrote:
08 Dec 2014 05:51
The fastest way to get code that is robust and efficient is to clearly describe what you need to do and then let the programmers decide the best way to handle the job.

Batch files are often specific to your task because your filepaths, text, filenames and data are often used to write code that is both simpler and more efficient.

It makes it easy for people if you provide accurate details. The script you are given is designed to work in your specific situation and they will often add extra code to handle aspects that you may not have thought of.

If you hide your details by using fake names and fake paths and fake file information then nobody knows if you are using things like non-latin characters, or unicode, or poison characters for batch scripts. In these situations the volunteers can't include appropriate code to handle your special situations because they aren't aware you need it.

By providing poor information about your task the code you are given stands a good chance of failing.

Batch code is often written by analysing the characters and the patterns in text/numbers/paths/filenames that are being handled by the batch script. Wrong analysis, when all you have is wrong data, just leads to wrong batch code.

The layout of your text, numbers, paths, and filenames is also important when writing code so type over the sensitive details in your examples to disguise them but don't change the layout or length of these lines.

Please show respect to those people who give their free time to provide you with code by giving them accurate information about your task.
foxidrive wrote:
28 Jul 2016 03:01
What happens in a thread when incorrect or poor details are supplied: is that the volunteers write a script based upon that information and the next post often comes from the question writer saying "It doesn't work!"

Often a very long series of posts begins where we try to ascertain what the problem is and how the code fails and then what the real details of the task are. The script has to be rewritten, and that just wastes the time of those volunteers who are giving you free code and it is terribly frustrating and unsatisfying for volunteers to have to re-write a script for the same task again.

Don't do this.

SIMMS7400
Posts: 539
Joined: 07 Jan 2016 07:47

Re: Best way to setup a "Dictionary" similar to VBA?

#12 Post by SIMMS7400 » 02 Feb 2021 03:49

Thanks, A. It was a new development or else I would have revealed in the beginning

Thanks for your time, this is fantastic!!

One last question, how do I get this value into a variable?

Code: Select all

call echo Date is : %%DATAFILENAME:~!STR[%DATAFILENAME:~0,2%].DSP!,!STR[%DATAFILENAME:~0,2%].DEP!%%

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Best way to setup a "Dictionary" similar to VBA?

#13 Post by Aacini » 02 Feb 2021 19:35

SIMMS7400 wrote:
02 Feb 2021 03:49
One last question, how do I get this value into a variable?

Code: Select all

call echo Date is : %%DATAFILENAME:~!STR[%DATAFILENAME:~0,2%].DSP!,!STR[%DATAFILENAME:~0,2%].DEP!%%

Code: Select all

call set "result=%%DATAFILENAME:~!STR[%DATAFILENAME:~0,2%].DSP!,!STR[%DATAFILENAME:~0,2%].DEP!%%"
Antonio

CJM
Posts: 19
Joined: 25 Oct 2019 20:34
Location: Baltimore, MD USA

Re: Best way to setup a "Dictionary" similar to VBA?

#14 Post by CJM » 21 Feb 2021 19:12

For a quick lookup list, I use the name.ext variable parsers %~n and %~x, adding a . before the lookup value like this example which expands the 3-letter abbreviation for day-of-week to the full word:

Code: Select all

@	FOR %%T in (	%DATE%
)do @	FOR %%D in (
			Sunday.Sun
			Monday.Mon
			Tuesday.Tue
			Wednesday.Wed
			Thursday.Thu
			Friday.Fri
			Saturday.Sat
)do @	IF .%%T==%%~xD ECHO/%%~nD
It has a few obvious character limitations such as:
: \ ? *
but for simple lookups, it's quick and easy.

[EDIT 22Feb2021]

I forgot to note that these list entries don't reference actual files in this context (which is why wildcard characters * and ? can't be used--would make results unpredictable based on files that do happen to exist in the current directory). I'm merely using built-in variable substitution to manipulate the name.ext portion for cross-reference purposes, which is similar (albeit for short strings only) to the scripting.dictionary collection mentioned by the OP.
Last edited by CJM on 22 Feb 2021 07:59, edited 1 time in total.

Aacini
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Best way to setup a "Dictionary" similar to VBA?

#15 Post by Aacini » 21 Feb 2021 23:11

CJM wrote:
21 Feb 2021 19:12
For a quick lookup list, I use the name.ext variable parsers %~n and %~x, adding a . before the lookup value like this example which expands the 3-letter abbreviation for day-of-week to the full word:

Code: Select all

@	FOR %%T in (	%DATE%
)do @	FOR %%D in (
			Sunday.Sun
			Monday.Mon
			Tuesday.Tue
			Wednesday.Wed
			Thursday.Thu
			Friday.Fri
			Saturday.Sat
)do @	IF .%%T==%%~xD ECHO/%%~nD
It has a few obvious character limitations such as:
: \ ? *
but for simple lookups, it's quick and easy.
This method is interesting and may be extended to use real disk files, like this:

Code: Select all

@echo off
setlocal

rem Create the array-like table as empty files on disk
for %%a in ( Sunday.Sun
             Monday.Mon
             Tuesday.Tue
             Wednesday.Wed
             Thursday.Thu
             Friday.Fri
             Saturday.Sat ) do rem/> %%a

:next
echo/
set /P "ext=Enter 3-letters day: "
if errorlevel 1 pause & goto :EOF

rem Do the mapping
for %%a in (*.%ext%) do set "name=%%~Na"

echo Full day: %name%
goto next
Antonio

Post Reply