Do I have to create a separate ODBC connection (using admin privileges) every time I want to access a different Excel file with ODBC?

[Michael Anbar]

Stata's ODBC interface is fantastic, but I'm looking to use it to access numerous Excel files in an automated fashion. So far, every time I want to access a different Excel file with ODBC, I need to create a new data connection to it using these instructions (http://www.ats.ucla.edu/stat/stata/faq/odbc.htm), which requires admin privileges (and therefore a call to my IT department).

I realize this is somewhat a Windows question and a Stata question, but if I just have a do file and some Excel files in the same folder, do I really need to create a separate data source connection for *each* of those files, using admin privileges? Or is there a way to simply have Stata load an Excel file using the built-in Excel driver (well, built-in since I have Office installed), without having to create a new data connection to it every time?

[Joseph Coveney]

No, you don't need to set up a separate DSN for each Excel file that you're accessing. If you are running under Windows and have Office installed, then you should have a couple of general DSNs already in place for Excel and Access. Type

odbc list

and you should see something like

DataÿSourceÿNameÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿDriver
-------------------------------------------------------------------------------
.ÿ.ÿ.
ExcelÿFilesÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿMicrosoftÿExcelÿDriverÿ(*.xls,ÿ*.xlsx,ÿ*.xl
MSÿAccessÿDatabaseÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿMicrosoftÿAccessÿDriverÿ(*.mdb,ÿ*.accdb)
.ÿ.ÿ.
-------------------------------------------------------------------------------

You can use the general ODBC DSN for Excel from within Stata using something like the following

local dsn Excel Files;DBQ=<your Excel file name>;DefaultDir=<directory path where Excel file is>;

odbc load, table(<worksheet in the workbook you're loading>) dsn("`dsn'") clear

You could automate the process for multiple Excel files by leaving the DBQ in the local macro to be, itself, completed with another local macro to contain each Excel file name, and then completing the specification at run time.

local dsn Excel Files;DefaultDir=<whatever>;DBQ=

foreach Excel_file in First Second Third {
    odbc load, table(TheSheet) dsn("`dsn'`Excel_file'.xlsx;") clear
    . . . <whatever you're doing with the data>
}

(Note the red semicolon--everything in the DSN specification needs to be delineated with a semicolon, and so in the context of the local macro's usage you'll need to add any outstanding semicolons if the local macro doesn't already contain all of the necessary semicolon delimiters.)

If there are multiple directories, then you can put everything in the DBQ (e.g., DBQ=F:\My Directory\My Subdirectory/`My_Excel_Workbook'.xlsx;) and skip the DefaultDir= ; specification. Note that, in the DSN specification, you do not use double quotation marks to delineate directory paths containing spaces; everything there is delineated by semicolons.

(If you don't see something like these general DSNs after odbc list, then you can call your IT people just once and create the general Excel DSN yourself.)

[Michael Anbar]

Hmmm, I'm using a 64-bit machine with 64-bit Stata and 32-bit Office, and I installed the 64-bit Office ODBC drivers from here (http://www.microsoft.com/downloads/d...displaylang=en), but when I attempt to load the Excel driver, I get this error:

. odbc query "Excel Files", dialog(complete)
The ODBC driver reported the following diagnostics
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the
Driver and Application
SQLSTATE=IM014
r(682);

Do I need to do additional configuration within Stata for this to work? This old Stata list post (http://www.stata.com/statalist/archi.../msg00777.html) suggests installing 64-bit Office, but unfortunately that isn't an option for me; I can only install the 64-bit Office ODBC drivers.

[Bjarte Aagnes]

If 32-bit Office is the only option, you could use 32-bit Stata and the 32-bit ODBC driver.

-import excel- may be an alternative.

--
Bjarte Aagnes