topic Re: CSV Dates in Talend Studio

CSV Dates

Anonymous — Wed, 28 Dec 2016 15:09:11 GMT

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

Re: CSV Dates

Anonymous — Thu, 29 Dec 2016 08:43:40 GMT

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

Re: CSV Dates

Anonymous — Fri, 30 Dec 2016 03:58:22 GMT

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

Re: CSV Dates

Anonymous — Fri, 30 Dec 2016 10:34:28 GMT

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

Re: CSV Dates

Anonymous — Fri, 30 Dec 2016 13:23:32 GMT

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.

Re: CSV Dates

Anonymous — Fri, 30 Dec 2016 17:21:59 GMT

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".

Re: CSV Dates

Anonymous — Fri, 30 Dec 2016 20:54:18 GMT

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.

Re: CSV Dates

Anonymous — Sat, 31 Dec 2016 06:08:09 GMT

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

Re: CSV Dates

Anonymous — Sat, 31 Dec 2016 13:37:23 GMT

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

Re: CSV Dates

Anonymous — Sat, 31 Dec 2016 15:40:01 GMT

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.

Re: CSV Dates

Anonymous — Sat, 31 Dec 2016 23:05:19 GMT

Thanks Harkirat,
I want the date format MM/dd/yyyy as 12/31/2016. But I thought parseDate(String ----> Date) can be used here. So if I have 100 csvs I have to rectify all the date formats for all the csvs. Is there anyway I can have string as data type in tfileinputdelimited and in tmap do TalendDate.parsedate("MM/dd/yyyy".row1.MyDate) for all the date values? Manually formatting ALL the csvs to the proper date format will be quiet an hassle.

Re: CSV Dates

Anonymous — Tue, 03 Jan 2017 03:10:22 GMT

Hi Sabrina,

Can you help me with my problem please? I am facing difficulties converting data types.

Re: CSV Dates

Anonymous — Tue, 03 Jan 2017 05:48:22 GMT

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

It does not display corresponding date if I change the number of format to a short date, the number 42722 does not match any date format, so it can not be parsed to a date.
Regards
Shong

Re: CSV Dates

Anonymous — Tue, 03 Jan 2017 14:37:15 GMT

Thanks Sabrina, for being explicit with me.