time format

[tincholiver]

Hi everyone, i have some problems with time format:

xls file has next header

Fecha	08:00	08:30	09:00	09:30	10:00	10:30
01/08/2020	80	5	94	20	8	52
02/08/2020	62	12	67	36	63	14
03/08/2020	43	41	19	43	42	60
04/08/2020	29	93	21	42	58	22
05/08/2020	46	39	45	39	79	3

but when i want to load i get next data

LOAD
Fecha,
[0.33333333333333331],
[0.35416666666666669],
[0.375],
[0.39583333333333298],
[0.41666666666666702],
[0.4375],
FROM...

i tried with Time and Time# function but not works.

any idea??

thanks a lot!

[QFabian · Accepted solution]

Hi @tincholiver , here an example that i made with yor data, the words in red are the new ones :

The script transforms the Time columns in rows, using crosstable() function.

Then cratres a valid num time format, replacing a character from '.' to ','.

Aux:
CrossTable(Time, Data)
LOAD Fecha,
[0.33333333333333331],
[0.35416666666666669],
[0.375],
[0.39583333333333331],
[0.41666666666666669],
[0.4375]
FROM
[..\Desktop\Libro1.xlsx]
(ooxml, embedded labels, table is Hoja1);

Data:
Load
Fecha,
replace(Time,'.',',') as Hora,
Data
Resident Aux;
drop table Aux;

[tincholiver]

thanks Fabian!! 

it works!

Regards