Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

RE: st: Re: ODBC plus Excel column labels with a # sign

From   "Steichen, Thomas J." <>
To   <>
Subject   RE: st: Re: ODBC plus Excel column labels with a # sign
Date   Wed, 26 Oct 2005 14:39:40 -0400

My thanks to Kevin Turner and Joseph Coveney...

> Kevin Turner wrote:
> You might be able to import them via a SQL convert/rename, 
> but I haven't had any luck with the Excel ODBC driver 
> supporting such syntax. Your best option may be to use 
> -rename- until the next update.
> and Joseph Coveney wrote:
> You can enclose in square brackets column names and table 
> names containing nonstandard characters.  For example, the 
> following works with an Excel spreadsheet containing the data 
> below, which mimic what Tom describes.
> odbc load , ///
>   exec("SELECT [subj#] AS subj, string_data FROM [Sheet1$]") ///
>   dsn(Book1)
> (By the way, I cannot get -allstring- to fix the problem Tom 
> describes with the second column.  It still loads the second 
> column as double-precision numeric.  Increasing the 
> rows-to-scan setting in the ODBC configuration to its maximum 
> of 16 doesn't work, either. 

I tried Joseph's approach for nonstandard column names and could not
get it to work, but the error message suggests a dsn() error rather than
an exec() error. Strangely, if I do not use the exec() option the dsn is OK.
I'll stick with the ODBC load then rename with Stata for now.  

Like Joseph, I also found that -allstring- does not resolve the second problem.

I then tried using the ' (apostrophe) approach in Excel to indicate that 
apparent numbers were actually text.  I applied this to the first few rows
to see if ODBC would detect the right data type... Instead, it ignored the 
apostrophe and read the column as numeric.  

Then, to make matters worse, when I tried -allstring-, only those cells with 
the apostrophe or a non-numeric character were read as string text and the 
remaining cells were transferred as blanks!
My next step was to resort back to my original Excel info by removing all the 
apostrophes I had entered and resaving.  Now, whether I use -allstring- or not, 
The column is read exactly as in the previous paragraph. And, yes, I am quite
frustrated by this Excel / ODBC situation!

Tom Steichen

CONFIDENTIALITY NOTE: This e-mail message, including any attachment(s),
contains information that may be confidential, protected by the
attorney-client or other legal privileges, and/or proprietary non-
public information. If you are not an intended recipient of this
message or an authorized assistant to an intended recipient, please
notify the sender by replying to this message and then delete it from
your system. Use, dissemination, distribution, or reproduction of this
message and/or any of its attachments (if any) by unintended recipients
is not authorized and may be unlawful.

*   For searches and help try:

© Copyright 1996–2017 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index