Extractng Google sites tables to Excel

In past few months I had to use tables in Google sites to gather issues and other data, replacing local spread sheets already in place. Though this is far from ideal issue tracking systems, it is absolutely great at what it is promising; fast set-up; easy to share and collaborate; good and clean UI . They worked great.

When I had to extract data, there was no easy way - at least I didn't find any. This little VBScript routine saved the day. I had to take short cuts to save time (to avoid all the authentication issues, I first open the site in IE and then run this. It does not use the same instance but new instance already have access to the site. And gave a 4 second sleep to complete the page load instead of waiting for an event).

Note that ~ is used as the delimiter to allow commas in data and non-breakable spaces in the table are replaced by regular spaces.

set FSO = CreateObject("Scripting.FileSystemObject")
Set IE = WScript.CreateObject("InternetExplorer.Application", "IE_")
IE.Visible = True

  recIndex=1
  set outFile = FSO.CreateTextFile(".\outfile.csv")
  IE.Navigate "https://sites.google.com/site/<mysite/mypage>"
  wscript.sleep 4000
  tmpSTR = IE.Document.DocumentElement.innerhtml

  set objTable = IE.Document.GetElementByID("goog-ws-list-table")
  set colRow = objTable.GetElementsByTagName("tr")
  For each objRow in colRow
    set colFields = objRow.GetElementsByTagName("TD")
    If Not isnull(colFields) then recIndex = recIndex + 1
    outfile.Write recIndex
    For each objField in colFields
       outfile.Write "~ " & trim(replace(objField.InnerHTML,"&nbsp;",chr(32),1,-1)) 
    Next
    outFile.Writeline
  Next
outfile.Close
MsgBox "Done"

SoapUI assertions using XQurey/Xpath for numeric comparisons

For new test steps, SoapUI adds a single assertion by default. This verifies that the response is actually a SOAP response. Other than this, SoapUI has a rich collection of assertion types to verify the response. One of them is XQurey assertions, work closely with Xpath. Using XQuery assertions not only make verification automatic but also improves the accuracy. Here are a few examples on how to use especially in numeric verifications.

Say your response contains the following XML structure.

     
<items>
  <item>
    <price>1000</price>
    <date>12-12-12</date>
  </item>
  <item>
    <price>110</price>
    <date>10-10-10</date>
  </item>
  <item>
    <price>125</price>
    <date>Sat Aug 11 12:49:05 NZST 2012</date>
  </item>
</items>

• Counting number of item nodes using XPath assertion is straight forward.

               count(//item)

       which is going to be 3.

• Compare each price value.

• Calculate the total of price values.

Setting current date and other dynamic values in SoapUI properties

One less known but very powerful and interesting features of SoapUI is the ability to use scripting inside property values. During property substitutions where ever they are used, in request or in side assertions, script is executed and the value is substituted. They take the general form of

${=(script)}

For example, if you want to generate a random number in a property, create a property in the TestCase, say Random and set the value to

${=(int)Math.random()*1000}
(Since Math.random gives a random value between 0 and 1, the value of this expression is going to be a random number between 1 and 1000)

In the request, if you specify

${#TestCase#Random}
It is going to be a random number between 1 and 1000.

Taking a more complex (and probably useful) example, following is going to generate the current date in form of DD-MM-YY.

${=Calendar.instance.get(Calendar.DATE)+"-"+(int)(Calendar.instance.get(Calendar.MONTH)+1)+"-"+Calendar.instance.get(Calendar.YEAR)}

Note that months are starting from 0, so you have to add one to the month.

Or, if the date time string is desirable,

${=Calendar.instance.getTime()}

Running mavenized JUnit Tests from command line

It has been a while again - I was doing lot of work but habit of blogging faded away. Fortunately I started writing things down, specially simple commands that are essential as a tester... Hopefully I will rapidly publish them.

I wasn't a fan of mavenizing java - at least at the start- but it has changed. The thing with these tools is the relative complexity for smaller scale projects. As a tester, I feel this more since we need small and quick JUnit or Java test programs often.

 If the project is mavenized, it is impossible to build the java path manually. You can export the classpath using dependency plugin, and set the classpath. Go to the project folder and give the command,

mvn dependency:build-classpath -Dmdep.outputFile=cp.txt

This will create the file cp.txt with all the dependencies. Now add the output folder of your project, this is usually,

%ProjectDir%\output\classes in a mavenized project. Don’t forget to add the semicolon!

Now open the text file from notepad, press Ctrl+A and then Ctrl+C to copy everything. Go to command prompt and type

Set CLASSPATH=

Without pressing Enter, right click the mouse and select paste. That will copy all the contents of the file in to the command. Now press enter and the classpath is set.

To verify, type

echo %CLASSPATH%

This should print all the contents you had in cp.txt. Now run the junit tests with command,

java org.junit.runner.JUnitCore MyTestClass


Alternatively, you can use the command line switch -classpath too but the command line is going to be very long!

java -classpath (content of cp.txt) org.junit.runner.JUnitCore MyTestClass