Statalist


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

Re: st: odbc load query


From   "Murali Kuchibhotla" <muralik@iastate.edu>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: odbc load query
Date   Mon, 18 Aug 2008 11:42:54 -0500 (CDT)

Hi Joseph,
          Sorry for the late reply. Thank you for your detailed suggestions 
though. I tried implementing your suggestions, but obtained the same result as 
before.When I type odbc list, dailysnap_2008 is recognised as a DSN. However,
when I follow that up with odbc query dailysnap_2008, I get :

 set debug on
 odbc query  dailysnap_2008
Connection Parameters: DSN=dailysnap_2008;
r(682);

However, when I try this procedure with another listed DSN, I am able to load 
the specified file. The only difference between the 2 files is in terms of 
their size; the one I am able to load is 50,000kb while dailysnap_2008 is 
220,000kb. Could this be causing the problem? 

Murali 


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


Murali Kuchibhotla
Department of Economics
Iowa State University
Office:75,Heady
Phone:515-294-5452


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