How to create and run a batch file

Batch files help user to execute a set of command to be executed in the defined order by  executing a batch file. We can execute large number of commands in one go using bat file.

Creating a batch file

A batch file has extension .bat .To create a new bat file, we can save a file in text editor like notepad in format .bat.

Some useful batch commands

@echo off – this will not echo the text post the command and will show only the execution result in the command prompt.

@echo on – this will echo the text post the command and will show the command as well as the execution result in the command prompt.

Pause– If we use pause, the batch file execution is halted until further intervention of user by pressing keys.

Arguments %1,%2,….. – This gives the parameter for the batch file.

echo val will display val in the command prompt

:: and rem can be used to add remarks to the script

Cls can be used to clear the command prompt

Example :

For e.g we need to compare 2 txt files, but name of files to be compared needs to be passed dynamically and log path also needs to be defined dynamically.

The content of batch file for this will look like

@echo off

Fc %1 %2 /n>%3

Pause

To execute this bat file, we have to write in run as

D:\Sample\new.bat d:\Sample\test4.txt d:\Sample\test7.txt d:\Sample\test9.txt

How to compare content of files using fc command

I was having issues with comparison of two large csv files that I came across fc comparision command of Microsoft DOS. fc  is a command line program that compares multiple files and outputs the differences between them

The syntax of fc Command and results of execution of fc with different arguments is shown below:

Syntax for fc command:

fc filename1 filename 2 [Parameter for comparision type] >Log File Path

where filename1 and filename2 are paths of the two files to be compared .

LogFilePath is path for saving result log

Commonly used Parameters are as described below:

/a :  fc displays only the first and last line for each set of differences.

/b : Compares the files in binary mode.

/c : Ignores the case of letters.

/l :  Fc compares the two files line by line and attempts to resynchronize the files after finding a mismatch. 

/lbn : Sets the n number of lines for the internal line buffer.

/n : Displays the line numbers during an ASCII comparison.

Connecting to excel as database using Adodb in VBScript/QTP

We can connect to excel file using Adodb object in vbscript.  Following are the steps in working with excel as database.

1.       Create an instance of connection :

Set objConn = CreateObject("Adodb.connection")

2.       Create connection string to connect to excel database as follows

strCon = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;Dbq=" & Workbook &";"

Here we have created a database for excel Workbook . The sheets in the workbook acts as table of the database.

3.       Connect to database as shown below:

objConn.open strCon

Now we are done with the connection to excel , we require SQL to run on the data to extract required information . 

4. Create a SQL as required as shown below:

strSQL =  "select name from [Table1$];"

Note here, we have to give sheet name acting as table within square bracket and with suffix “$” as shown above.

5.   Create a recordset as shown below and execute the query using recordset, all  the records based on query will be stored in recordset.

Set rs = CreateObject("ADODB.recordset")

rs.Open strSQL,objConn

6.       We can transfer the data extracted from query back in excel using copyrecordset method

XLSheet.range("A2").copyfromrecordset rs

Where XLSheet is the reference of required sheet in the excel  file and A2 is the location in sheet from which data will be pushed in the sheet.