group records and create file for each record

[Anonymous]

Hi,

I currently have a Excel macro enables workbook which reads the data in file then groups the data based on certain criteria, say for example Name and timesheet data. It then creates an individual workbook for each of these groups and names the workbook based on the name and the timesheet value column. It sorts the data first then inserts new line after each group, then copies this data and paste into another workbook, save and closes the file (current workbook), now read the next set of data in the original file and copies and paste the next set of group data and puts in new book and so on utill it reaches end of file with no data and closes the application.

I have been asked to rewrite this job in Talend as the timesheet data we longer receive in excel. The timesheet data comes in a pipe text file. I have been able to create a excel file from the pipe text file.

However, the problem I am having now is I need to read the output file which has been produced from the pipe text file and group this data and create a individual file for each set of group data. I have searched and cannot find any that meet my requirements. Please see screen shots. I will be ignoring Column 0 = "I". Lucky the data is already sorted for me this time, however  might not be next time.

Now I want to group the data by the name and date, and produce individual output (excel file) where it will need to name these file based on certain column names.

I am using talend 6.5 therefore do not have tMatchgroup and cannot use tXLMAP as this requires joins. I just want the job extract the data from the pipe file then convert  the file,  then group the data and produce individual files using one single output component as I do not know how many rows the text file would have. I thought I had found a way but the final output file is blank.

Please any help would be appreciated. 

[Anonymous]

I figured out the incorrect naming convention, the aggregated component (input outcome) i had different columns selected.

[Anonymous]

Fantastic job 🙂

A good idea will be to always have unique names for your components rather than Column1, Column2 etc. This will help in easy debugging.

Could you please mark the topic as solution provided as the issue has been resolved now?

It will help the Talend community for future reference.

Warm Regards,

Nikhil Thampi

[Anonymous]

@nthampi

On Sub Job OK was not available to me through the fileinput, I have now used "on sub job ok" from tfilelist.
With regards to the aggregated component, I have renames the fields and used the same columns as your job to create the grouping but the grouping is not working and not outputing any data in the text file.

LogRowMapping.PNG
Issue.PNG
Aggreated.PNG

[Anonymous]

Hi,

     On SubJob Ok will eb available only for the first component in a Subjob. 

      The data fetch is happening correctly in the sample job I had given. So the issue could be some minor mistakes when you recoded it at your end. Please try tlogrow to see the results after connecting each component to verify the effect of your changes.

Warm Regards,

Nikhil Thampi

[Anonymous]

I think the issue maybe the input file which I using for mapping for the end result. I have used a variable for file path assuming it will go back to tfilelist_1, but I don't think it works that way, is that correct?

FielPath.PNG

[Anonymous]

Hi,

    I have attached the excel file which I used based on the data you shared in the post. Could you please compare all the files you are having is having same format.

    Once you connect a tfilelist -> tfileInputExcel , you will have to use the variable ((String)globalMap.get("tFileList_1_CURRENT_FILE")) to get the file name from file list.

Note:- Change the value of number after tfilelist in the variable based on the component actual name.

Warm Regards,

Nikhil Thampi

[Anonymous]

I convert the pipe text file to excel, then use this file to read the data and produce excel file based on aggregation. I do not need to exclude "I" as the extraction ignore this column due to the data type.

SAP.txt
SAP.txt
TEST.zip

[Anonymous]

Please see my below reply where I have attached my project and text file. Thank you

[Anonymous]

Hi,

      Once you read the distinct values, you need to pick them from all of your input files. So you will need another tfilelist in your second subjob.

      In between, it is a good practice to arrange the components in straight line rather than in zig-zag manner for better readability.

       I have added tFilelist_2 intentionally in U shape so that it is is easily noticeable. Once you understand the flow,please make the flow as a straight line.

Since  you have got the solution for your original query, could you please mark the topic as solution provided? It will help to enrich the Talend community for later references.

Warm Regards,

Nikhil Thampi 

[Anonymous]

Apologies, I had some other components which was deactivated. the job flow has been straighten for readability. I have also marked original query as "Accept as Solution".
I have used the the project you craeted and just reset the fields to the "alias" and that does not work in grouping the data. The issue is on grouping.