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:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
