Autogenerate timestamps, based on input from Excel

[clausbarlose]

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

[Gysbert_Wassenaar · Accepted solution]

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

[clausbarlose]

Hi Gysbert

Thank you very much - works exactly as it I wanted!

Best regards

Claus