Import data using mlcp, a csv file and transform parse date

3
votes

I have a csv file. I've managed import these data into MarkLogic using mlcp which then created a xml file in MarkLogic.

Now in csv I have this format "6/29/2013 5:00:00 PM" random in one of the column. How do I use xquery and probably node-replace as a transform function to convert this date into a different format such as "2013-06-29" as MarkLogic default date format?

Any help is appreciated...

I have created transform.xqy and install it on Modules in MLogic. I'm thinking about using "xdmp:node-replace" to replace the date with expected format. Or should I go thorugh the csv column by column (How to do?) and use "castable as xs:dateTime" to determine date value or not. Yet, even just printing out the content value/uri, always giving me error.

xquery version "1.0-ml";
module namespace example = "http://test.com/example";

(: If the input document is XML, insert @NEWATTR, with the value
 : specified in the input parameter. If the input document is not
 : XML, leave it as-is.
 :)
declare function example:transform(
  $content as map:map,
  $context as map:map
) as map:map*
{
  let $the-doc-uri := map:get($content, "uri")
  let $the-doc := map:get($content, "value")
  return
    trace($the-doc, 'The value of doc is: ')
};
2
xquerymarklogicmlcp
What have you done so far? You can read the datetime with DateTime.ParseExact("dd/MM/yyyy hh:mm:ss") and later convert it to string with your preferred format... – Sebastian Siemens
Actually, it's not about syntax of date parsing. But how do we use transform function on mlcp to convert this date format on the fly to get expected result. – stuckedoverflow

2 Answers

2
votes

The MarkLogic documentation contains a full example of an MLCP transform:

https://docs.marklogic.com/guide/mlcp/import#id_65640

It shows this example, which adds an attribute to the XML content:

declare function example:transform(
  $content as map:map,
  $context as map:map
) as map:map*
{
  let $attr-value := 
   (map:get($context, "transform_param"), "UNDEFINED")[1]
  let $the-doc := map:get($content, "value")
  return
    if (fn:empty($the-doc/element()))
    then $content
    else
      let $root := $the-doc/*
      return (
        map:put($content, "value",
          document {
            $root/preceding-sibling::node(),
            element {fn:name($root)} {
              attribute { fn:QName("", "NEWATTR") } {$attr-value},
              $root/@*,
              $root/node()
            },
            $root/following-sibling::node()
          }
        ), $content
      )
};

Keep in mind you are supposed to update the "value" property of the $content map:map, and return $content to get your transformation result added to the database. I suggest using a (potentially recursive) typeswitch to identify element nodes, and then adjusting their value accordingly..

HTH!

0
votes

finally did it.

The thing is I must use mem:node-replace because it is on the fly, on memory. While xdmp:node-replace is when the data is already on MarkLogic.

The rest is as expected I must use format-date and xdmp:parse-dateTime to get date format as required.

Here is some snippets

xquery version "1.0-ml";
module namespace ns_transform = "this_is_my_namespace";
import module namespace mem = "http://xqdev.com/in-mem-update" at "/MarkLogic/appservices/utils/in-mem-update.xqy";

declare variable $ns := "this_is_my_namespace";

declare function ns_transform:transform(
  $content as map:map,
  $context as map:map
) as map:map*
{  

    let $doc := map:get($content, "value")

    let $format_in := "[M]/[D]/[Y0001] [h01]:[m01]:[s01] [P]"
    let $format_out := "[Y0001]-[M01]-[D01]"

    let $old_date := $doc/*:root_doc/*:date/text()
    let $new_date :=  format-date(xs:date(xdmp:parse-dateTime($format_in, $old_date)), $format_out)

    let $new_doc := mem:node-replace($doc/*:root_doc/*:date,element {fn:QName($ns, "date")}{$new_date})
    let $_ := map:put($content, "value", $new_doc)

    return $content  
};