Automatically Create properties for DataSource

Any answer for this question? Can the properties be automatically extracted out of a JDBC SQL query or Excel File?

This is sloppy, and missing the classes you'll need to import, but what you want probably looks something like:

def file = new File('C:\\files\\DatabaseName.xls').canonicalPath
def xls = new ActiveXObject('Excel.Application')
def workbooks = xls.Workbooks
def workbook = workbooks.Open(file)
def sheet = workbook.ActiveSheet

def source = testRunner.testCase.getTestStepByName("DataSource")
def thiscolumn
for(i=65;i<69;i++){
 thiscolumn = i as char
 source.addProperty(sheet.Range(thiscolumn+"1").Value)
}

A few points, 65 through 68 for i are just trivial numbers, where 65 = A, 66 = B, if you have 10 columns A to J, its 65 through 74, so put 75 in the loop condition part of the for statement.

Anyway, this assumes the DataSource is called "DataSource", and referrs to a file called "C:\files\DatabaseName.xls".

I get this error when trying to read from the CreateOrg sheet

groovy.lang.MissingMethodException: No signature of method: java.lang.String.getSheet() is applicable for argument types: (java.lang.String) values: [CreateOrg] Possible solutions: getBytes(java.lang.String), getAt(java.lang.String), getBytes(), getChars(), getChars(), getBytes(java.nio.charset.Charset) error at line: 6

Also how do i define which column to start on? I want to start reading from C36

import org.codehaus.groovy.scriptom.ActiveXObject
def file = new File("O:\\Technology & Telecommunications\\Application Development\\Applications Integration & Implementation\\Testing Team\\Projects\\Australia Post\\7.5 New Temp Permit - Decision Tree.xls").canonicalPath
def xls = new ActiveXObject('Excel.Application')
def workbooks = xls.Workbooks
def workbook = workbooks.Open(file)
def sheet = file.getSheet("CreateOrg");  

def source = testRunner.testCase.getTestStepByName("CreateOrgValidations DataSource")
def thiscolumn
for(i=36;i<76;i++){
   thiscolumn = i as char
   source.addProperty(sheet.Range(thiscolumn+"1").Value)
}
xls.Quit()
Scriptom.releaseApartment()

Theres a few problems here, but lets start with the easy one, the for loop.


Ok, the confusion here, is that in my script, I have assumed the ROW number with the column headers is 1.

This is the "1" in the 'source.addProperty(sheet.Range(thiscolumn+"1").Value)' line.

So if you want row 36, this line would be 'source.addProperty(sheet.Range(thiscolumn+"36").Value)'.

Now, the C part.

Check out an ascii table like this: http://www.ascii-code.com/

Look at the DEC and Description columns. In the for loop, you want to start at the DEC number for your column, so if you want C36, this is dec 67.

I'm not sure where you want your column headers to stop, so lets say the column headers are C-M. you need the DEC number of M+1 for the condition part of the for loop.

So given the information:

C = 67
M= 77
M+1 = 78
ROW = 36

The code looks like:

for(i=67;i<78;i++){
thiscolumn = i as char
source.addProperty(sheet.Range(thiscolumn+"36").Value)
}

for(i=67;i<78;i++){
   thiscolumn = i as char
   source.addProperty(sheet.Range(thiscolumn+"36").Value)
}

Ok, now for the error you are getting. You appear to be trying to get a sheet object directly off the file

instead of:

def sheet = file.getSheet("CreateOrg");

just use

def sheet = workbook.getSheet("CreateOrg")

Having said THAT thoug, I am pretty sure getSheet is not a scriptom method, at least not like that. Try:

def sheet = workbook.Sheets.Item["CreateOrg"]


Anyway, at the end of the day, the script will look like this:

import org.codehaus.groovy.scriptom.*

def source = testRunner.testCase.getTestStepByName("CreateOrgValidations DataSource")

def file = new File("O:\\Technology & Telecommunications\\Application Development\\Applications Integration & Implementation\\Testing Team\\Projects\\Australia Post\\7.5 New Temp Permit - Decision Tree.xls").canonicalPath
def xls = new ActiveXObject('Excel.Application')
def sheet = new ActiveXObject("Excel.Sheet")
def workbooks = xls.Workbooks
def workbook = workbooks.Open(file)

sheet = workbook.Sheets.Item["CreateOrg"]
//log.info thesheet.Name

String thiscolumn //or def if you dont want it cast yet

for(i=67;i<78;i++){
   thiscolumn = i as char
   source.addProperty(sheet.Range(thiscolumn + "36").Value)
   //log.info sheet.Range(thiscolumn + "36").Value
}

workbook.close(false, null, false)
xls.Quit()
Scriptom.releaseApartment()

If the ascii value thing is still confusing you try this:

import org.codehaus.groovy.scriptom.*

def source = testRunner.testCase.getTestStepByName("CreateOrgValidations DataSource")

def file = new File("O:\\Technology & Telecommunications\\Application Development\\Applications Integration & Implementation\\Testing Team\\Projects\\Australia Post\\7.5 New Temp Permit - Decision Tree.xls").canonicalPath
def xls = new ActiveXObject('Excel.Application')
def sheet = new ActiveXObject("Excel.Sheet")
def workbooks = xls.Workbooks
def workbook = workbooks.Open(file)

sheet = workbook.Sheets.Item["CreateOrg"]
//log.info thesheet.Name

String FirstColumn = "C"
String LastColumn = "M"

String thiscolumn //or def if you dont want it cast yet

for(i=(int)FirstColumn;i<((int)LastColumn)+1;i++){
   thiscolumn = i as char
   source.addProperty(sheet.Range(thiscolumn + "36").Value)
   //log.info sheet.Range(thiscolumn + "36").Value
}

workbook.close(false, null, false)
xls.Quit()
Scriptom.releaseApartment()

I haven't checked that though, but should work unless I've forgotten something simple..

Thankyou very much for your help. The file path is correct and the sheet exists in the workbook but the script will not run. I get this error if i try to print out the sheet name.
grooxy.lang.MissingPropertyException: No such property : thesheet for class :Script6. Error at line 12

If i comment out line 12 , then i get this error
java.lang.NullPointerException: Cannot invoke method addProperty() on null object error at line:20

import org.codehaus.groovy.scriptom.*

def source = testRunner.testCase.getTestStepByName("Datasource")

def file = new File("O:\\Technology & Telecommunications\\Application Development\\Applications Integration & Implementation\\Testing Team\\Projects\\Australia Post\\7.5 New Temp Permit - Decision Tree.xls").canonicalPath
def xls = new ActiveXObject('Excel.Application')
def sheet = new ActiveXObject("Excel.Sheet")
def workbooks = xls.Workbooks
def workbook = workbooks.Open(file)

sheet = workbook.Sheets.Item["CreateOrg"]
//log.info thesheet.Name
String FirstColumn = "B"
String LastColumn = "S"
String thiscolumn //or def if you dont want it cast yet

for(i=(int)FirstColumn;i<((int)LastColumn)+1;i++){
   thiscolumn = i as char
   log.info sheet.Range(thiscolumn + "34").Value
   source.addProperty(sheet.Range(thiscolumn + "34").Value)
}
workbook.close(false, null, false)
xls.Quit()
Scriptom.releaseApartment()

If this is an easy fix then do you have any ideas? If not, then dont spent anymore time on it... i can add them manually.
Thankyou.

I would say the datasource is named wrong.

in my examples I've just called it 'Datasource' so the line mapping it to a variable looks like:

def source = testRunner.testCase.getTestStepByName("Datasource")

in the first bit of code you posted, its looking like yours is called 'CreateOrgValidations DataSource'

so try changing the name in that line to whatever you called the datasource. probably something like:

def source = testRunner.testCase.getTestStepByName("CreateOrgValidations DataSource")