Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Happy New Year to all!
I want to autogenerate timestamps based on input from an Excel sheet.
In short, purpose is to decide whether timestamps from another table, collides in time with periods in the Excel sheet.
Autogenerated timestamps must be in five minutes intervals.
Excel input file layout:
StartTimestamp, EndTimestamp, Comment
e.g.
2015-01-12 14:00:00, 2015-01-14 17:00:00, Relase #101515
2015-02-25 23:00:00, 2015-02-26 03:00:00, Release #224355
...
Hopefully someone has tried this before?
Best regards,
Claus
Something like this:
LOAD
StartTimestamp,
TimeStamp(StartTimestamp+(IterNo()-1)/24/12) as TimeStamp
EndTimestamp,
Comment
FROM
MyExcelFile.xlsx (ooxlm, ...etc)
WHILE
StartTimestamp+(IterNo()-1)/24/12 <= EndTimestamp
;
If your excel timestamps aren't timestamps but text strings then you first need to use the timestamp# function to turn the strings into numeric timestamp values: timestamp#(StartTimestamp,'YYYY-MM-DD hh:mm:ss') as StartTimestamp
Something like this:
LOAD
StartTimestamp,
TimeStamp(StartTimestamp+(IterNo()-1)/24/12) as TimeStamp
EndTimestamp,
Comment
FROM
MyExcelFile.xlsx (ooxlm, ...etc)
WHILE
StartTimestamp+(IterNo()-1)/24/12 <= EndTimestamp
;
If your excel timestamps aren't timestamps but text strings then you first need to use the timestamp# function to turn the strings into numeric timestamp values: timestamp#(StartTimestamp,'YYYY-MM-DD hh:mm:ss') as StartTimestamp
Hi Gysbert
Thank you very much - works exactly as it I wanted!
Best regards
Claus