Thursday, December 4, 2008

Variables in the .bat script


This series of blog postings about variables has probably been of interest mostly to technical folks concerned with automating QlikView documents. This one is also like that only more so. Here’s some automation wisdom that you won’t find anywhere else:

We have a set of reports where the document variables contain all the file pathnames and database access names. Loading of the reports is handled by Windows .bat scripts. We saw that the .bat scripts also needed to be able to use flexible file pathnames and database access. The variables and their values are stored in a comma-delimited file (a .csv file). The .bat scripts load their variables from the same file that the QlikView documents use to load their variables.

This is a .bat script command that will load DOS variables from a comma-delimited file:

FOR /F "skip=1 tokens=1-2 delims=," %%a IN ('type ..\Report_Control\RPT_VARS.csv') DO set %%a=%%b

That one line reads a comma-delimited text file named RPT_VARS.csv that is stored in a folder named ..\Report_Control
Skip=1 tells it to skip over the first line of the file (because it contains column titles).
The text file is simple. It looks something like this:

Variable Name,Variable Value
RPT_DBNAME,INVT_PROD
RPT_INPUT,E:\CURRENT\DATA\
RPT_OUTPUT,\\AMSERV28\PROJECTS\
RPT_LOG,C:\LOGS\BATCHLOG.TXT

Within the .bat script the variables are used between percent signs. So a line in the script might look like this:

echo %date%,%time% QV Batch started >> %RPT_LOG%



One of the reasons we stored all of the pathnames and access parameters in an external file that could be turned into variables is that it simplified the change control or promote to production process. We could develop a QlikView report and the associated scripts on a development server and then easily move the report and scripts as files to the QA server or production server without any changes. The programs would run correctly after being promoted to QA or production because they picked up the appropriate pathnames and database access variables info from the comma-delimited file stored on the new server.

We name a lot of our files with a yyyymmdd datestamp in the filename, for example “20081126_NorthRegion.dat”. It’s easy to create today’s yyyymmdd date in the .bat script as a variable with this script line:
FOR /F "tokens=1-4 delims=/- " %%A in ('date/T') do set TTDATE=%%D%%B%%C

Then it can be used in the .bat script as part of a filename like this:
COPY CURRENT.DAT %TTDATE%_NorthRegion.dat

1 comment:

Anonymous said...

Simple script that works great. Thanks.