Splitting time within a cell into multiple cells- Excel upload

[#1]

Hi,

Can someone please help me? I would like to upload my script to break down below into two or more separate columns Date & Time. Right now, my information is gathered into one column.

Column 1

[13/Mar/2014:20:00:08+0000]

Preferably, I would like the script to be able to break down as per below.

Column 1 - YYYY

Column 2 - MM

Column 3 - DD

Column 4 - HH:MM:SS

I would also take below if above is too difficult

Column 1 - MM/DD/YYY

Column 2 - HH:MM:SS.

[swuehl · Accepted solution]

This should do it:

LOAD *,

  Year([Request Time]) as Year,

  Month([Request Time]) as Month,

  Day([Request Time]) as Day,

  Time([Request Time]) as Time;

LOAD Timestamp(Timestamp#(Mid([Request Time],2,20),'DD/MMM/YYYY:hh:mm:ss')) as [Request Time],

     [HTTP Status],

     [Data Bytes],

     [Time to Serve Request (Microseconds)]

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

[#3]

I loaded per below and am able to execute successfully, however, when I select a Table Box selecting Year, Month, or Day, there are no values.

I would like to be able to use the Year, Month, Day fields rather than the Request Time field.

LOAD

Year([Request Time]) as Year,

Month([Request Time]) as Month,

Day([Request Time]) as Day,

Time([Request Time]) as Time,

Timestamp(Timestamp#(Mid([Request Time],2,20),'DD/MMM/YYYY:hh:mm:ss')) as [Request Time],

[HTTP Status],

[Data Bytes],

[Time to Serve Request (Microseconds)],

FROM

[..\..\20140313_Resource_2.xlsx]

(ooxml, embedded labels, table is resource);

[#4]

I figured it out.