Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: st: odbc load query


From   "Joseph Coveney" <jcoveney@bigplanet.com>
To   "Statalist" <statalist@hsphsun2.harvard.edu>
Subject   Re: st: odbc load query
Date   Tue, 12 Aug 2008 00:31:28 +0900

Murali Kuchibhotla wrote:

     I am having problems trying to connect to my odbc dsn. When I try to
load an excel spreadsheet through the odbc, I obtain the following message:

set debug on

odbc load , table("07-01-08") dsn("dailysnap_2008")
Connection Parameters (IN): DSN=dailysnap_2008;
Connection Parameters (OUT): DSN=dailysnap_2008;

r(682);

I have the driver for dailysnap_2008 correctly identified. Also, r(682)
suggests that I might have insufficient permission to connect to the
datasource, which is not the case here. Any suggestions? Thanks.

--------------------------------------------------------------------------------

Just a couple of suggestions.

1.  Try typing

odbc list

in order to verify that the DSN is recognized by Stata.  If it is set up
correctly, then you should see it listed.

2.  If that's okay, then try typing

odbc query dailysnap_2008

in order to verify that the workbook is correctly recognized and that the
table (worksheet) is "07-01-08" as believed.  If this command works, then
you should be able to click on the hyperlink to -describe- the worksheet,
and then click on the hyperlink after that command to load it.  If this
works, then see what commands Stata generates when you click on the
hyperlinks.  Examining the Stata-generated commands often reveals the names
of worksheets as different from what you think they are.

2.  In the same vein, I take it that the name of the worksheet is 07-01-08,
because when I tried to create an Excel name with that sequence of
characters, Excel (2000) wouldn't let me--it said that it is invalid as a
name.  So, if the worksheet is named 07-01-08, wouldn't it be recognized as
"07-01-08$"?

3.  The -debug- message seems kinda short.  When I create an Excel 2000
workbook named Daily Snap 2008.xls that contains a worksheet named 07-01-08,
and set up a DSN for it (Windows XP Pro, SP 2), although I get the same
error code (return code 682), I get much more to the error message with
Stata 10/SE when I type your line verbatim (see below).

Joseph Coveney

[I get more to the debug error message]

. set debug on

. odbc load , table("07-01-08") dsn("dailysnap_2008")
Connection Parameters (IN): DSN=dailysnap_2008;
Connection Parameters (OUT): DSN=dailysnap_2008;DBQ=F:\Daily Snap
2008.xls;DefaultDir=F:\;
DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not
find the object
 '07-01-08'.  Make sure the object exists and that you spell its name and
the path name
correctly.
r(682);

[Try the following--click on the successive hyperlinks to see whether it
works better]

. odbc list

Data Source Name                   Driver
-------------------------------------------------------------------------------
[redacted]
dailysnap_2008                     Microsoft Excel Driver (*.xls)
[redacted]
-------------------------------------------------------------------------------

. odbc query "dailysnap_2008"
Connection Parameters: DSN=dailysnap_2008;DBQ=F:\Daily Snap
2008.xls;DefaultDir=F:\;Driver
Id=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;
DataSource: dailysnap_2008
Path      : F:\Daily Snap 2008
-------------------------------------------------------------------------------
'07-01-08$'
-------------------------------------------------------------------------------

. odbc desc "'07-01-08$'", dialog(complete)
Connection Parameters: DSN=dailysnap_2008;DBQ=F:\Daily Snap
2008.xls;DefaultDir=F:\;Driver
Id=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;
DataSource: dailysnap_2008 (query)
Table:      '07-01-08$' (load)
-------------------------------------------------------------------------------
Variable Name                               Variable Type
-------------------------------------------------------------------------------
a                                           NUMBER
-------------------------------------------------------------------------------

. odbc load, table("'07-01-08$'") dialog(complete)
Connection Parameters (IN): DSN=dailysnap_2008;
Connection Parameters (OUT): DSN=dailysnap_2008;DBQ=F:\Daily Snap
2008.xls;DefaultDir=F:\;
DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;
. list in 1/2

    +---+
    | a |
    |---|
 1. | 1 |
 2. | 2 |
    +---+

.


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



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