Map files to generic table using reference data

Anonymous — Wed, 18 Jan 2017 11:11:38 GMT

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<J>=something</J>

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<K>==GenHeader<J>) //Column 3 of row5 is FIELD_NAME
                    {test=false;};
                row6]=row3<J>; //Column 7 of row5 is MAPS_TO
                };
           };
     };</J></J></K>

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

Re: Map files to generic table using reference data

Anonymous — Mon, 13 Feb 2017 02:15:21 GMT

Hi
Take a look d ynamic schema feature which is only available in enterprise subscription product, you might be interesting in it.
Regards
Shong

topic Map files to generic table using reference data in Talend Studio

Map files to generic table using reference data

Re: Map files to generic table using reference data