Date incorrect parsing from Excel

CloverDX Platform
1

When I read a Excel file using XLS component, Date value Aug 07 2009 changes to 0007, August 07, 2009. Metadata is defined as String.

Is this a bug ? Any workaround ? Thank you.

2

What happens when you define metadata as Date?

3

If I define metadata as Date, it reads the Date without additional value in the front. Strange - it reads the value as one day less than the date in Excel. 6/1/2009 becomes 5/31/2009.

4

It’s really strange. Could you send me the file with data probe?

5

zip file with graph and excel is attached. Thanks.

6

Hello,
reading the dates as a Date type problem, seems to be related to the time zone problem (http://bug.cloveretl.com/view.php?id=3491). That means, that reading timestamps from xls file depends on time zone settings on the computer - dates on excel file are always treated as formatted in GMT time. As a workaround I can advice you the usage of Java Execute component, that sets the default time zone to GMT time:

import java.util.SimpleTimeZone;
import java.util.TimeZone;

import org.jetel.component.BasicJavaRunnable;


public class setTimeZone extends BasicJavaRunnable {

	public void run() {
		TimeZone.setDefault(new SimpleTimeZone(0, "GMT"));
	}

}

But the above, sets the GMT time for parsing and formatting all dates in your graph, so you can have a problem in different point of the transformation.

When I set the metadata type to String, the data was read properly, without any additional strings. What Clover version do you use?

7

Hi,

Please see the attached zip for recreating the issue. Right now I am proceeding with a workaround - substring function.

Also, Is it possible to combine both the ExtFilter in one in the attached graph ? I tried combining isnull, nvl with is_number. It does not help. Short circuit && is not available in CTL and I can’t convert the code to Java in ExtFilter.

Not a big issue . I am managing with two filters. Thought you might suggest a better solution.

Thanks!!

8

Hello, && operator as well as and works properly with Filter:

!is_blank($0.is_int_test) and is_number($0.is_int_test)

or

!is_blank($0.is_int_test) && is_number($0.is_int_test)

filters only 3 of 5 records.
If you know all the date formats in excel file you can use try_convert or str2date functions in Reformat node:

//#TL
date d;
string format = "dddd, MMMM dd, yyyy";
// Transforms input record into output record.
function transform() {
	d = try_convert($0.DateString,date, "MM/dd/yy");
	if (isnull(d)){
		d = str2date($0.DateString,format);
	}
	$0.* := $0.*;
	$0.Date := d;
}

If you set Reformat with above code after XLSReader, you can also use it as a Filter:

//#TL
date d;
string format = "dddd, MMMM dd, yyyy";
// Transforms input record into output record.
function transform() {
	if (is_blank($0.is_int_test) || !is_number($0.is_int_test)) return SKIP;
	d = try_convert($0.DateString,date, "MM/dd/yy");
	if (isnull(d)){
		d = str2date($0.DateString,format);
	}
	$0.* := $0.*;
	$0.Date := d;
}
9

I have solved my issue by applying filter to the whole column. here