1

Hi I have a column in my Access database which I import from a log file. My date and time format in the log file is as follows: 2016:6:28:10:15:0:390000000 which is basically: yyyy:mm:dd:hh:mm:ss:ms

When I import the log file in access, it reads this as one column as a text. However, I want this to be in two columns and be read as: column1: yyyy:mm:dd column2: hh:mm:ss

And have its format as Datetime instead of text. I need to do this using Excel VBA. I am unable to find a solution to this. Please help!

Improve this question
2
  • Needs more detail on exactly how you're currently running the import. Commented Aug 10, 2016 at 5:37
  • 1
    What have you tried so far? And you don't want two columns, you should store the data in one column, always. Commented Aug 10, 2016 at 5:48

2 Answers 2

Reset to default
1

That's a bit tricky because of the variable length of the string.

But this will do: Chop the milliseconds, replace the separators for the datepart and for the spacing between the datepart and the timepart. Then convert to date or time:

DateColoumn = DateValue(Replace(Replace(Left([DateTimeText], Len([DateTimeText]) - 10), ":", "-", , 2), ":", " ", , 1))
TimeColoumn = TimeValue(Replace(Replace(Left([DateTimeText], Len([DateTimeText]) - 10), ":", "-", , 2), ":", " ", , 1))

If this is for a database table, Rene's advice is right: Use one field only:

LogTime = CDate(Replace(Replace(Left([DateTimeText], Len([DateTimeText]) - 10), ":", "-", , 2), ":", " ", , 1))

Edit:

The combined value can also by obtained using Split as shown by User, though I would simplify it a bit:

LogTime = ConvertLog([DateTimeText])

using a function like this:

Public Function ConvertLog(ByVal Entry As String) As Date

    Dim Parts   As Variant
    Dim LogTime As Date

    Parts = Split(Entry, ":")

    LogTime = DateSerial(Parts(0), Parts(1), Parts(2)) + TimeSerial(Parts(3), Parts(4), Parts(5))

    ConvertLog = LogTime

End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

This notation is typical for very large logs and parse it by very slow string functions, like Gustav solution, is not good. More over - it doesn't take into account fractional seconds part AT ALL.

Sub sb_SplitDate()
Dim dDate As Date, dDatDat As Date, dDatTim As Date
Dim i&, lArr&(), lLB&, lUB&
Dim sDatInc$, sArr$(), sClm1$, sClm2$

    sDatInc = "1999:12:31:23:59:59:50000000000000000"
    sDatInc = "1999:12:31:23:59:59:49999999999999999"
    sDatInc = "2016:6:28:10:15:0:49999"
    sDatInc = "2016:6:28:10:15:0:50"
    sDatInc = "2016:6:28:10:15:0:5"
    sDatInc = "2016:6:28:10:15:0:499999999999"
    sDatInc = "2016:6:28:10:15:0:999999999999999999999999999"
sDatInc = "2016:6:28:10:15:0:390000000" ' Original

    sArr = Split(sDatInc, ":")

    lLB = LBound(sArr): lUB = UBound(sArr)
    ReDim lArr(lLB To lUB)
    For i = lLB To (lUB - 1)
        lArr(i) = CLng(sArr(i))
    Next
   'lArr(6) = CLng(Left$(sArr(6), 1))
    lArr(6) = CLng(AscW(sArr(6)) - 48) ' significantly faster
              ' or AscB 

    dDatDat = DateSerial(lArr(0), lArr(1), lArr(2))
    dDatTim = TimeSerial(lArr(3), lArr(4), lArr(5))

    dDate = dDatDat + dDatTim - _
            CLng(lArr(6) > 4) / 86400
' standard (school) rounding of the fractional part of seconds
' may gives increasing time, date, month & year ;)

    sClm1 = Format$(dDate, "yyyy:mm:dd")
    sClm2 = Format$(dDate, "hh:mm:ss")

    Debug.Print sDatInc & vbLf & _
                sClm1 & " " & sClm2 & _
                IIf(lArr(6) > 4, " ' *** Rounded Up!", "") & _
                vbLf
    Stop
End Sub

.

Re: Gustav

> Normally you round time parts down
Normally I round as task is standing. If it's a time stamp - Down. If it's a time diapason (how much was ticked?) - Up. Moreover, even in 1st case it may be rounded Up for special purposes like avoiding near clock zeroes events raising. As task is standing...

> 4/5 rounding will only shift ...
What means "only"? Do you wait any more from rounding above simple shifting of given data to one of boundaries? Any rounding will shift for some value as task was standed by OP: "hh:mm:ss:ms" --> "hh:mm:ss"

> ... shift all the log recordings by 0.5 second
Are you sure? What about given original "2016:6:28:10:15:0:390000000"?
It will be shifted for 0.39-Down or for 0.61-Up...

OP didn't explain which rounding must be applying. But it's not a reason to forget about rounding at all in favor of simple cutting.

I proposed little sophisticated but common solution that easy transformed from one rounding to another. It only needs to add the only '=Chr(39) symbol :) 

dDate = dDatDat + dDatTim ' - CLng(lArr(6) > 4) / 86400

It's not difficult, yeah? But it's a visual representation of one of the this task pitfalls and its solution.

> I believe only one array is needed
- Yeah... I understand. And I'm not surprised, since you believe that string operations are acceptable in such tasks as numbers parsing. The second array is intended exactly to avoid superslow string operations. For myself I would not use Split() at all, but only byte arrays parsing...

And, btw, - does the task was to minimize the quantity of used arrays or to try to cram solution in one-two strings of code?..

Sorry, but the stars are not in favor of strings now :) .

Improve this answer

8 Comments

Normally you round time parts down - the hour is not 8 for a time of 07:59:59, neither 07:59:59.999 (except when displayed by default in Access where milliseconds will be rounded to the nearest second). So you can skip the rounding of nano/milliseconds - after all, 4/5 rounding will only shift all the log recordings by 0.5 second.
Re: Gustav I'm a newbee and I'm very interesting - is it normal on this community to to take someone else's code, and paste it on already positive voted solution, thereby crossed out it? I'm in a fog...
Sorry, I considering adding it to your answer, but some find that offending, so I made a reference instead, as there is nothing special in using Split for tasks like this.
You stole it out and then been sorried :) Please, read "12 chairs" by Ilf & Petrov, 1927. "The Assistant Warden of the Second Home of Stargorod Social Security Administration was a shy little thief. His whole being protested against stealing, yet it was impossible for him not to steal. He stole and was ashamed of himself. He stole constantly and was constantly ashamed of himself, which was why his smoothly shaven cheeks always burned with a blush of confusion, shame, bashfulness and embarrassment. [...] The world has never seen such a bashful chiseller as Alexander Yakovlevich."
Well, the source is a string, so you cannot avoid string handling. I ran a test on your code, and it runs in 4.4µs. My one-liner runs in 5.7µs, while my ConvertLog runs in 3.9µs, so not much of a difference.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.