CSV Dates

[Anonymous]

Hi,
I am importing CSV files and have two date columns. In my CSV the two columns show numbers (43705, 45673, etc...). I am designating the two columns as Strings in the tFileInputDelimited and using tConvert to convert two strings into integers and then in tMap I am using routines.TalendDate.parsedate("MM/dd/yyyy", row1.Source) and routines.TalendDate.parsedate("MM/dd/yyyy", row1.Week) it still shows an error.



Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "42715"
at routines.TalendDate.parseDate(TalendDate.java:895)
at routines.TalendDate.parseDate(TalendDate.java:839)
at rehearsalproject.infocsv_0_1.infocsv.tFileInputDelimited_1Process(infocsv.java:2369)
at rehearsalproject.infocsv_0_1.infocsv.runJobInTOS(infocsv.java:3084)
at rehearsalproject.infocsv_0_1.infocsv.main(infocsv.java:2941)
Caused by: java.text.ParseException: Unparseable date: "42715"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:881)
... 4 more
[statistics] disconnected

[Anonymous]

Hi?
We have replied to your another topic. Please stick to one topic so that we can pay individule attention to your issue?

Best regards
Sabrina

[Anonymous]

Hi Shong,
I have a csv file in which the date field is defined as 42772.0. I tried everything tconvert and tmap. I set the field as String in the tfileinputdelimited and in tmap did TalendDate.parseDate("MM/dd/yyyy",row1.myDate) still it threw out an error:
UnparseableDate: 42772.0. I even tried the parseDatelocale("MM/dd/yyyy",row1.myDate,"EN") it still threw an error.


And in tconvert I set the column as float in tfileinputdelimited and then in tconvert I did Date MM/dd/yyyy and then tmap but even then it threw out an error.

I have read numerous posts on this but all futile.

I have read that knowing java can ease matters in Talend.

Can I use tjava or javaflex or javarow (I have not used these before nor do I know Java)? So if you suggest this route please do show a vivid way on how I can use here.

Please provide assistance on what do you recommended.

My current staging:

tfileinputdelimited ------------------tmap----------------toutputpostgresql

[Anonymous]

Hi 
the source data is number 43705, apparently, it doesn't map any date pattern. What is it? is it a milliseconds of a date? What date can map to the number 43705 in your case? 

Regards
Shong

[Anonymous]

Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "42722.0"
at routines.TalendDate.parseDateLocale(TalendDate.java:1011)
at rehearsalproject.infocsv_0_1.infocsv.tFileInputDelimited_1Process(infocsv.java:1837)
at rehearsalproject.infocsv_0_1.infocsv.runJobInTOS(infocsv.java:2616)
at rehearsalproject.infocsv_0_1.infocsv.main(infocsv.java:2473)
Caused by: java.text.ParseException: Unparseable date: "42722.0"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDateLocale(TalendDate.java:1009)
... 3 more

This is the screenshot from the csv I am loading into the database. Talend adds a zero at the end and makes it 42722.0.

 



When I MANUALLY change the number format to short date in the csv.

[Anonymous]

If you are able to change the correct date format, read the columns with Date type from CSV file, and set its pattern as "MM/dd/yyyy".

[Anonymous]

Hi Shong,
I know I can do it manually I want to know how I can convert the 42770.0 in Talend to the desired date MM/dd/yyyy. If I have hundreds of CSVs I cannot be manually changing the date formats of all them. Please provide assistance.

[Anonymous]

Hi
Which component do you use to read this CSV? To be honest, I don't know what's the formula can be used to convert the number 42770.0 to a Date, would you mind send me an example CSV file for further investigation.

Regards
Shong

[Anonymous]

Week                 Date
42722                42717
42722                42717
42722                42719
42722                42721

Here Shong,

Please copy and paste this into an Excel Worksheet and save it as an csv file. And then please let me know how would you transform the Week and Date fields as Date MM/dd/yyyy in Talend and then loaded into Database. Currently, I am having all kinds of difficulty converting the data types in Talend. I have tried parseDate, formatDate, parseDatelocale and ALL futile. I have lots of csv files I really require your assistance.

Thanks

[Anonymous]

Hi ,

I have read your question and concern ,Could you please let me know ,If date is "31/12/2016" what will be the "Date format" and "Week Date value" in your csv.? 
Why you are getting error  .b/c In order to run "Parsedate " routine  you have to pass the correct  and fixed format  (Your csv should have fixed format ) then this rountine will work.