This is a snippet of my data in Excel:
trail_type correct response created_date
T_1_P_2 S S 02-07-2018 17:42
T_1_P_1 L L 02-07-2018 17:42
T_1_P_3 L L 02-07-2018 17:42
T_1_P_4 L S 02-07-2018 17:42
T_1_P_5 S S 02-07-2018 17:42
As you will notice, the date-time is in a 24-hour format.
However, when I import the same data in Stata, I get the following:
trail_type correct response created_date
T_1_P_2 S S 7/2/2018 5:42
T_1_P_1 L L 7/2/2018 5:42
T_1_P_3 L L 7/2/2018 5:42
T_1_P_4 L S 7/2/2018 5:42
This happens even after importing everything as a string.
Below is the code I used to import:
import excel "abc.xls", sheet("xyz") firstrow allstring
Can someone guide me on how to get the proper import?
I need to either preserve the 24-hour format or get an AM/PM indicator.
The weird part is that if I simply copy paste the date-time from Excel onto the data editor in Stata, the 24-hour format is being preserved.
What kind of import command will work?
The problem is that I have hundreds of such files, so I need something in code, rather than me manually changing the column in each Excel file.
You just need to change the format of the imported date-time variable to the one desired.
The following should work:
. import excel "abc.xls", sheet("xyz") firstrow
. format created_date %tcDD-NN-CCYY_HH:MM
. list, noobs abbreviate(20)
+------------------------------------------------------+
| trail_type correct response created_date |
|------------------------------------------------------|
| T_1_P_2 S S 02-07-2018 17:42 |
+------------------------------------------------------+
Note that the variable created_date will be numeric.
From Stata's command prompt, type help format for more details.