External Data Sheet - Excel 2

Introduction of External Data Sheet

In QTP, we have an option for Data Table which comes very useful when the user wants to parameterize his/her test. The parameters set by user appear by default in the Columns of the sheet. Not only is this helpful for parameterized operation of your test also when you want to work with the Output function of QTP you can use this table since the corresponding outputs can be viewed over here.
This option can be as per the user requirement and in this the user can make an Excel sheet with the required parameters in it. Also one can import and export the contents to his test while running it.

Import External Data File

The user shall perform the following operations:
In this once the user has created the Excel sheet and has recorded the test, (s)he shall right click on any of the cells in the Data Table and select the File>Import, user will get a window where (s)he will have to select the exact location of the excel sheet.


On Clicking the option, user will get a window pop-up box asking whether you will want to replace the contents of the current data table with the ones in the excel sheet. Click OK and proceed.
Select the exact location of the excel file on your PC which you want to import.
The contents of the excel sheet shall appear in the Data Table of QTP once you selected the required excel sheet.
Remember to use the same naming convention through-out the script, you have used in the file. Each of the column name in your excel file will become one parameter.
Secondly the actions for which the user shall be setting parameters need to be given the same name as in the imported excel file. As shown below, while recording the file the user has selected a particular value for the Action which he wants to parameterize which will appear in the constant option.

The user has to choose the Parameter option and then type the exact name as in the Data table for e.g.: “No_Pass”, here we are parameterizing the no of passengers for whom the tickets have to be booked and will re-run the script for that much time.

Points to note regarding which location to select OR whether to make a sheet global or local.

1. Globally implies the user has the Global sheet of the data table filled up with certain data. Now when he runs the test it will operate for the amount of rows that are filled in this sheet.
2. If you want a condition that for single row run of the global sheet there should be run of another sheet namely the “Action 1” of the data table we call the operation as Local.
3. In this it is important that the user selects the Parameter option as local and not global as in the above condition and the required contents will come in Action 1 sheet.
4. Now initially if while you are importing if there were two sheets created by you then by default the contents of the second sheet will be Action 1. It is only that the corresponding Action be parameterized properly.

Now, In the keyword view, Right click on the Action 1 and select Action Call Properties and then in the Run Tab select the no of rows for which you want to run the Local sheet.
When the user runs this script, for every single row of the Global sheet, the Action 1 sheet will run for all of its respective columns.
A similar method can be used to import a single sheet or a database file.

Also from within the script you can use Datatable.Import("path of file")

Export External Data File

Like importing data sheet we can also export the entire data table into an external excel file. This is basically useful if one wants to use the required parameters in some different test run within QTP.
Similar to the exporting of complete file we can also export an single sheet from the data table

Also from within the script you can use Datatable.Export("path of file")

Using Datatable - QTP with Excel

How can we use the data table to provide input data to an application?

Use the DataTable.Value method to access data from the data table and input it into the application
For data in the Global datasheet:
1. Open a new script.
2. In column A of the Global datasheet, enter the data in three rows.
3. Go to www.google.com.
4. Begin recording.
5. Type a value into the Google search field.
6. Stop recording.
7. Go to the Expert view. Modify the script so it look like this:
rc = DataTable.Value ("A", dtGlobalSheet)
msgbox rc
Browser("Google").Page("Google").WebEdit("q").Set rc
8. To run all rows in the global data table, go to Test ->; Test Settings -> Run tab, and select "Run on all rows."
For data in the Local datasheet:
1. Start a new script.
2. In column A of the Action1 datasheet, enter the data in three rows:
3. Go to www.google.com.
4. Begin recording.
5. Type a value into the Google search field.
6. Stop recording.
7. Go to the Expert view. Modify the script so it look like this:
rc = DataTable.Value ("A", dtLocalSheet)
msgbox rc
Browser("Google").Page("Google").WebEdit("q").Set rc
8. To run all rows:

1. Right-click on the Action name in the Tree View.
2. Go to Action Properites -> Run tab, and select "Run all rows."

Similarly, How can we use the data table to get output data from an application?

Create an Output Value. The text will be placed in the datatable and can be accessed as needed.
1. Once you see the text you want to retrieve, start recording.
2. From the Insert menu, select Output Value, then Text Output Value.
3. Click on the desired text. The "Text Output Value Properties" window will appear.
4. In the window you can verify or set the Before and After text settings.
5. By default the retrieved value will be added to the Global sheet. You can modify the settings by selecting Output Text in the combobox, then clicking Modify.
6. Once satisfied, click OK.
An Output statement will be entered into the script.
Example:Browser("Browser").Page("Page").Output CheckPoint("Text")
msgbox DataTable.Value("PageOutput_Text_out", dtGlobalSheet)
In addition, a column (in the example, PageOutput_Text_out) will be inserted into the datatable(Remember in the runtime datatable), with the output text.
OR Another method to retrieve data during run time is to do just the opposite of what we did above in the first question above.
DataTable.Value(ParameterID [, SheetID])=NewValue
Note:The value property is the default property for the DataTable object. As the default property you do not need to explicitly use .Value.
DataTable(ParameterID [, SheetID]) = NewValue
Example:' Add data to the current row of the Global sheet
DataTable("VarName", dtGlobalSheet) = "new value" ' Using DataTable by itself
DataTable.Value("VarName2", dtGlobalSheet) = "new value2" ' Using .Value
' Add data to the current row of the Local sheet
DataTable("VarName", dtLocalSheet) = "new value" ' Using DataTable by itself
DataTable.Value("VarName2", dtLocalSheet) = "new value2" ' Using .Value

Upload Test cases from Excel to Quality Center(QC)

Upload Test cases from Excel to Quality Center(QC)

Writing Test Cases directly to QC is quite a cumbersome task. So To simplify the process, We can create the test cases in an excel sheet and then upload them to QC.
To do so, we have to make sure that the columns used in excel match with the fields/data which we enter in QC. I will explain the steps using an example,--

1. Suppose we have created the test cases in excel in below


2. Now, next step would be to install the excel addin for QC. To do so, follow the following steps:-
    a. Launch Quality Center
    b. Click on Addins link
    c. Click on More QC Addins link
    d. We can go through the Guide also or this step can be skipped
    e. Click on "download ..." link
    f. Follow the steps of installation
    g. Restart the system

3. Now open the excel sheet containing test cases ns select only the part which needs to be uploaded as shown in the picture below.


4. Now go to Tools—>Export to Quality Centre. Following window would be displayed. Login with valid credentials.


5.Click on Next and login with valid credentials. Click Next again.


6. Select the respective domain and project.


7. Select ‘Tests’ Radio button and click Next.


8. Select New Map to store the settings. Give a map name and click next.


9. Now, we have to map each column in our excel sheet to specific fields in QC. To do so, click on following from the quality center valued displayed in window and give their respective column numbers.


10. Window would look like this after selecting all values. Click on next button.

11. Above steps will upload the test cases in QC at path ‘Iteration1\ModuleABC\xyz’. We can view them in Qc now.
Donot mention the root directory.
If the xyz folder is not present  at Iteration1\ModuleABC in QC, it would be created.
similarly, if the ModuleABC folder itself is not present at Iteration1, The folder and subfolder mentioned would be created.

Dictionary Objects in QTP

Dictionary object is similar to a typical array. The difference between a dictionary object and an array is that there is a unique key associated with every item of dictionary object. This unique key can help you in calling that item as and whenever required.

Shown below is a typical script.

Dim dict ‘ Create a variable.
Set dict = CreateObject(“Scripting.Dictionary”)
dict.Add “Company”, “HP” ‘ Adding keys and corresponding items.
dict.Add “Tool”, “QuickTest Pro”
dict.Add “Website”, “LearnQTP”
dict.Add “Country”, “India”

dict is an object of class Scripting.Dictionary.

Items Method, Keys Method, Remove Method, RemoveAll Method

Using Exists Method to check whether the key Country exists?

If dict.Exists(“Country”) Then
msg = “Specified key exists.”
Else
msg = “Specified key doesn’t exist.”
End If

Using Items and Keys method to retrieve ALL items and keys respectively from inside dictionary object.

i = dict.Items ‘ Get the items.
k = dict.Keys ‘ Get the keys.
For x = 0 To dict.Count-1 ‘ Iterate the array.
msgbox  i(x) & ” :” & k(x)
Next

Using Remove method to remove Website – LearnQTP pair from the object.

dict.Remove(“Website”)

Using Remove all method to clear the dictionary

dict.RemoveAll ‘ Clear the dictionary.

What are the places where it can be used?

When you want to share data between different actions in a test, dictionary object can be used. To do this you should create a reserved test object for the Dictionary object. Here is a process to set up a reserved dictionary object.

Open Window’s registry by opening a Run window and entering regedit.

1. Navigate to HKEY_CURRENT_USER\Software\Mercury Interactive\QuickTest Professional\MicTest\ReservedObjects.
2. Create a new key (folder) named GlobalDictionary by right-clicking on the ReservedObjects key and selecting New -> Key.
3. Under the new key, create a String value named ProgID by right-clicking on GlobalDictionary and selecting New -> String Value.
4. Assign “Scripting.Dictionary” to the new ProgID string value by right-clicking on ProgID and selecting “Modify.”
5. If QTP window is already open you need to close and restart it.
6. Now to check whether dictionary object was installed successfully in registry, simple write GlobalDictionary. (don’t forget the dot) and you should see a drop-down containing all methods and properties associated with dictionary object.

Why dictionary object and why not array only?

As shown in the example above, dictionary object was used with the index (key) being string. In the case of array, the index can be ONLY numeric. Then of course we have some obvious advantages like referencing a value with a meaningful keys etc.

Regular Expression

A regular expression is a string that describes or matches a set of strings. It is often called a pattern as it describes set of strings.

Given underneath is one of the most widely used and ever confused BackLash character. The remaining expressions are serialized below that.

Using the Backslash Character
A backslash (\) instructs QuickTest to treat the next character as a literal character, if it is otherwise a special character. The backslash (\) can also instruct QuickTest to recognize certain ordinary characters as special characters. For example, QuickTest recognizes \n as the special newline character. 
For example:
w matches the character w
\w is a special character that matches any word character including underscore

The period would be mistaken as an indication of a regular expression. To indicate that the period is not part of a regular expression, you would enter it as follows:
mercurytours\.mercuryinteractive\.com Note: If a backslash character is used before a character that has no special meaning, the backslash is ignored. For example, \z matches z.

Expressions & Explanation
Special characters and sequences are used in writing patterns for regular expressions. The following describes the characters and sequences that can be used.


\
Marks the next character as either a special character or a literal. For example, "n" matches the character "n". "\n" matches a newline character. The sequence "\\" matches "\" and "\(" matches "(".

^
Matches the beginning of input.

$
Matches the end of input.

*
Matches the preceding character zero or more times. For example, "zo*" matches either "z" or "zoo".

+
Matches the preceding character one or more times. For example, "zo+" matches "zoo" but not "z".

?
Matches the preceding character zero or one time. For example, "a?ve?" matches the "ve" in "never".

.
Matches any single character except a newline character.

(pattern)
Matches pattern and remembers the match. The matched substring can be retrieved from the resulting Matches collection, using Item [0]...[n]. To match parentheses characters ( ), use "\(" or "\)".

xy
Matches either x or y. For example, "zwood" matches "z" or "wood". "(zw)oo" matches "zoo" or "wood".

{n}
n is a nonnegative integer. Matches exactly n times. For example, "o{2}" does not match the "o" in "Bob," but matches the first two o's in "foooood".

{n,}
n is a nonnegative integer. Matches at least n times. For example, "o{2,}" does not match the "o" in "Bob" and matches all the o's in "foooood." "o{1,}" is equivalent to "o+". "o{0,}" is equivalent to "o*".

{n,m}
m and n are nonnegative integers. Matches at least n and at most m times. For example, "o{1,3}" matches the first three o's in "fooooood." "o{0,1}" is equivalent to "o?".

[xyz]
A character set. Matches any one of the enclosed characters. For example, "[abc]" matches the "a" in "plain".

[^xyz]
A negative character set. Matches any character not enclosed. For example, "[^abc]" matches the "p" in "plain".

[a-z]
A range of characters. Matches any character in the specified range. For example, "[a-z]" matches any lowercase alphabetic character in the range "a" through "z".

[^m-z]
A negative range characters. Matches any character not in the specified range. For example, "[m-z]" matches any character not in the range "m" through "z".

\b
Matches a word boundary, that is, the position between a word and a space. For example, "er\b" matches the "er" in "never" but not the "er" in "verb".

\B
Matches a non-word boundary. "ea*r\B" matches the "ear" in "never early".

\d
Matches a digit character. Equivalent to [0-9].

\D
Matches a non-digit character. Equivalent to [^0-9].

\f
Matches a form-feed character.

\n
Matches a newline character.

\r
Matches a carriage return character.

\s
Matches any white space including space, tab, form-feed, etc. Equivalent to "[ \f\n\r\t\v]".

\S
Matches any nonwhite space character. Equivalent to "[^ \f\n\r\t\v]".

\t
Matches a tab character.

\v
Matches a vertical tab character.

\w
Matches any word character including underscore. Equivalent to "[A-Za-z0-9_]".

\W
Matches any non-word character. Equivalent to "[^A-Za-z0-9_]".

\num
Matches num, where num is a positive integer. A reference back to remembered matches. For example, "(.)\1" matches two consecutive identical characters.

\n
Matches n, where n is an octal escape value. Octal escape values must be 1, 2, or 3 digits long. For example, "\11" and "\011" both match a tab character. "\0011" is the equivalent of "\001" & "1". Octal escape values must not exceed 256. If they do, only the first two digits comprise the expression. Allows ASCII codes to be used in regular expressions.

\xn
Matches n, where n is a hexadecimal escape value. Hexadecimal escape values must be exactly two digits long. For example, "\x41" matches "A". "\x041" is equivalent to "\x04" & "1". Allows ASCII codes to be used in regular expressions.

File handling in QTP

To interact with text files using QTP. Interaction can be(but not limited to) in the form of reading input from a file, writing output to a file.  This post describe in detail "File handling using QTP".
We use FSO object to do this.

What is FSO?
FSO stands for File System Object. This is used to support text file creation and manipulation through the TextStream object and is contained in the Scripting type library (Scrrun.dll)
The FSO Object Model has a rich set of properties, methods and events to process folders and files.


How to create a file?
We first create a FSO object using CreateObject and then create a text file using CreateTextFile.
For Example: Suppose you want to create a file called "test.txt" located in C:

Dim fso, file, file_location
file_location = "C:\file_location"
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set file = fso.CreateTextFile(file_location, True) // True--> file is to be overwritten if                  it already exists else false 

We would use the same example for the rest of this post.
How to open a file?

Set file= fso.OpenTextFile("C:\file_location", ForWriting, True)
//2nd argument can be ForReading, ForWriting, ForAppending
//3rd argument is "True" if new file has to be created if the specified file doesn’t exist else false, blank signify false.

How to read content from a file?
Use ReadLine() method
For example:

Set file= fso.OpenTextFile("C:\file_location", ForReading, True) //2nd argument should always be "ForReading" in order to read contents from a file
Do while file.AtEndofStream <> True
      data = file.ReadLine()
      msgbox data
Loop

How to write content to a file?
You can use  Write() or WriteLine() Methods to write text into a file. The difference between the Write() and WriteLine() Method is that the latter automatically inserts a new line character while the former doesn’t insert a new line character.
For example:

Set file= fso.OpenTextFile("C:\file_location", ForWriting, True) //2nd argument should always be "ForWriting" in order to write contents to a file
file.Write("This is a place to get all your qtp")
file.Write("questions and answers solved.")
//Output will be:
This is a place to get all your qtp questions and answers solved.
 while
file.WriteLine("This is a place to get all your qtp")
file.Write("questions and answers solved.")
//Output will be:
This is a place to get all your qtp
questions and answers solved.

How to delete content?
Use DeleteFile() method to delete a file from a particular location
Foe Example:

file_location = "C:\file_location"
Set fso = CreateObject(“Scripting.FileSystemObject”)
fso.DeleteFile(file_location)

Datatable

Usage of datatable

 
Use the DataTable.Value method to access data from the data table and input it into the application
For data in the Global datasheet:
1. Open a new script.
2. In column A of the Global datasheet, enter the data in three rows.
3. Go to www.google.com.
4. Begin recording.
5. Type a value into the Google search field.
6. Stop recording.
7. Go to the Expert view. Modify the script so it look like this:
rc = DataTable.Value ("A", dtGlobalSheet)
msgbox rc
Browser("Google").Page("Google").WebEdit("q").Set rc
8. To run all rows in the global data table, go to Test ->; Test Settings -> Run tab, and select "Run on all rows."
For data in the Local datasheet:
1. Start a new script.
2. In column A of the Action1 datasheet, enter the data in three rows:
3. Go to www.google.com.
4. Begin recording.
5. Type a value into the Google search field.
6. Stop recording.
7. Go to the Expert view. Modify the script so it look like this:
rc = DataTable.Value ("A", dtLocalSheet)
msgbox rc
Browser("Google").Page("Google").WebEdit("q").Set rc
8. To run all rows:

1. Right-click on the Action name in the Tree View.
2. Go to Action Properites -> Run tab, and select "Run all rows."

Similarly, How can we use the data table to get output data from an application?

Create an Output Value. The text will be placed in the datatable and can be accessed as needed.
1. Once you see the text you want to retrieve, start recording.
2. From the Insert menu, select Output Value, then Text Output Value.
3. Click on the desired text. The "Text Output Value Properties" window will appear.
4. In the window you can verify or set the Before and After text settings.
5. By default the retrieved value will be added to the Global sheet. You can modify the settings by selecting Output Text in the combobox, then clicking Modify.
6. Once satisfied, click OK.
An Output statement will be entered into the script.
Example:
Browser("Browser").Page("Page").Output CheckPoint("Text")
msgbox DataTable.Value("PageOutput_Text_out", dtGlobalSheet)
In addition, a column (in the example, PageOutput_Text_out) will be inserted into the datatable(Remember in the runtime datatable), with the output text.
OR Another method to retrieve data during run time is to do just the opposite of what we did above in the first question above.
DataTable.Value(ParameterID [, SheetID])=NewValue
Note:
The value property is the default property for the DataTable object. As the default property you do not need to explicitly use .Value.
DataTable(ParameterID [, SheetID]) = NewValue
Example:
' Add data to the current row of the Global sheet
DataTable("VarName", dtGlobalSheet) = "new value" ' Using DataTable by itself
DataTable.Value("VarName2", dtGlobalSheet) = "new value2" ' Using .Value
' Add data to the current row of the Local sheet
DataTable("VarName", dtLocalSheet) = "new value" ' Using DataTable by itself
DataTable.Value("VarName2", dtLocalSheet) = "new value2" ' Using .Value