Specify Lookup Values Programmatically

In this example, you will learn how you can use Data Pipeline to perform data lookups in a file and combine the retrieved information with the specified values, based on a common field. It provides a flexible and dynamic way to augment data by combining external data sources with user-defined values, enhancing the dataset's completeness and accuracy.

The example can be used to enable ad hoc analysis or data processing tasks where the dataset needs to be augmented with specific values or attributes based on the user's requirements.

Input CSV files

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating
101,Reeves,Keanu,9315.45,10000.00,1/17/1998,A
312,Butler,Gerard,90.00,1000.00,8/6/2003,B
868,Hewitt,Jennifer Love,0,17000.00,5/25/1985,B
761,Pinkett-Smith,Jada,49654.87,100000.00,12/5/2006,A
317,Murray,Bill,789.65,5000.00,2/5/2007,C

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.FieldList;
import com.northconcepts.datapipeline.core.StreamWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.transform.TransformingReader;
import com.northconcepts.datapipeline.transform.lookup.BasicLookup;
import com.northconcepts.datapipeline.transform.lookup.LookupTransformer;

public class SpecifyLookupValuesProgrammatically {
    
    public static void main(String[] args) throws Throwable {
        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-01.csv"))
            .setFieldNamesInFirstRow(true);
        
        // create a lookup that returns a field named 'rating_description'
        BasicLookup lookup = new BasicLookup(new FieldList("rating_description"))
                .add("A", "Class-A")
                .add("B", "Class-B")
                .add("C", "Class-C");
        
        // use 'Rating' from example/data/input/credit-balance-01.csv
        reader = new TransformingReader(reader)
                .add(new LookupTransformer(new FieldList("Rating"),  lookup));  
        
        Job.run(reader, new StreamWriter(System.out));
    }
    
}

Code walkthrough

1. CSVReader is created corresponding to the input file credit-balance-01.csv.
2. The CSVReader.setFieldNamesInFirstRow(true) method is invoked to specify that the names specified in the first row should be used as field names.
3. BasicLookup is created to specify additional information about Ratings. A new field rating_description is added with three rows.
4. TransformingReader is a proxy that applies transformations to records passing through.
5. The lookup matches "credit-balance-01.csv".[Rating] with the first column of the BasicLookup instance. The reader contains the full data from credit-balance-01.csv along with the rating_description field from the BasicLookup. 
6. Data is transferred from the reader to the CSVWriter via Job.run() method. 

Output

-----------------------------------------------
0 - Record {
    0:[Account]:STRING=[101]:String
    1:[LastName]:STRING=[Reeves]:String
    2:[FirstName]:STRING=[Keanu]:String
    3:[Balance]:STRING=[9315.45]:String
    4:[CreditLimit]:STRING=[10000.00]:String
    5:[AccountCreated]:STRING=[1/17/1998]:String
    6:[Rating]:STRING=[A]:String
    7:[rating_description]:STRING=[Class-A]:String
}

-----------------------------------------------
1 - Record {
    0:[Account]:STRING=[312]:String
    1:[LastName]:STRING=[Butler]:String
    2:[FirstName]:STRING=[Gerard]:String
    3:[Balance]:STRING=[90.00]:String
    4:[CreditLimit]:STRING=[1000.00]:String
    5:[AccountCreated]:STRING=[8/6/2003]:String
    6:[Rating]:STRING=[B]:String
    7:[rating_description]:STRING=[Class-B]:String
}

-----------------------------------------------
2 - Record {
    0:[Account]:STRING=[868]:String
    1:[LastName]:STRING=[Hewitt]:String
    2:[FirstName]:STRING=[Jennifer Love]:String
    3:[Balance]:STRING=[0]:String
    4:[CreditLimit]:STRING=[17000.00]:String
    5:[AccountCreated]:STRING=[5/25/1985]:String
    6:[Rating]:STRING=[B]:String
    7:[rating_description]:STRING=[Class-B]:String
}

-----------------------------------------------
3 - Record {
    0:[Account]:STRING=[761]:String
    1:[LastName]:STRING=[Pinkett-Smith]:String
    2:[FirstName]:STRING=[Jada]:String
    3:[Balance]:STRING=[49654.87]:String
    4:[CreditLimit]:STRING=[100000.00]:String
    5:[AccountCreated]:STRING=[12/5/2006]:String
    6:[Rating]:STRING=[A]:String
    7:[rating_description]:STRING=[Class-A]:String
}

-----------------------------------------------
4 - Record {
    0:[Account]:STRING=[317]:String
    1:[LastName]:STRING=[Murray]:String
    2:[FirstName]:STRING=[Bill]:String
    3:[Balance]:STRING=[789.65]:String
    4:[CreditLimit]:STRING=[5000.00]:String
    5:[AccountCreated]:STRING=[2/5/2007]:String
    6:[Rating]:STRING=[C]:String
    7:[rating_description]:STRING=[Class-C]:String
}

-----------------------------------------------
5 records