I'm new to Clover (using v2.9.4).
I have a UniversalDataReader taking in a CSV using | delimiters in UTF-16 format
The out port metadata was built off of a table from MySQL and has string, long, integer, boolean, and date fields within it. I've had trouble getting the file to parse, so rolled the metadata back to all string and started rolling each type back in with trash files off of both ports so I can see the detail messages.
Two types are giving me some problems.
the file has boolean data as "False" or "True" and date data down to the millisecond.
These are the two errors I'm receiving:
...(date) cannot be set to "2009-06-18 10:35:14.173000000" - doesn't match defined format "yyyy-MM-dd HH:mm:ss.S" in field # 10 of record # 5, value: '2009-06-18 10:35:14.173000000'
...(boolean) cannot be set to "False" - - doesn't match defined True/False format "T|TRUE|YES|Y|t|true|1|yes|y" / "F|FALSE|NO|N|f|false|0|no|n" in field # 5 of record # 1, value: 'False'
What am I doing wrong? tia,
crrb
I have a UniversalDataReader taking in a CSV using | delimiters in UTF-16 format
The out port metadata was built off of a table from MySQL and has string, long, integer, boolean, and date fields within it. I've had trouble getting the file to parse, so rolled the metadata back to all string and started rolling each type back in with trash files off of both ports so I can see the detail messages.
Two types are giving me some problems.
the file has boolean data as "False" or "True" and date data down to the millisecond.
These are the two errors I'm receiving:
...(date) cannot be set to "2009-06-18 10:35:14.173000000" - doesn't match defined format "yyyy-MM-dd HH:mm:ss.S" in field # 10 of record # 5, value: '2009-06-18 10:35:14.173000000'
...(boolean) cannot be set to "False" - - doesn't match defined True/False format "T|TRUE|YES|Y|t|true|1|yes|y" / "F|FALSE|NO|N|f|false|0|no|n" in field # 5 of record # 1, value: 'False'
What am I doing wrong? tia,
crrb
-
Hello,
Universal Data Reader can't handle such data. You need to read them as strings and convert to proper type in Reformat node:<?xml version="1.0" encoding="UTF-8"?>
<Graph id="1281700646917" >
<Global>
<Metadata id="Metadata1" >
<Record fieldDelimiter="|" name="data_types" recordDelimiter="\n" type="delimited">
<Field format="yyyy-MM-dd HH:mm:ss.S" name="date_data" type="date"/>
<Field name="boolean_data" type="boolean"/>
</Record>
</Metadata>
<Metadata id="Metadata0">
<Record fieldDelimiter="|" name="data_types_as_string" recordDelimiter="\n" type="delimited">
<Field name="date_data" type="string"/>
<Field name="boolean_data" type="string"/>
</Record>
</Metadata>
<Property fileURL="workspace.prm" id="GraphParameter0"/>
</Global>
<Phase number="0">
<Node fileURL="${DATAIN_DIR}/data.txt" id="DATA_READER0" type="DATA_READER"/>
<Node id="REFORMAT0" type="REFORMAT">
<attr name="transform"><![CDATA[//#TL
// Transforms input record into output record.
function transform() {
$0.boolean_data := iif($boolean_data == "True", true, false);
$0.date_data := str2date(substring($date_data,0,22),"yyyy-MM-dd HH:mm:ss.S");
}
// Called to return a user-defined error message when an error occurs.
// function getMessage() {}
// Called during component initialization.
// function init() {}
// Called after the component finishes.
// function finished() {}
]]></attr>
</Node>
<Node debugPrint="true" id="TRASH0" type="TRASH"/>
<Edge fromNode="DATA_READER0:0" guiBendpoints="" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (output)" toNode="REFORMAT0:0"/>
<Edge fromNode="REFORMAT0:0" guiBendpoints="" id="Edge1" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="TRASH0:0"/>
</Phase>
</Graph> -
That was the approach I had settled on for the booleans. The source supplied milliseconds, but we really aren't interested in that value and as a result I discovered that a format of 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd' can still read those fields in as date, it just truncates off the extraneous information.
Thank you for getting a complete solution back to me. My apologies for the dup on the boolean question - I didn't realize that the first post actually went through.
Thank you Agata,
crrb -
Hi, Zram_K,
You can read such data either as date directly (Set metadata field to date and fill MM/dd/yyyy into field format property in this case.) or as string which can be retyped with str2date($in.0.input_date,"MM/dd/yyyy") function in CTL (Here will be two kinds of metadata - the first one with string type field and the second one with date type field again with format set to MM/dd/yyyy).
Best regards,
Please sign in to leave a comment.
Comments 4