One Star

Adding an extra column to the delimited input file

Hi,
I download tab delimited files from an ftp server. The number of columns in the files vary and are between 5 and 15 columns. Some of the files are as below.
YAHOO_keyword_20101127.txt --- contains 5 columns
YAHOO_detail_20101127.txt --- contains 8 columns
I am using tInputFileDelimited to read these files and change the encoding and delimiter of the file and writing to another file using tOutputFileDelimited.
I would like to add another column to the file created by tOutputFileDelimited component. The extra column will be the date (20101127)in the name of the file itself.
Here is an example of what I need to do.
Input File:
col1 col2 col3 col4 col5
a b c d e
b c d e a
Output File wanted:
col1 col2 col3 col4 col5 date_created
a b c d e 20101127
b c d e a 20101127
The date value comes from the file itself.
I would really appreciate if someone can help me with this. I was also trying to use the tmap component but was not able to achieve what I described above.
Thank you,
Santhosh.
29 REPLIES
One Star

Re: Adding an extra column to the delimited input file

In the tMap, add a column to the output table and use the following for the expression: TalendDate.getCurrentDate()
Make sure the that you select 'OK' for propagating changes. Or in the Output component, click on 'Edit Schema' to ensure the column is there (if not, then add it). Also, make sure the data types are consistent. Hope this helps!
One Star

Re: Adding an extra column to the delimited input file

Hi,
Using a source file we created a job and got the output. Next time when the updated source file comes with following updations:
1. with an extra column
2. names of 2 columns are different.

Can we use the same Job again using the same schema so that extra column is updated dynamically and no impact with 2 differing column names.
as it's an irritating and monotonous task to create the same job again.
Can any 1 help
Thanks in advance,
Anchit
One Star

Re: Adding an extra column to the delimited input file

Hi Anchit
Welcome to Talend Community!
Dynamic Schema is only supported by Talend Integration Suite(Commercial Version).
In TOS, you have to set schema manually.
Next time, please create a new topic for your question. Thanks.
Regards,
Pedro
One Star

Re: Adding an extra column to the delimited input file

Hi all,
I have tab seperated input files and I'm using tFileInputDelimited to read them. I want to add some columns to the output file,for which I've to do some caluculations.For that I'm using tmap.
But I don't know how to add new columns and do caluculations for those columns.
For example I've 4columns
col1            col2          col3            col4
1                  2              3                4
The output file should be as
col1            col2          col3            col4                col5=col1*col3             col6=col1*col2*col3
1                  2              3                4                             3                                     6
How could I acheive it? Please anyone provide the solution to me.
Thanks in advance.                                    
Four Stars

Re: Adding an extra column to the delimited input file

Hi,
Please check the example given in documentation below
http://www.talendforge.org/tutorials/tutorial.php?idTuto=7
In tMap output metadata below add new column and use row1.col1*row1.col3 for value in output column.
Vaibhav
One Star

Re: Adding an extra column to the delimited input file

Thanks for your reply vaibav,
I tried as mentoned in the link only.But I could not get want I want. While adding newcolumn it is showing like column5 cannot be resolved to be a variable.
What could I do?
Four Stars

Re: Adding an extra column to the delimited input file

Please show the screenshot of your tMap
Vaibhav
One Star

Re: Adding an extra column to the delimited input file

Thanks for your prompt reply.
If I map the new column with input columns I'm getting output. But my doubt is I want to do the caluculation on previous column output
for example column4(output)=couln3*50
And one more thing I need to execute some if else conditions in my job for new columns
How could I do this?
One Star

Re: Adding an extra column to the delimited input file

One more is
For example I've marks list
if(marks<35)
status=30%;
else if(50>marks>35)
status=50%
where status is new column which I have to create.
how to do it?
Four Stars

Re: Adding an extra column to the delimited input file

where status is new column which I have to create.
how to do it?
Inside output column you can write expression
input_row.marks < 35 ?"30%"Smiley Sadinput_row.marks > 35 & input_row.marks < 50)?"50%":some condition
This is cascaded if-then-else
previous column output = input column for that column, take that directly...
Thanks
vaibhav
One Star

Re: Adding an extra column to the delimited input file

previous column output = input column for that column, take that directly.
When I'm using that directly it is showing me as error.
Four Stars

Re: Adding an extra column to the delimited input file

Something is wrong... can you show the screenshot of tMap..
One Star

Re: Adding an extra column to the delimited input file

Sorry I could not able to add the screenshots.
Four Stars

Re: Adding an extra column to the delimited input file

Use Post reply and keep the size of your screenshot as per their instruction... now it is very simple..
Click and paste your screenshot..

vaibhav
One Star

Re: Adding an extra column to the delimited input file

Sorry I could not get to attach screenshot.
One Star

Re: Adding an extra column to the delimited input file

I'm getting error when there is no mappaing to the new column which I've created in output columns and adding expression.
Four Stars

Re: Adding an extra column to the delimited input file

That is not an issue, can you show the expression used in additional column?
vaibhav
One Star

Re: Adding an extra column to the delimited input file

Thanks for your reply Vaibav,
Example I'm using newcolumn=column5*100
where column5 is previous column in output.
Not only the multiplication I could not get any expression.It is showing me error as
 Newcolumn cannot be resolved or not a field.
Four Stars

Re: Adding an extra column to the delimited input file

You must have added newcolumn in metadata in tMap output
- remove newcolumn= from expression and i assume that column5 is your name of previous column in metadata...
I am not sure why you can't display the screenshot of tMap, you can hide the fields if you have security issue... This way you can save time and efforts of many people.
thanks
vaibhav
One Star

Re: Adding an extra column to the delimited input file

Thank you Vaibav,
I'm not able to upload screenshots into the forum.I don't know how to do?
I'm tried to upload already but could not able to upload.
Ya I'm adding newcolumn in the metadata and not including newcolumn= while writing expression.
When my expression includes input column which I mapped,I could get output.
Example column5= (row1.column2)*10 got ouput.
But when column6=Column5*10
(column5 is previous output which is not mapped from input)
Four Stars

Re: Adding an extra column to the delimited input file

Use column6 = ((row1.column2*10)*10)
vaibhav
One Star

Re: Adding an extra column to the delimited input file

Ya I tried like that I got ouput but if there is no relation to newcolumn with the input columns what could be the solution.
I told you just example that is not my exact requirement.
If I want to add some if else condition to my input how could I write?
Four Stars

Re: Adding an extra column to the delimited input file

Use expression like
input_row.marks < 35 ?"30%"Smiley Sadinput_row.marks > 35 & input_row.marks < 50)?"50%":some condition

Vaibhav
Four Stars

Re: Adding an extra column to the delimited input file

If you have trouble in using tMap...
take tJavarow next to tmap and perform further processing... this will be simple...
vaibhav
One Star

Re: Adding an extra column to the delimited input file

Tahnks Vaibav,
I'll try this definately
We can write our own code and can execute ah..
In this no problem to add new column right
One Star

Re: Adding an extra column to the delimited input file

Hi Vaibav,
I tried using tJavaRow after tMap but no use.What could I do?
If u give me your mail Id I would send my serreenshots to you.
Thank you
Four Stars

Re: Adding an extra column to the delimited input file

Please Help!
Good afternoon:
I am extremely new to Talend and do not know Java.  I will not be using Talend much in future as our IT department will take over.  But I have spent the last 1.5 weeks studying the guide and the components guide and am pretty clueless as to how to create the job to accomplish my task.  I am not even sure I can perform it within the parameters my boss has given me, as he knows less about Talend than I do.  Here is my task:
Overall task, take 39 different 97-2003 excel files, different headings, different numbers of rows, compile into one upload to a redshift table.
Facts:
1. The redshift table will have all the various fields from all the different excels.
2. The excels do not have all these fields.
3.  I have assumed I must bring in the raw data, and perhaps tmap it to another table whose schema has all the fields listed in the redshift table.
What is the best flow to open an excel file with 10 rows, append it to another "table" that has 30 rows, and hard code the value into the 20 fields that do not have an input value from the original input file, then append to one output that will be uploaded to redshift.  I have mapped a schema for each raw data excel file as each one will have different hardcoded values for the empty fields.  I have experimented a zillion ways, it seems, but because I do not know Java, I am unable to figure out the error codes.  I also apologize if I should have started a new thread, but I found this one by googling for "adding an extra column to the delimited input file".  Thank you.
I can attach samples if needed but basically:
excel raw data rows for file 1:                               tmap to output or ????
1  2  3  4  5  6  7  8  9  10                        1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16
m k  l   a c  g  h  k  n  6                          m k  l   a  c   g  h  k  n  6   JJ    kk  ll   mm no  cc
One Star

Re: Adding an extra column to the delimited input file

I have a Use case where i have  5 columns say a,b,c,d,e in a file Input.csv which needs to be looked up for validation in a master file say Master.csv. The resultant output should be a csv where all the matching records from the master file should be retrieved.
I used a tmap to apply the processing logic and join conditions but one thing i need in the resultant file is that whenever the input file is failing to find a matching record in master file an additional column should be added in the resultant file wherein the status should mentioned "Failed as no matching records for "a,b.c...etc"   are found.This i am unable to do using tmap.
I have added additional 5 columns but what should i add as logic in the addtional file columns so that for every look up failed criteria the resultant output should tell me that it failed as this value did not match.
psl suggest
One Star

Re: Adding an extra column to the delimited input file

Hi,
scenario is
1.first download the file from ftp it has three columns(name,address,state) and in table Code(id,uniquecode,used) and uniquecode  is some integer values 
2. we have to add a coulmn to input file uniquecode(name,address,state,uniquecode)
can you please help me how to dump the column from a table.
Many regards,
Rasool.