One Star

Need to combine date & time fields from csv file into 1 datetime field

Hi everyone,
this is my first post, testing Talend the first day.
I have a csv file where someone split a datetime field into two separate fields, one date field and then another time field. Here are a couple of lines:
SCNUM;MID;DATE_USE;TIME_USE;SCRPREF
029591000453778;934028796;2014-10-01 12:00:00.000000;12:00:11;56
026221000953450;904247896;2014-10-01 12:00:00.000000;12:01:35;66
029301001661005;1229263596;2014-10-01 12:00:00.000000;12:01:56;66
I want to combine (concatenate?) the date of the first field (DATE_USE) with the time part of the second field (TIME_USE). So that I get something like this for the sample above:
029591000453778;934028796;2014-10-01 12:00:11;56
026221000953450;904247896;2014-10-01 12:01:35;66
029301001661005;1229263596;2014-10-01 12:01:56;66
How can I achieve this? Which component from the palette is doing such an operation, I couldn't find concat in there.
Thanks in advance,
Andreas
2 REPLIES
Employee

Re: Need to combine date & time fields from csv file into 1 datetime field

Hi,
Take a look at the tMap component. Can take database or file delimited connection (on input and output) and transform the data as you need.  You can map everything from the input table to the output table, but combine several fields with an expression in the output table (a CSV file in your case) similar to:
InputTable.Datestuff + " " + InputTable.TimeStuff
Other fields would map from input to output without any appending or other expressions
Another handy component (almost for doing the opposite of what you want though ;-) is tExtractDelimitedFields.  You can split out fields based on a delimiter your specify (e.g. ":", ",", "|", " " ...)
Hope that helps point you in the right direction,
GregD
One Star

Re: Need to combine date & time fields from csv file into 1 datetime field

Thanks for pointing me at tMap, it's a really useful component. However, I've not managed to solve my problem with it, part of it is that the document is missing examples and all that free text about @param is confusing IMO.
I've added the following to the expression builder to build a new variable:
TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",row1.DATE_USE+" "+row1.TIME_USE,true)
But when I click test it doesn't work. Also if I replace the concatenation portion with the true and enter the "" string again. I don't get what the right syntax is and the error msg: java.lang.NumberFormatException: For input string: "null" isn't really helping (I don't know Java, only Node.js).
Lastly, I'm confused as to where I should enter this and what the difference is between the var window in the middle (where I entered it) and the out1 window on the right side.
Checked the documentation but it's not telling what the difference is between both variable and out1. Totally confused :rolleyes: