Re: st: ODBC misreading or not reading numbers with allstring option

[kcrow@stata.com (Kevin Crow, StataCorp LP)]

Michael Mills <mmills@rti.org> wrote:

>I'm importing data from Excel to Stata with ODBC (to which I'm rather new) and
>several observations have some variables being read in as "E-2" rather than the
>number that exists in the Excel files.

>It appears the numbers in Excel appearing as "E-2" are almost always <0.1 and
>are repeating (e.g. 0.08333333333333 or 0.066666666666667). I often have
>multiple, duplicate, observations and generally this issue will occur for the
>same variable within a set of duplicates. However, there are occasionally
>observations for which the data has been read in (0.83333333333).

>Any thoughts on why this is occurring and what could be done to read in the
>data? Could this be a length of number/string issue? And can I resolve this
>without altering the original Excel files (given the number of files, this
>would be quite time-consuming)?
...

This is a bug with the Excel ODBC driver, not Stata's -odbc- command.  The way
ODBC works is Stata asks Excel for column B, for example, as a string and the
Excel ODBC driver gives us that column as a string.  The -odbc- command simply
copies the string values returned in the column by the driver to the variable
it is creating.

If Michael has Stata 12 I would recommend using -import excel- to import his
Excel data.  -import excel- should be able to handle the numeric to string
conversion of the cells in question. 

Another solution would be to -odbc load- the column(s) in question separately
as numeric variables.  The columns could then be saved and merged back to
the rest of the data.  If the final goal is to have them numeric, this is
all that is needed.  If the final goal is to have them in string form, the
colunms could then be converted to a string using -tostring-.


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