Results 1 to 4 of 4
  1. #1
    okinawan79 is offline Novice
    Windows XP Access 2016
    Join Date
    Aug 2018
    Posts
    5

    Convert text into minutes, second and millisecond

    We are currently running our performance test in Loadrunner and in itself spits out csv export that's formatted like this: 38:06.1 - which equals to minutes, seconds and millisecond.

    I can import into access database with format "Text" but how do I convert this into time (?) or number ... so that I can do some calculation? There is Time in and Time out columns with same format and I need to do some subtractions between the these 2 columns

    When I try to import it using date/time format : mm:ss - only few gets imported correctly.
    Example:

    Thanks for your help.

    Desire Result:


    Time in Time Out Diff
    38:00.0
    38:06.1
    6.1

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by okinawan79 View Post
    When I try to import it using date/time format : mm:ss - only few gets imported correctly.
    The "time" format is wrong for a Time. A time is hh:mm:ss (8 characters). The import chokes on the decimal for the milliseconds.

    The Time format is hh:nn:ss:llll where
    h = Hours
    n = Minutes
    s = seconds
    l = Milliseconds

    Examples:
    12:43:13:0854
    23:02:58:0012




    Quote Originally Posted by okinawan79 View Post
    I can import into access database with format "Text" but how do I convert this into time (?) or number ... so that I can do some calculation?
    You would need to "break apart" the text string and put it back together in the correct format.
    Then you would be able to run a sub to calculate the difference.

    So, for your example, you would have to change "38:006.1" to "00:38:06:0001". (note - the decimal was changed to a colon)


    At https://bytes.com/topic/access/answe...lliseconds-now , there is an attached dB (8th post from the end), you can download a demo dB.
    You will have to modify the code - the demo returns a difference of 6,001.

    Don't know how you are importing the CSV file - importing to a Temp field or reading each line of the CSV file, but once you have each Time In and Time Out, you can fix the fix the format and save to a Text field.
    Then the sub can use the reformatted Time In and Time Out text data to get the difference.


    To save time, I am attaching it here. I really suggest you go to the web site and read the thread.

    Note: I did not create this Demo dB!!
    ADezii (at the referenced site) created the dB......
    Attached Files Attached Files

  4. #4
    okinawan79 is offline Novice
    Windows XP Access 2016
    Join Date
    Aug 2018
    Posts
    5
    thanks for the input... I was afraid I would have to take a part .... I will try out after reviewing the demo DB.

    Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 12-12-2016, 12:00 PM
  2. Replies: 4
    Last Post: 01-28-2016, 11:48 PM
  3. convert hours into minutes
    By princess12 in forum Access
    Replies: 5
    Last Post: 04-13-2015, 01:12 PM
  4. Refreshing Imported Text File Data Every X Minutes
    By acarella610 in forum Import/Export Data
    Replies: 5
    Last Post: 12-17-2013, 10:41 PM
  5. Replies: 4
    Last Post: 06-12-2013, 10:20 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums