How to import data from excel to UFT based on cond

问题:

I have an excel sheet which contain data for test cases and each test cases is separated by a column value say script ID and i want to load only row where script ID =1 . How we can achieve this with DataTable.ImportSheet method as this method import all the row having script id=2 and 3

I also try with query string and that is working fine , but i want to achieve this by DataTable.ImportSheet method (project constraints)

Thanks Aman

回答1:

You can also do it by creating data table first manually.

For example,

Set myxl = createobject("excel.application")
myxl.Application.Visible = true
myxl.Application.DisplayAlerts = false
myxl.Workbooks.Open DataSheetFilePath
set sheet = myxl.ActiveWorkbook.Worksheets("SheetName")
Row=sheet.UsedRange.Rows.Count
Col=sheet.UsedRange.Columns.Count
'Create Data table first
Datatable.AddSheet ("DataTableName")
Set ParameterTable = datatable.GetSheet("DataTableName")
'Add cols from Excel
For j = 1 To Col
    ParameterTable.AddParameter sheet.cells(1,j).value,""
Next

'Begin search in Excel
'Suppose the ID column will be 1
DataTableRow = 1
For i = 1 to Row
    If sheet.cells(i,1).value = 1 Then
        'Add row record to datatable
         ParameterTable.SetCurrentRow (DataTableRow)
         For j = 1 to Col
         ColName = sheet.cells(1,j).value
         Val = sheet.cells(i,j).value
         datatable.Value(ColName,"DataTableName") = Val
         DataTableRow = DataTableRow+1
         Next
    End If
Next

回答2:

Well, the DataTable.ImportSheet method simply imports an entire sheet (one tab/sheet from an excel file). As far as I know, it won't allow you to import just some rows of it.

However...

You can set up your code to only process the row you want. You will want to control your loop programmatically instead of automatically, so first: set your action (or test) to run OneIteration only. If the action is set to "Run on all rows", then it just looks at the sheet what is the last row with any data and then runs that many iterations. So, if you have a sheet with data on the first four lines, and some data down on row 100, then QTP will try to run 100 iterations even though the data is blank.

To set the iterations setting for an action, you can right-click on the action in the flow diagram and select Action Call Properties. To set iterations for a whole test, right click on the Start oval, select properties, select Run, and find the setting there.

Next, Create a loop similar to this:

'ImportedSheetTabName = the name of the tab in Excel, you specified it during importsheet

for Row = 1 to DataTable.GetSheet(ImportedSheetTabName).GetRowCount

  DataTable.GetSheet(ImportedSheetTabName).SetCurrentRow Row

  if DataTable("script_ID",ImportedSheetTabName) = "1"  'based on your question
    'do stuff with the row
    'this is where you would put ALL of the work to be done...  
    'it can be as long as you need.
  else
    'do nothing.  this will ignore all rows that are not Script ID = 1
  end if

next

What I actually do in all my sheets (to be used with QTP) is to include a column called "data" that simply has an X in it if the row contains datasheet data. I can literally put in anything I want between the rows, including other formulas and calculations (data building stuff), comments, lists, etc... and know that all that will be ignored because I control my loops by hand.

回答3:

For importing specific data from the datasheet you can follow the following the procedure: - first import the data from the excel sheet to the global or specific action sheet as per your requirement. - In the next line take a variable, lets say var which will hold the value of total number of rows in it. - then run the for loop from the value which you want to the value which you want.

Code can be following:

datatable.importsheet "path where excel is stored" , 1, "Global or specific action data sheet"

Here 1 denotes that you are taking the data from first sheet in the excel file which you are importing

var = datatable.GetSheet("Global or action sheet").GetRowCount

for i = 1 to var (change the conditions as per your requirements)

Datatable.SetCurrentRow(i)

Now as per the value of i in the above loop you will have your scripts iterated on to the particular data for which you wanted the sheet to run.

Hope this helps.