Re: st: Importing data/time variables from Excel

[Phil Schumm <pschumm@uchicago.edu>]

On Aug 29, 2013, at 12:51 PM, Joe Canner <jcanner1@jhmi.edu> wrote:
> I have a spreadsheet which has a column of date/time values.  However, because it is formatted to display just the date, when I import it into Stata (12.1) sometimes it re-formats it as a date and throws away the time data.  This doesn't happen consistently for some reason.
> 
> Is there any way to force Stata to recognize this column as a date/time and override its natural whims?  I have a number of spreadsheets like this and will probably have to re-import new versions from time to time, so I would rather not go into each spreadsheet before every import and re-format the column.


I'm not 100% certain, but I think you're out of luck with -import excel-.  This will pay attention to the way in which the cells are formatted (e.g., as either date only or having both a date and time component), and I don't believe there's any way to override this (at least not that is documented).

Given this, you have (at least) three potential alternatives:

1) Use a Visual Basic macro/script to reformat the date columns in the Excel files as datetime before importing them into Stata.

2) Use ODBC to read the data from Excel into Stata (I believe using ODBC will allow you to circumvent this issue, but don't know for sure)

3) Upgrade to Stata 13, and use the -xl()- class to read the affected column(s) into Stata as numbers, and then reconstruct the datetime representation yourself (i.e., combine the integer and fractional parts to create Stata datetime values (milliseconds since 01jan1960)).

Unfortunately, (1) requires knowing a little bit of VB (though you could easily cobble together the script you need from stuff on the internet), (2) requires knowing how to set up ODBC (see previous postings to Statalist by our resident expert on this, Joseph Coveney), and (3) requires knowing a bit of Mata.  If you don't already satisfy one of these requirements, you'll have to decide whether any of them is worth investing in for your particular problem.


-- Phil

P.S. I suppose you could also try Stat/Transfer.  I have no idea what it will do with this, but you could email them and ask.


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/