ImportSheet in QTP Data Table from QC

How to import and export sheets from Quality Center using DataTable of QTP ?

I came across this problem recently when we decided to store our excel files in test plan module of Quality Center(Version 10.0.).

Joining the automation team while the project is already ON keeps you limited in your approaches. If Automation is being carried out using QTP and Quality Center with a framework, which is not flexible It limits the approaches further.

In my project, There were loads of reusable Actions , in which data was being pulled from excel sheets and we were using "DataTable.ImportSheet" for the task. Excel files were on client filesystem BUT now we wanted to keep our data at a central location so that it is created, managed and maintained better. QC was on everybody's mind as It was already being used by other testing teams as central repository. Till Date QC does not have any mechanism which can be used to take care of test data in easier and better ways. Atleast to me it is not better. I am not a very big fan of QTP and QC.

We wanted to reflect minimum changes, for data needs, in our scripts. I wish QTP could support over-ridingImportSheet method of DataTable using RegisterUserFunc. Which could solve the problem without going inside scripts and replacing DataTable.ImportSheet with search and replace option. However, We do not have that option.

I am posting the approach taken by me (May not be the best one) so that it helps you if you are also stuck on the same problem.

Importing Data tables inside QTP is quite simple using the following line of code.

DataTable.ImportSheet "C:\Full\File\path\FileName.xls" _
,"SourceSheetName","DestinationSheetName"

'OR
'Add C:\Full\File\path to folder options in QTP Options.

DataTable.ImportSheet pathfinder.locate("FileName.xls") _
,"SourceSheetName","DestinationSheetName"

If you refer the documentation provided in QTP,you will find that the Ist parameter to ImportSheet method can be a filesystem path or Quality Center Path.

Following the docs In File-->Settings-->resources if you would choose a path for importing data table using the file explorer and point it to the file stored in attachments of a test stored in Quality Center the path would be something like

[Quality Center] \Subject\path\to\yourtest\FileName.xls

1. What was digged to solve the problem ?

   1. Pathfinder.Locate("FileName.xls") by adding my QC Folder in options.

      I tried using this approach, however it did not work, and I kept getting error Path not found

   2. Can I play with Environment("TestDir") or Envrionment("SystemTempDir") ??

      If you explore the %Temp%TD_80 you would see the following path to the attachments of your test!!

      "C:\Users\[UserName]\AppData\Local\Temp\TD_80\ _6f4df540\Attach\TEST\[TestID]\TEST_[TestID]_[FileName].xls"

      How to ensure that Temp%\TD_80 is not cleared on every round of execution. And the solution should mandatorily download the excel file so that we can keep our standard approach of using DataTable.ImportSheet.

2. Solution

We decided to go for OTA API and felt relieved that it could be accessed using set QCConn = QCUtil.QCConnection inside QTP's IDE.

Following is the algorithm, which we used.

1. Get hold of a Test Stored in Quality Center and reach the attachment object associated with a test.
2. Download the excel file(stored as attachment) to client's filesystem using the Attachment Object.
3. Use DataTable.ImportSheet method to import data.
4. replce DataTable.ImportSheet call with a custom function and Leave rest of the scripts as intact as it was.

I am posting the code below for your use and review.

Public Function DataTableImportSheetFromQC(QTPTest,sFileName,SheetSource,SheetDest)
        Dim attachFact,attachList,attachObj,bResult

        On Error Resume Next

        'Assingments
        bResult=False
        Set attachFact = QTPTest.Attachments
        Set attachList = attachFact.NewList("")

        For each attachObj in attachlist
        'Ensuring Excel File is stored as attachment to the test
        'Naming Convention used by QC is TEST_[TESTID]_[FileName]
                If Instr(attachObj.Name,sFileName)>0 And _
                        Instr(attachObj.Name,TestObject.ID)>0 Then
                        'Download the attachment to client  filesystem
                        'Import Sheet Method was not working to QCPaths,
                        'Or We can say that QCPath is the temp path of downloaded file
                        attachObj.Load True, "" 'the second param is not required in vbscript
                        'FilName Property of attachObj gives full file path
                        DataTable.ImportSheet  attachObj.FileName,SheetSource,SheetDest
                        bResult = True
                        Exit For ' Dont waste time in searching for others
                End If
        Next

        If Err.Number <> 0 Or Not  bResult Then
                bResult = False
                Err.Clear
                On Error goto 0
        End If

        DataTableImportSheetFromQC = bResult
        'release the objects
        Set attachList = Nothing
        Set attachFact = Nothing

End Function

After designing this function we replaced all lines, which used DataTable.ImportSheet statement with a call to this function. Hope it is of some help to you and please post replies if there is any other approach, which you have adopted.