How to Replace a Date in String Format with Values ​in a Table

Question

I have a Table generated by reading a set of Excel files using the code:

files = FileNames["*.xls", {"D:\\Dados\\"}]
data = Table[
  Import[files[[j]], {"Data", 1, Range[5, 133], {2, 4}}], {j, 1,
   Length[files]}]

But unfortunately, the program that generates the spreadsheets is writing the date and time in text format, and the result of the reading (data) is:

{{{"2021-08-01 05:47:47", 0.}, {"2021-08-01 05:52:47",
   0.}, {"2021-08-01 05:57:47", 26.3}, {"2021-08-01 06:02:47",
   48.3}, {"2021-08-01 06:07:48", 85.4}, {"2021-08-01 06:12:48",
   178.7}, {"2021-08-01 06:17:48", 260.9}, {"2021-08-01 06:22:49",
   309.1}, {"2021-08-01 06:27:49", 378.5}, {"2021-08-01 06:32:49",
   347.3}, ,...
....
..., {"2021-08-11 16:00:42", 929.7}, {"2021-08-11 16:05:43",
   862.2}, {"2021-08-11 16:10:43", 781.9}, {"2021-08-11 16:15:43",
   716.}, {"2021-08-11 16:20:44", 641.5}, {"2021-08-11 16:25:44",
   575.4}}}

I need to have this "date and time" column splited in two columns. One column containing the value of the day and another column containing the value of decimal hour.

Can someone help me?

Thank you in advance.

Answer 1

data = {{"2021-08-01 05:47:47", 0.}, {"2021-08-01 05:52:47", 0.}, 
   {"2021-08-01 05:57:47", 26.3}, {"2021-08-01 06:02:47", 48.3},
   {"2021-08-01 06:07:48", 85.4}, {"2021-08-01 06:12:48", 178.7},
   {"2021-08-01 06:17:48", 260.9}, {"2021-08-01 06:22:49", 309.1}, 
   {"2021-08-01 06:27:49", 378.5}, {"2021-08-01 06:32:49",  347.3}};

ClearAll[splitdates1]
splitdates1 = Map[Flatten] @* MapAt[StringSplit, {All, 1}];

splitdates1 @ data

 {{"2021-08-01", "05:47:47", 0.}, {"2021-08-01", "05:52:47", 0.},
  {"2021-08-01", "05:57:47", 26.3}, {"2021-08-01", "06:02:47", 48.3}, 
  {"2021-08-01", "06:07:48", 85.4}, {"2021-08-01", "06:12:48", 178.7},
  {"2021-08-01", "06:17:48", 260.9}, {"2021-08-01", "06:22:49", 309.1}, 
  {"2021-08-01", "06:27:49", 378.5}, {"2021-08-01", "06:32:49", 347.3}}

splitdates1 @ data // Grid[#, Dividers -> All] &

Alternatively, create DateObjects and TimeObjects from the first column elements:

ClearAll[splitdates2]
splitdates2 = Map[Flatten] @*
 MapAt[Apply[{DateObject@#, TimeObject@#2} &]@*StringSplit, {All, 1}];


splitdates2 @ data // Grid[#, Dividers -> All] &

We can also use properties of DateObjects to get the desired split:

ClearAll[splitdates3]
splitdates3 = Map[Flatten] @*
 MapAt[{DateObject@DateObject[#][{"Year", "Month", "Day"}], TimeObject @ #} &, {All, 1}];


splitdates3 @ data // Grid[#, Dividers -> All] &

same picture

Answer 2

Edit:

files = FileNames["*.xls", {"D:\\Dados\\"}]
data = First@Table[
  Import[files[[j]], {"Data", 1, Range[5, 133], {2, 4}}], {j, 1,
   Length[files]}];

data[[All, 1]]=StringSplit /@ data[[All, 1]];
data

Original Answer:

You can use StringSplit

data = {{"2021-08-01 05:47:47", 0.}, {"2021-08-01 05:52:47", 
    0.}, {"2021-08-01 05:57:47", 26.3}, {"2021-08-01 06:02:47", 
    48.3}, {"2021-08-01 06:07:48", 85.4}, {"2021-08-01 06:12:48", 
    178.7}, {"2021-08-01 06:17:48", 260.9}, {"2021-08-01 06:22:49", 
    309.1}, {"2021-08-01 06:27:49", 378.5}, {"2021-08-01 06:32:49", 
    347.3}};

StringSplit /@ data[[All, 1]]

{{"2021-08-01", "05:47:47"}, {"2021-08-01", "05:52:47"}, {"2021-08-01", "05:57:47"}, {"2021-08-01", "06:02:47"}, {"2021-08-01", "06:07:48"}, {"2021-08-01", "06:12:48"}, {"2021-08-01", "06:17:48"}, {"2021-08-01", "06:22:49"}, {"2021-08-01", "06:27:49"}, {"2021-08-01", "06:32:49"}}