Extract field values & DateTime

[prees959]

HI,  Im reading some log files where each row starts as a datetime and an IP address:

Can someone advise please on how to split the field into the following data items :

Many thanks!

Phil

[Frank_Hartmann · Accepted solution]

see attached

hope this helps

[Taoufiq_Zarra]

One solution :

Data:

load Date(TimeStamp#(replace(subfield(A,']',1),'[',''),'YYYYMMDD hh:mm:ss')) as Date,
Time(TimeStamp#(replace(subfield(A,']',1),'[',''),'YYYYMMDD hh:mm:ss')) as Time,
replace(subfield(A,']',2),'[','') as IP,
subfield(A,']',3) as DESC

FROM
.\Downloads\dsds.xlsx
(ooxml, explicit labels, table is Feuil1);

Input:

output :

[Kushal_Chawda]

Assuming that you have same format of the other values. You can do below

If format of the time in string is different then you need to provide same format in timestamp# function.

Data:
Load *,
     date(floor(timestamp#(TextBetween(Field,'[',']'),'YYYYMMDD hh:mm:ss')),'DD/MM/YYYY') as Date,
     time(timestamp#(TextBetween(Field,'[',']'),'YYYYMMDD hh:mm:ss')) as Time,
     TextBetween(Field,'[',']',2) as IP,
     SubField(Field,']',-1) as DESC
Inline "
Field
[20200303 14:00:00] [86.245.198.170] southern stores 588872 "