CloverETL is now CloverDX - Learn Why

Read and compare values from csv

Discussion on developing CloverDX engine, transformation components etc.

IamGiuly
Posts: 1
Joined: Sat Nov 24, 2018 2:58 am
Location: Ancona

Read and compare values from csv

Postby IamGiuly » Sat Nov 24, 2018 3:45 am

Hi guys!
I have to transform one field of an input DataRecord only if another field in the same DataRecord is equal to a specific value read from csv file.

The transformation have to compare "UserCountryName" input field with a value read in the csv and if there is a match, it has to write the "UserCountryGeocode" output field with the respective value read in the file.
For example:

The metadata record input have this form:

ID | UserName | Sex | Text | UserCountryName | UserCountryGeocode | Opinion | EventImpact

The csv file have this form:

UserCountryName, UserCountryGeocode
Italy, 41.87 12.56

I used a Reformat component with a Java class, this is the code:

Code: Select all

import com.opensys.cloveretl.ctl.CTLUtils;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import org.jetel.exception.ComponentNotReadyException;
import org.jetel.exception.TransformException;

public final class CSVReader extends org.jetel.component.CTLRecordTransform{

   private String csvFile = "...."; //Path of file
        private BufferedReader br = null;
        private String line = "";
        private String cvsSplitBy = ",";
             
      @Override
      public void globalScopeInit() throws ComponentNotReadyException {
         
      }       

      @Override
      protected int transformDelegate() throws ComponentNotReadyException,
            TransformException {
         
         
         String metadataFieldNames[] = getInputRecord(0).getMetadata().getFieldNamesArray();
         int metaFieldNames_Lenght = metadataFieldNames.length;   
         boolean StopScanCSV=false;
         int i=0;
         
         
         while (i<metaFieldNames_Lenght){
            
            
            if (metadataFieldNames[i]=="UserCountryName"){
               
               try{
                  
                  FileReader r=new FileReader(csvFile);
                  br = new BufferedReader (r);
                  while (((line = br.readLine())!=null)&&!(StopScanCSV){
                     String lineCSV[] = line.split(cvsSplitBy);
      
                     if(lineCSV[0] == CTLUtils.toString(getInputRecord(0).getField("UserCountryName").getValue())){
                        getOutputRecord(0).getField("UserCountryName").setValue(lineCSV[0]);
                        getOutputRecord(0).getField("UserCountryGeocode").setValue(lineCSV[1]);
                        StopScanCSV=true;
                     }
                  }
                  
                  
                  //No-match
                  if !(StopScanCSV){
                     getOutputRecord(0).getField(metadataFieldNames[i]).setValue(getInputRecord(0).getField(metadataFieldNames[i]).getValue());
                     getOutputRecord(0).getField(metadataFieldNames[i+1]).setValue(getInputRecord(0).getField(metadataFieldNames[i+1]).getValue());
                     
                  }
                     
                  i+=2;
                  
                 } catch (IOException e) {
                     e.printStackTrace();
                 } finally {
                     if (br != null) {
                         try {
                             br.close();
                         } catch (IOException e) {
                             e.printStackTrace();
                  }}}}
                  
   getOutputRecord(0).getField(metadataFieldNames[i]).setValue(getInputRecord(0).getField(metadataFieldNames[i]).getValue());
            i+=1;
         }
               
         return 0;
   
         }


}


I'd try to write only the String lineCSV[] to the output port and it works fine (the problem isn't the csv). But the entire code doesn't work correctly.
Other values (ID, UserName, Text and UserCountryName, Opinion, EventImpact) are write correctly but UserCountryGeocode not (i read always null values).
There is anyone can help me please?
Thank you!

Ps. Sorry for mistake in my english! I'm not so well.

jandikovae
Posts: 39
Joined: Fri Nov 04, 2016 8:51 am

Re: Read and compare values from csv

Postby jandikovae » Wed Dec 05, 2018 10:30 am

Hi,

We have tried to review your java code but for some reason, we haven't been able to run it as it is and review the data flow. Would you mind attaching some example graph so that we can take a closer look?

However, I would like to suggest you another solution using functions and components in the Designer itself without any need to use Java code at all. Please take a look at the graph that I have attached.

I have used so-called Simple Lookup. The lookup reads the list of Countries and its Geocodes (the csv) and adds them to the original data using component called LookupJoin. The simpleLookup takes the file as it is using metadata called "geocode" in my example graph. However please be aware that all data records stored in the simple lookup table are kept in memory. For this reason, to store all data records from the lookup table, sufficient memory must be available.

Please let me know if this solution meets your needs or if you have any additional question or concern. Thanks, Eva
Attachments
geocode.txt
Lookup Input
(57 Bytes) Downloaded 1 time
opinioninput.txt
Input
(165 Bytes) Downloaded 1 time
geocode.grf
Graph
(3.58 KiB) Downloaded 1 time
---
Eva Jandikova
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com