Forum Discussion

richie's avatar
richie
Community Hero
9 years ago

Extract Specific Value from nonXML datafile to provide JDBC request with query filter value

Hi,

 

This is very similar to a previous post I submitted and nmrao and Radford really helped me out, but now instead of XML, my testdata files are | delimited text files.

 

I need to parse a test data file (| delimited text file) and then scrape the content of the testdata file via groovyscript to hold the CustRefID value to then pass that value to the JDBC step to query the database after I've posted to the web service.

 

For my previous request (with XML) Radford and nmrao told me to use the following

 

import groovy.util.XmlSlurper   
def xmlText = context.expand( '${DataSource#FileContents}' )  //where FileContents is the property set on my DataSource object)

def RecordInput = new XmlSlurper().parseText(xmlText)
log.info(VenueInput.VenueProfile.@VenueID)

Now the above works great - but now I need to change this so it works with | delimited text files

 

The format of the file is as follows:

 

CustRefID|title|firstname|lastname|addressline1|addressline2|addressline3|postcode
ID0000001|MR|RICH|JONES|5 WHATEVER WAY|HODGE HILL|BIRMINGHAM|B36 9LB
ID0000002|MRS|ALISON|WHITEFORD|6 READY ROAD|WHITTAKER|FOUR OAKS|B74 4TH
Rows=2

 

As you can see - there is a header (which lists the attributenames), there are 2 data rows and a footer that indicates the number of data rows.

 

I need to extract the first value in each data row - so for the example above I need to grab the values 'ID0000001' and 'ID0000002'

 

I'm sorry to have to ask such a basic question - but I'm struggling - I've found some groovy to read the contents of the file from the directory e.g.

 

String fileContents = new File('/path/to/file').getText('UTF-8')

but I haven't found anything that allows me to source a specific value from  a certain position in the file (e.g. first value in each data row) and also my coding is so bad I'd like to try and keep things as consistent with my other test cases as possible - e.g. 

 

Thanks to anyone who can help!

 

26 Replies

  • Radford's avatar
    Radford
    Super Contributor
    9 years ago

    You may or may not feel this over complicates things but I would use the Apache Commons CSV java library, this will deal with the parsing of your data.

     

    Just download the jar file from the link above and pop it in your bin/ext directory and then the code below is an example of how to parse your data.

     

    import org.apache.commons.csv.CSVFormat
import org.apache.commons.csv.CSVParser

def pipeDelimText = '''CustRefID|title|firstname|lastname|addressline1|addressline2|addressline3|postcode
ID0000001|MR|RICH|JONES|5 WHATEVER WAY|HODGE HILL|BIRMINGHAM|B36 9LB
ID0000002|MRS|ALISON|WHITEFORD|6 READY ROAD|WHITTAKER|FOUR OAKS|B74 4TH'''

def pipeDelimTextStringReader = new StringReader(pipeDelimText)

CSVFormat pipeDelimFormat = CSVFormat.newFormat('|'.toCharacter()).withRecordSeparator('\r\n').withHeader()

pipeDataParser = new CSVParser(pipeDelimTextStringReader, pipeDelimFormat);

pipeDataParser.getRecords().each {record ->
	log.info('Record ' + record.getRecordNumber().toString() + ': Cust Ref ' + record.get('CustRefID') + ' is ' + record.get('title') + ' ' + record.get('lastname'))
}

     

    If you need to loop through your pipe delimited data then this could be put in a Groovy Data Source and paired with a Data source loop.

    • Radford's avatar
      Radford
      Super Contributor
      9 years ago

      This issue has interested me and because I was looking at the Groovy DataSource for another issue I thought I'd turn this into a full example, it may possibly help you or perhaps someone else. I've attached a full stand-alone sample project, where you will just need to go to the DataSource step and update the path to your file (assuming you have already added the Apache commons CSV library jar file to you bin/ext directory).

       

      The things I like about this solution is:

       

      1. Let the CSV library deal with parsing out your data (why reinvent the wheel?)
      2. It minimises the Groovy, which is all in one place.
      3. You can now access your pipe delimitted data in the standard Ready API way as it is now just properties of the DataSource test step within a DataSource Loop.

      Below is a screen shot from the attached project of the Groovy DataSource and you can see that your data has been parsed at the bottom:

       

       

       

       

       

       

      • nmrao's avatar
        nmrao
        Community Hero
        9 years ago

        Well, here are my comments :
        1. The code presented by me is much simpler, readable and condensed than other samples presented above.
        2. No custom library is used.
        3. No reinvention here, just groovy, I believe (as only separator changed and it is applicable even if other libraries are used) at least for this case requested by original poster.

        If at all custom library needs to be used, below is very simplified and groovified:

        https://github.com/xlson/groovycsv

  • nmrao's avatar
    nmrao
    Community Hero
    9 years ago

    You should be able to get it from this thread. Only thing is to change the separator and take only what you need:

     

    def lines = new File('data.txt').readLines()
lines.eachWithIndex { line, index ->
    if (index) {
    	def data = line.split('\\|')*.trim()
	log.info data[0]            	   
    }	    	
}
  • IgorG's avatar
    IgorG
    Icon for Staff rankStaff
    9 years ago

    So, this suggestion might be a bit weird with all the cool groovy scripting in this topic, but did you try File DataSource? It seems like a solution for this kind of stuff without Groovy.

    • Radford's avatar
      Radford
      Super Contributor
      9 years ago

      You learn something new every day! :-)

       

      I didn't realise that the file data source would do the splitting. I think it's usually always better to use the the in-built functionality rather than custom scripting, though I would be the first to admit that sometimes I'm guilty of resorting to Groovy too quickly.

       

      An aside about the external libraries (appologies for going off topic a bit)...

       

      groovyguy, regarding your comment about other users having to remember to add the libraries. In our environment Ready API is installed via pc management software, thus just get the software to add all the required jar file, so we luckily don't have to worry about that, but a very valid point none the less. Interstingly though this discussion has got me thinking about the issues/overheads of using external libraies and I've just spotting that Ready API actually installs a large number of the Apache Commons libraries into it's own lib directory, IgorG, is there an "official" SmartBear line about referencing the jars in the lib directory from user Groovy scripts? Do I need to go back and investigate if I need to bother to add them to my ext directory after all?

       

      nmrao, thanks for the pointer to the "GroovyCSV" library I'll be sure to check it out. When looking for external libraries I tend to first look at the Apache Commons web site and stop there if something fits the bill, thus I know I do miss out on other alternative libraries.

       

      • nmrao's avatar
        nmrao
        Community Hero
        9 years ago
        Radford, thank you for consolidated reply.

        Apache-commons is, of course, a good place for the libraries for the people who uses Java. However, if some one uses pure groovy code, most of the times either not required or less and readable code with groovy without even using any libraries.
    • nmrao's avatar
      nmrao
      Community Hero
      9 years ago

      IgorG, thank you for the link, that seems to be good.

      How to access the certain columns? Property Expansion?
      Have to use just like Data driven?

      • IgorG's avatar
        IgorG
        Icon for Staff rankStaff
        9 years ago

        nmrao Pretty much. It reads the file line-by-line and returns the column values as properties. The you use property expansions and a DataSource loop to go through the data.

         

         

        Not sure how you handle a footer in this scenario through, could probably remove it beforehand or set the End Row value somehow.

  • groovyguy's avatar
    groovyguy
    Community Hero
    9 years ago

    The first thought that comes to mind is a Datasource, if you can customize delimiters for that. Barring that, my next recommendation is writing a custom groovy script to pull in the data. You could likely build a 2D collection for harvesting, and tokenize based off of your delimiter. Hopefully that helps.

    • richie's avatar
      richie
      Community Hero
      9 years ago

      hahaha!

       

      groovyguy wrote:

      The first thought that comes to mind is a Datasource, if you can customize delimiters for that. Barring that, my next recommendation is writing a custom groovy script to pull in the data. You could likely build a 2D collection for harvesting, and tokenize based off of your delimiter. Hopefully that helps.

       

      Erm.....I appreciate your response but I barely understand your answer - never mind put it into practice!

       

      Think I need to do some more reading before I come back to your answer,

       

      Thanks anyway!

       

      richie

      • groovyguy's avatar
        groovyguy
        Community Hero
        9 years ago

        richie: Once I get a chance to get to my desk later today, I can mock up a groovy script that could import the example data file you provided. That might give you somewhere to start.