Using etl.date.format in CSV

[Daniel G. Kluge]

Hello there,
I'm trying something, and it doesn't quite work as I expected. My Code
is along the lines of:

<query connection-id="oracle">
SELECT sysdate FROM dual;
<script connection-id="csv">
${etl.date.now('yyyyMMdd')}, ${etl.date.format($sysdate,
'yyyyMMddHHmmss')}
</script>
</query>

The result of this is:
"20100615","${etl.date.format(2010-06-15
15:53:59.0","'yyyyMMddHHmmss')}"

This is of course not what I was expecting, I wanted the result of the
query to be formated according to the format specified. It looks like
the CSV driver is just splitting hard on the "," characters,
regardless if those are in an EtlVariable?

Workaround is of course to format the date in the query...

Anybody got any thoughts on this? Bug or User Error?

Cheers,
-daniel

[Martin Alderson]

Hi Daniel,

You can eliminate the CSV driver as the problem by trying with the
text driver instead.

The first problem with your snippet is the $sysdate inside ${}. The $
is not needed again as everything inside the braces is treated as a
JEXL expression.

If removing the $ doesn't fix it, it might be because sysdate is just
a string as far as scriptella is concerned. You can wrap it in a call
to etl.date.parse to fix this, i.e.

<script connection-id="csv">
${etl.date.now('yyyyMMdd')}, $

{etl.date.format(etl.date.parse(sysdate, 'yyyy-MM-dd'),
'yyyyMMddHHmmss')}
</script>

Hope this helps,

Martin

[Daniel G. Kluge]

On 15 Jun., 17:52, Martin Alderson <eek...@gmail.com> wrote:
> Hi Daniel,
>
> You can eliminate the CSV driver as the problem by trying with the
> text driver instead.
>

That would be a solution as well I'd guess, but ugly, since I'd have
to properly do the formatting by hand.

> The first problem with your snippet is the $sysdate inside ${}.  The $
> is not needed again as everything inside the braces is treated as a
> JEXL expression.
>

You're not looking at the output properly, the problem isn't the type
of the $sysdate, it's the fact that the csv driver seems to cut up on
commas regardless of their position.

Run, the following minimum script:
<script connection-id="csv">
${etl.date.now('dd MMM, yyyy')}, ${etl.date.now('dd MMM; yyyy')}
</script>

And the output will be:
"${etl.date.now('dd MMM","yyyy')}","15 Jun; 2010"

You can change the quote character and the separator character to make
it more obvious.

> If removing the $ doesn't fix it, it might be because sysdate is just
> a string as far as scriptella is concerned.  You can wrap it in a call
> to etl.date.parse to fix this, i.e.
>         <script connection-id="csv">
>                 ${etl.date.now('yyyyMMdd')}, $
> {etl.date.format(etl.date.parse(sysdate, 'yyyy-MM-dd'),
> 'yyyyMMddHHmmss')}
>         </script>
>

That will never work, at least not with the CSV driver. and when using
the TEXT driver, the parse is not needed anyway.

Greets,
-daniel

[Fyodor Kupolov]

Hi guys,

I agree this is a bug in
scriptella.driver.csv.opencsv.CSVReader.parseLine. The class is a
customized fork of opencsv library , so it was not designed with
property expressions in mind. However I think this is not a critical
bug since there are reasonable workarounds available like formatting
in a SQL query or wrapping with additional JEXL query where variables
are formatted(which might look ugly but works)... Is it acceptable for
you or maybe you see the alternative solution?

If you look at the problem from a different standpoint, I think it
would be useful to allow customizing/overriding formatting on
connection level, something like this
<connection>
etl.date_format = yyyy-MM-dd
etl.number_format = ####.##
</connection>
Do you see good benefits in adding this?

Regards,
Fyodor

[Fyodor Kupolov]

Hi all,

i've just remembered that you can use quotes to escape commas, because
the script is initially parsed as a set of csv lines and then columns
are substituted individually.
Example:
<script connection-id="csv">
"${etl.date.now('yyyyMMdd')}","${etl.date.format(d,
'yyyyMMddHHmmss')}"
</script>


Hope that helps,
Fyodor