locked
Load CSV with partially missing Milliseconds in timestamp RRS feed

  • Question

  • I recently had an issue where I was parsing a CSV into predefined Structure (using "Structure:" [] ) as input for a copy activity
    The CSV contained a time-column in format 14:9:52:873 which corresponds to "H:m:s:fff" - so i used { ....., "type": "DateTime", "format": "H:m:s:fff"}
    however, some of the entries only contained one digit for the millisecond - e.g. 15:8:13:5 
    this caused the Copy-Activity to fail 
    I also tried "f", "fff", "F", "FFF" - but all failed with parsing issues

    is there anything I missed or is this just not supported (yet?)?

    thanks,
    -gerhard

    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Thursday, October 27, 2016 3:52 PM

All replies

  • Hi Gerhard,

    Sorry for the late response.

    For your met issue, currently it is not a supported scenario, because data "14:9:52:873" has format "H:m:s:fff" and data "15:8:13:5" has format "H:m:s:f". There is no way to parse such data with a single format.

    For now, you may leverage a custom activity to clean such csv file first.

    BTW, I'm curious that where does such data come from? It would help us to better prioritize the effort to support such use case.

    Thanks,

    Yingqin

    Thursday, November 17, 2016 1:33 PM
  • Hi Yingqin,

    thanks for your reply!

    the data was a dump from a customers IoT sensors but I also assume that this is a very common issue as zeros tend to be truncated if they appear on the very right side of a decimal number.
    the statement that "its two different formats" is somehow controversy - you have the same issue also for Mintues where "m" can be used to parse 14:3:59 (single-digit minutes) and also 14:22:59 (two-digit minutes). Also, decimal numbers can be read regardless of the number of digits after the decimal-seperator

    any thoughts on this?

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Thursday, November 17, 2016 1:57 PM
  • Thanks for the comment. It is truly arguable that why the 2 kinds of data are treated as 2 formats. The format is defined by .Net, and what we do is to call the .Net API DateTime.ParseExact to parse string data into dateTime type.

    Currently we don't have an easy way to address such limitation. Sorry for that.

    Sunday, November 20, 2016 1:55 PM
  • regarding .Net and DateTime.ParseExact - using "FFF" in my case works in a regular .Net program:

    string dt1 = "14:9:52:873";
    string dt2 = "14:9:52:8";
    
    DateTime conv1 = DateTime.ParseExact(dt1, "H:m:s:FFF", null);
    DateTime conv2 = DateTime.ParseExact(dt2, "H:m:s:FFF", null);


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, November 21, 2016 8:02 AM
  • Good. I only tried the format "H:m:s:fff", and missed the format "H:m:s:FFF". Then you should be able to parse your data with format "H:m:s:FFF". Please further provide the runId if it doesn't work for you.

    BTW, you may also consider setting a format like "H:m:s:FFFFFFF" in case there is any string like "14:9:52:87312".

    Tuesday, November 22, 2016 8:41 AM
  • I think I tried "FFF" but it also did not work 

    I will test this again if I find some time and keep you updated!

    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, November 22, 2016 9:08 AM