Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

RE: st: Importing data/time variables from Excel


From   Joe Canner <[email protected]>
To   "[email protected]" <[email protected]>
Subject   RE: st: Importing data/time variables from Excel
Date   Thu, 29 Aug 2013 23:16:54 +0000

Phil,

Thanks for the suggestions.  I went ahead and did it manually (select the column and change the format) in each spreadsheet for now, but hopefully I can use one of your solutions before the next wave of data comes in.  The mostly likely candidate is Stata 13, as I hope will be upgrading soon, but I am also intrigued by the ODBC possibility.

Regards,
Joe 
________________________________________
From: [email protected] [[email protected]] on behalf of Phil Schumm [[email protected]]
Sent: Thursday, August 29, 2013 6:27 PM
To: [email protected]
Subject: Re: st: Importing data/time variables from Excel

On Aug 29, 2013, at 12:51 PM, Joe Canner <[email protected]> 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/

*
*   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/


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index