I have a .xlsx file (Microsoft Excel file) called myWorkbook.xlsx. This file has two sheets, named myFirstSheet and mySecondSheet. Each sheet contains two columns and two rows. The first column contains day/date data, and the second column contains time data. This is shown in the following schematic:
I can use the "Sheets" Import element to list the sheet names:
Import["myWorkbook.xlsx", "Sheets"]
(* {"myFirstSheet", "mySecondSheet"} *)
I can use the "Data" Import element to extract the sheets' data:
{sheet1, sheet2} = Import["myWorkbook.xlsx", "Data"];
Unfortunately, the time data doesn't appear to be imported as expected:
{sheet1, sheet2} = Import["myWorkbook.xlsx", "Data"];
Grid[{{
Labeled[Grid[sheet1], Style["sheet1", Gray], Bottom],
Labeled[Grid[sheet2], Style["sheet2", Gray], Bottom]
}}, Spacings -> {3, Automatic}, Alignment -> {Left, Top}]
So, my question is, how can I import the times as simple strings, rather than the obfuscated DateObject elements that are obtained above?
What do the DateObject elements represent?
Inspecting the FullForm of the sheets may be helpful:
FullForm[sheet1]
FullForm[sheet2]
(* List[
List["M 9/8", DateObject[List[-2, 11, 30, 13, 25, 0.`],
"Instant", "Gregorian", -4.`]],
List["Tu 9/9", DateObject[List[-2, 11, 30, 15, 30, 0.`],
"Instant", "Gregorian", -4.`]]
] *)
(* List[
List["W 9/10", DateObject[List[-2, 11, 30, 5, 30, 0.`],
"Instant", "Gregorian", -4.`]],
List["F 9/12", DateObject[List[-2, 11, 30, 7, 30, 0.`],
"Instant", "Gregorian", -4.`]]
] *)
From the FullForm output, it appears that Wolfram language is interpreting the first time data in sheet 1 as a DateObject representing November 30 at 13:25 (i.e., 1:25 PM) in the year -2:
exampleDateObject1 = DateObject[{-2, 11, 30, 13, 25, 0.`},
"Instant", "Gregorian", -4.`]
I think that November 30 is equivalent to "month 0, day 0" -- in other words, one month and one day prior to January 1. Also, it seems that Wolfram language interprets a "0" in the year slot as year -2, as shown in this example:
exampleDateObject2 = DateObject[{0, 0, 0, 13, 25, 0.`},
"Instant", "Gregorian", -4.`]
DateString[exampleDateObject2, "Year"] // FullForm
(* "-2" *)
DateString[exampleDateObject2, {
"DayNameShort", " ",
"DayShort", " ",
"MonthNameShort", " ",
"Year", " ",
"Hour12Short", ":", "Minute", ":",
"Second", " ",
"TimeZoneGMTRelative"}] // FullForm
(* "Tue 30 Nov -2 1:25:00 GMT-04:00" *)
It's unclear to me why Wolfram language interprets "year 0" as year -2.
Reformatting the DateObject elements as simple time strings
Anyway, now that I somewhat understand why the .xlsx is imported and displayed how it is, I can reformat the data stored in sheet1 and sheet2 by mapping DateString onto the DateObject elements:
{sheet1Formatted, sheet2Formatted} =
Map[MapAt[DateString[#, {"Hour12Short", ":", "Minute", " ", "AMPM"}]
&, #, {{1, 2}, {2, 2}}] &,
{sheet1, sheet2}];
Grid[{{
Labeled[
Grid[sheet1Formatted, Frame -> All, FrameStyle -> Gray],
Style["sheet1Formatted", Gray], Bottom],
Labeled[
Grid[sheet2Formatted, Frame -> All, FrameStyle -> Gray],
Style["sheet2Formatted", Gray], Bottom]
}}, Spacings -> {3, Automatic}, Alignment -> {Left, Top}]
... which recovers the formatting of the original Microsoft Excel file, myWorkbook.xlsx.
My question is, is there an easier way to accomplish this?
I could export each sheet, myFirstSheet and mySecondSheet, into separate .csv files. When those .csv files are imported using Import, the formatting of the times is preserved. But this method is inconvenient because .csv doesn't support multiple sheets.
