Reading and Excel file | SQL Server Performance Forums

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.<br /><br />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.<br /><br />You could still use OPENDATASOURCE like this<br /><hr noshade size="1"><br />SELECT * <br />FROM OpenDataSource(‘Microsoft.Jet.OLEDB.4.0’,<br /> ‘Data Source="PathToYourFile";User ID=Admin<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=;Extended properties=Excel 8.0;’)…sheet1$<br /><hr noshade size="1"><br /><br /><br />(1). Assuming you are running Excel 97 and above (use ‘Extended Properties=Excel 8.0’). Jet 4.0 installs with that Excel engine<br /><br />(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.<br /><br />
And read about the dangers of using OPENDATASOURCE here]