Joseph Coveney <[email protected]> writes:
>Does anyone know whether Stata's -odbc- commands can be used with *file*
>DSNs? I cannot seem to get -odbc list- to see a file DSN even though it's
>listed in the File DSN tab in the Windows OS's Control Panel's ODBC Data
>Sources. Does Stata only see DSNs that are stored in the registry?
>
>I've checked the Stata reference manual, searched the Statalist archives,
>and googled for Stata file DSN ODBC.
>
>Joseph Coveney
Answering Joseph's question, you can connect via a FileDSN with Stata on
Windows. However, it is not as convenient as it could be and that is due to
the fact that FileDSNs are not regular DSNs.
Just for clarification to anyone who doesn't know what a FileDSN is -- it
is a connection method that stores all connection parameters in a file. It has
nothing to do with connecting to a file as a data source, but rather using a
file for obtaining the connection parameters. Because of the subtle difference
in the mode of connection, the ODBC specification separates DSNs from FileDSNs.
This is why a poll of DSNs with -odbc list- does not show any FileDSNs.
It is also worth noting that using a FileDSN should not be required to connect
to a particular data source. A similar entry as a regular DSN can be configured
to connect to the same data source, but with a FileDSN the task of configuring
a new connection is greatly reduced since the information is stored within the
file.
The general concept for connection via a FileDSN with Stata involves using a
regular DSN to initiate the connection, prompting for a connection dialog, and
from the connection dialog, crossing over to a FileDSN.
I will use an example for an Excel data source:
1. Verify that there is a User DSN defined with the appropriate driver
for your data. Most Windows computers come with a default 'Excel
Files' DSN entry, and this is the DSN entry I will use for this
particular example. If you are connecting to a specific database,
such as Postgres or MySQL, you will have to create a basic DSN entry
that uses the appropriate driver. Don't worry about filling in any
specific information for the DSN; just make sure it uses the
appropriate driver.
2. Add your FileDSN entry to the File DSN tab by selecting your *.dsn
connection file.
3. From Stata, enter an -odbc load- command that uses the dialog prompt
option. You will need to know the table name at this point -- which
is something that you will have to know from an external (from Stata)
source. The DSN to use is the regular DSN from step 1.
. odbc load, dsn("Excel Files") table("table1$") dialog(prompt)
4. From the dialog that appears, select the "File Data Source" tab,
select the FileDSN that you defined, and click the following "OK"
buttons.
Assuming everything was defined appropriately, the data should now be loaded.
Other odbc commands that accept the dialog(prompt) option can be used in
similar fashion.
--Kevin
[email protected]
*
* 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/
