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.
Is this a bug ? Any workaround ? Thank you.
-
zip file with graph and excel is attached. Thanks. -
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? -
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!! -
Hello, && operator as well as and works properly with Filter:
or!is_blank($0.is_int_test) and is_number($0.is_int_test)!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;
} -
I have solved my issue by applying filter to the whole column. here
Please sign in to leave a comment.
Comments 8