Map files to generic table using reference data

Four Stars

Map files to generic table using reference data

Hi everyone,
I have recently been asked by my company to explore Talend as a way to perform ETL and my job right now is to create a job that imports any type of file, read it with a reference table storing its information (number of fields, separators, etc.) and stores it in a generic table after reading in a reference data table the field in the generic table each field of the original table goes to. If the explanation isn't good enough, you can check the screenshot I posted below (part that requires attention is the one after OnSubjobOk, everything else works).
 
I can already successfully import the files, read the information on their separators in REF_FILE_MAPPING, store all the information in a GENERIC_FILE_LOAD table and have the reference data in REF_FIELD_MAPPING with, among other columns, FIELD_NAME (field name of original file) and MAPS_TO (field of the final table GENERIC_FILE that field goes to).
But I'm having troubles in how to do this mapping from a conceptual and also programming level. I tried to do it with tMap but I couldn't figure it out. I also come from MATLAB where I could just access tables with
tablename[][] and I was expecting to do it with, say row6=something

but that doesn't work and I don't know how I can access the tables I import with tOracleInput.
My code on t_JavaFlex_2 is:
Start code:
int NumFields = Integer.parseInt(globalMap.get("NumFields").toString());
int NumRecords = (Integer) globalMap.get("tFileRowCount_1_COUNT");
String[] GenHeader=new String;
Main code:
row6.FILE_NAME = (String)globalMap.get("tFileList_1_CURRENT_FILE");
row6.PROCESSED_DATE_TIME=TalendDate.getCurrentDate();
//row6.LOAD_TIMESTAMP=Integer.parseInt(globalMap.get("LoadTimestamp").toString());

for (int i=2; i<NumFields;i++){
GenHeader="COL" + StringHandling.RIGHT("00"+(i-1), 2); //Generic Columns on both GENERIC_FILE_LOAD AND GENERIC_FILE are called COLXX
};
for (int i=1; i<NumFields; i++){ //Not wanting the Header
      for (int j=2; j<NumFields; j++){ //First two columns are already stored before
           boolean test=true;
           for (int k=1; k<NumFields-1 && test; k++){ //iterating lines of REF_FIELD_MAPPING
                if (row5==GenHeader) //Column 3 of row5 is FIELD_NAME
                    {test=false;};
                row6]=row3; //Column 7 of row5 is MAPS_TO
                };
           };
     };

If this is too confusing, feel free to ask for further information.
Thank you!
Community Manager

Re: Map files to generic table using reference data

Hi
Take a look dynamic schema feature which is only available in enterprise subscription product, you might be interesting in it.
Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business