This example shows how to use the CSVToTuple activity to convert a comma separated values (CSV) data file into a tuple list that can be used by other activities in OGSA-DAI. Assume we have file containing the following CSV data:

name,age,height
ally,37,1.5
ross,2,0.7
bob,20,1.4

The first line of this file is a header that specifies column names. The next three lines are data values. The name column contains text, the age column contains integers and the height column contains floating point numbers.

The following example code passes this CSV data as input to the TupleToCSV activity. The tuple list produced by the activity is then converted into XML Web Row Set format and returned in the request status. Finally the example code prints out the XML Web Row Set data . This is not the type of workflow one would normally execute but it is used in this example because converting the tuples to XML Web Row Set allows us easily read the tuple metadata so we can see what the activity has done. In practice output of the TupleToCSV activity would go as input to another activity that expects a tuple stream such as SQLParameterisedQuery or SQLBulkloadTuple. Also in practice the CSV data may not be passed to OGSA-DAI directly as an input from the client, instead it may be obtained from another activity such as ObtainFromFTP.

The example code is:

package uk.org.ogsadai.test.system.activity.transform;

import java.io.BufferedReader;
import java.io.Reader;
import java.io.StringReader;
import java.net.URL;

import uk.org.ogsadai.client.toolkit.DataRequestExecutionResource;
import uk.org.ogsadai.client.toolkit.PipelineWorkflow;
import uk.org.ogsadai.client.toolkit.RequestExecutionType;
import uk.org.ogsadai.client.toolkit.Server;
import uk.org.ogsadai.client.toolkit.ServerProxy;
import uk.org.ogsadai.client.toolkit.activities.delivery.DeliverToRequestStatus;
import uk.org.ogsadai.client.toolkit.activities.transform.CSVToTuple;
import uk.org.ogsadai.client.toolkit.activities.transform.TupleToWebRowSetCharArrays;
import uk.org.ogsadai.resource.ResourceID;
import uk.org.ogsadai.tuple.TupleTypes;

/**
 * Example client program using the CSVToTuple activity.
 *
 * @author The OGSA-DAI Project Team
 */
public class CSVToTupleExample
{
    public static void main(String[] args) throws Exception
    {
        // Reader to CSV data.  This could easily be a FileReader to read
        // the data from a file.
        String csvData = 
            "name,age,height\nally,37,1.5\nross,2,0.7\nbob,20,1.4\n";
        Reader reader = new StringReader(csvData);
        
        // Construct the CSVTuple activity
        CSVToTuple csvToTuple = new CSVToTuple();
        
        // Add the data
        csvToTuple.addData(reader);
        
        // Specify that the data has a header row.  This will be use to
        // form the names of the column tuples.
        csvToTuple.addHeaderIncluded(true);
        
        // Specify the type of data in each column.
        csvToTuple.addOutputTypesAsArray(
            new int[]{TupleTypes._STRING, TupleTypes._INT, TupleTypes._FLOAT});
        
        // Create a TupleToWebRowSetCharArrays to convert a tuple list to
        // XML Web Row Set format
        TupleToWebRowSetCharArrays tupleToWebRowSet = 
            new TupleToWebRowSetCharArrays();
        tupleToWebRowSet.connectDataInput(csvToTuple.getResultOutput());
        
        // Deliver the result to the requets status
        DeliverToRequestStatus deliverToRequestStatus = 
            new DeliverToRequestStatus();
        deliverToRequestStatus.connectInput(tupleToWebRowSet.getResultOutput());
        
        // Build the pipeline workflow
        PipelineWorkflow pipeline = new PipelineWorkflow();
        pipeline.add(csvToTuple);
        pipeline.add(tupleToWebRowSet);
        pipeline.add(deliverToRequestStatus);
        
        // Get hold of the DRER
        Server server = new ServerProxy();
        server.setDefaultBaseServicesURL(
            new URL("http://localhost:8080/wsrf/services/dai"));
        DataRequestExecutionResource drer = 
            server.getDataRequestExecutionResource(
                new ResourceID("DataRequestExecutionResource"));
        
        // Execute the workflow
        drer.execute(pipeline, RequestExecutionType.SYNCHRONOUS);

        // Print out the XML Web Row Set data
        while( tupleToWebRowSet.hasNextResult())
        {
            BufferedReader bufferedReader = 
                new BufferedReader(tupleToWebRowSet.nextResult());
            
            String line;
            while( (line = bufferedReader.readLine()) != null )
            {
                System.out.println(line);
            }
        }
    }
}

Running the program will produce the following output which is the tuple stream in XML Web Row Set format:

<webRowSet 
  xmlns="http://java.sun.com/xml/ns/jdbc" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/jdbc http://java.sun.com/xml/ns/jdbc/webrowset.xsd">
<properties>
<command></command>
<concurrency></concurrency>
<datasource></datasource>
<escape-processing>true</escape-processing>
<fetch-direction>1000</fetch-direction>
<fetch-size>0</fetch-size>
<isolation-level>0</isolation-level>
<key-columns></key-columns>
<map></map>
<max-field-size>0</max-field-size>
<max-rows>0</max-rows>
<query-timeout>0</query-timeout>
<read-only>true</read-only>
<rowset-type>1003</rowset-type>
<show-deleted>false</show-deleted>
<table-name></table-name>
<url></url>
<sync-provider>
<sync-provider-name/>
<sync-provider-vendor/>
<sync-provider-version/>
<sync-provider-grade/>
<data-source-lock/>
</sync-provider>
</properties>
<metadata>
<column-count>3</column-count>
<column-definition>
<column-index>1</column-index>
<auto-increment></auto-increment>
<case-sensitive></case-sensitive>
<currency></currency>
<nullable>2</nullable>
<signed></signed>
<searchable></searchable>
<column-display-size>0</column-display-size>
<column-label>name</column-label>
<column-name>name</column-name>
<schema-name></schema-name>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name></table-name>
<catalog-name></catalog-name>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
<column-definition>
<column-index>2</column-index>
<auto-increment></auto-increment>
<case-sensitive></case-sensitive>
<currency></currency>
<nullable>2</nullable>
<signed></signed>
<searchable></searchable>
<column-display-size>0</column-display-size>
<column-label>age</column-label>
<column-name>age</column-name>
<schema-name></schema-name>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name></table-name>
<catalog-name></catalog-name>
<column-type>4</column-type>
<column-type-name>INTEGER</column-type-name>
</column-definition>
<column-definition>
<column-index>3</column-index>
<auto-increment></auto-increment>
<case-sensitive></case-sensitive>
<currency></currency>
<nullable>2</nullable>
<signed></signed>
<searchable></searchable>
<column-display-size>0</column-display-size>
<column-label>height</column-label>
<column-name>height</column-name>
<schema-name></schema-name>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name></table-name>
<catalog-name></catalog-name>
<column-type>8</column-type>
<column-type-name>DOUBLE</column-type-name>
</column-definition>
</metadata>
<data><currentRow>
<columnValue>ally</columnValue>
<columnValue>37</columnValue>
<columnValue>1.5</columnValue>
</currentRow><currentRow>
<columnValue>ross</columnValue>
<columnValue>2</columnValue>
<columnValue>0.7</columnValue>
</currentRow><currentRow>
<columnValue>bob</columnValue>
<columnValue>20</columnValue>
<columnValue>1.4</columnValue>
</currentRow></data>
</webRowSet>

The highlighted sections show the column name and column type metadata and also the data values. This shows that the tuples have correctly been extracted from the CSV data and tuple metadata has been constructed from the header row and the column types the user specified.