SQL Server Performance Forum – Threads Archive

Reading and Excel file

Hey all, I am trying to use the OPENDATASOURCE syntax to read an excel spreadsheet. I have having a terrible time trying to get this to work. Has anyone attempted this and if so how did you get it to work? Thanks in advance! -chili

The SQL Server BOL does not document how to use Excel datasources with OPENDATASOURCE correctly. There is 1 issue it does not indicate – The ‘…xactions’ part in the documentation should read ‘…xactions$’ (with a dollar sign at the end). Without this the dollar sign the ‘OLEDB Provider’ will return an error. You could still use OPENDATASOURCE like this <hr noshade size="1"> SELECT * FROM OpenDataSource(‘Microsoft.Jet.OLEDB.4.0’, ‘Data Source="PathToYourFile";User ID=Admin<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=;Extended properties=Excel 8.0;’)…sheet1$ <hr noshade size="1"> (1). Assuming you are running Excel 97 and above (use ‘Extended Properties=Excel 8.0’). Jet 4.0 installs with that Excel engine (2). Add ‘HDR=YES;’ at the end of your ‘init string’ if your excel sheet contains Headers for the columns in the First row; SQL server will otherwise assume that the first row contains the column names.

And read about the dangers of using OPENDATASOURCE here http://www.ciac.org/ciac/bulletins/m-094.shtml]

]]>