Hi Am new to talend.....I need a little help with this issue am going through...
I have an issue with my date format...when I pull my excel file into talend which contains the dates in different formats,it throws errors while transferring the datas into tMSSqlOutput . Am not able to get all the rows in my file.I just want to transfer the entire data along with the dates in the tFileinputExcel file to tMSSqlOutput with the date format in dd/MM/yyyy. I need to do this without changing the date format in my original excel file. how to do this in talend? I have attached the images.... Please do help me Thanks & Regards, Apk.
Hi Shong, Thank you!! I also have another problem as my date columns contains null values to it. The sqlserver throws error. How to solve it and send the entire date column to the target. Thanks & Regards, Arnold.
If the data type of column is datetime in your table, only dates between January 1, 1753 and December 31, 9999 or Null are accepted, so, you can set it with Null or a default datetime if the input data is null, for example: row1.c1==null?nullTalendDate.isDate(row1.c1,"yyyy-MM-dd")?TalendDate.parseDate("yyyy-MM-dd",row1.c1)TalendDate.isDate(row1.c1,"dd-MM-yyyy")?TalendDate.parseDate("dd-MM-yyyy",row1.c1):TalendDate.parseDate("MM-dd-yyyy",row1.c1))) Shong
---------------------------------------------------------- Talend | Data Agility for Modern Business
Hi, am having a trouble with this date format. I need my date to be in dd/MM/yyyy format (date alone no time).My date column is in string format in my excel sheet some of the rows in my date column are empty or null. Am able to change the format in Tmap from string to date by using the following code
CODE : (row1.Anniversary_Date!=null)?TalendDate.parseDate("dd/MM/yyyy",row1.Anniversary_Date):null the output runs in tLogRow and shows all rows,but when I pull the data into sqlserver 2008 it shows date format error. I also have another problem as my date columns contains null values to it. The sqlserver throws error. How to solve it and send the entire date column to the target. I have attached my work and error in this post.
Hi, thank you bkar81 & xdshi. But what is the solution to make the date column to get into the target (MSSQL)??? bkar81 can u brief the solution by how to apply the change in both the formula & datatype as well
Change the format to (row1.Anniversary_Date!=null)?TalendDate.parseDate("MM/dd/yyyy",row1.Anniversary_Date):null Check the target DB Datatype in the target table in tMap component. By default it will be dd/MM/yyyy. Change it to MM/dd/yyyy. It will ask for propagate changes. Accept it. Check the same in the MSSQLOutput column as well. Let us know if it works.
Hi still the problem is there.i have attached the error along with this post. My date column has some null also.i need all the rows to be loaded irrespective of null or not null. give me a solution. Thanks & Regards, Arnold.
Hi still it is showing the same error as i said in my first post i have few date column like DOB,Anniversary date,Contract Expiration date,Hire date,Leave date in my excel sheet.Except the DOB and Hire date rest of the date columns have some null values,each of the column has different data types. So i need all the rows in all these date columns to be taken in to the target system. And another thing is... For example if we take the column DOB, in it one row has Date format and another row has string format thus in a single Column DOB, i have multiple rows that have multiple formats like Date,string etc Example representation DOB **** 12/01/2001 ---> date datatype 02/08/1988 ---> String datatype 05/1/2007 ----> String data type 02/05/2013 ----> date datatype considering all this, how to bring a solution???
Please find the screenshots below While loading, I set the datatype of the date column to String. Image 1: The input data in Excel (string & date format) - you can see by the difference in alignment Image 2: The initial mapping to parse the data from Excel Image 3: Oops, an error occurred - Unable to parse the date (Note the highlighted date format) Image 4: Analyze why it is throwing the error (removed the tMap and directly connected to tLogRow) Image 5: Got it, there is the problem... few items are parsed properly and few not parsed properly Image 6: A slight tweak in my code in tMap - used SimpleDateFormat (Java code), but we can also use TalendDate.parseDate Image 7: Viola!!! All dates are parsed properly. Formula used inside tMap (row1.DateCol_InString!=null)?(TalendDate.isDate(row1.DateCol_InString,"dd/MM/yyyy")? TalendDate.parseDate("dd/MM/yyyy",row1.DateCol_InString):new SimpleDateFormat("EEE MMM dd hh:mm:ss z yyyy").parse(row1.DateCol_InString)):null or (row1.DateCol_InString!=null)?(TalendDate.isDate(row1.DateCol_InString,"dd/MM/yyyy")? TalendDate.parseDate("dd/MM/yyyy",row1.DateCol_InString):TalendDate.parseDate("EEE MMM dd hh:mm:ss z yyyy",row1.DateCol_InString)):null I checked whether the date I am parsing is in the desired format using TalendDate.isDate function If it returns true, then parse normally, else do the custom parsing. Here you go: For Date in the format = "Wed Jan 01 00:00:00 GMT 2014", I used the format "EEE MMM dd hh:mm:ss z yyyy" So check out the same and find out which format the output is coming. In a nutshell, use isDate and parseDate in multiple if conditions depending upon your need. Note: If the destination server is in different locale, this would not work (example, the above example is in UK format. This may not be the same if I execute this in other machine where the date settings are different)