Moving Data From Excel to Temp Table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Moving Data From Excel to Temp Table

How can i move data from a Excel file to a Temp Table. I tried using DTS but no luck. Thanks

Never use DTS, but anyway … Easiest solution is probably to let DTS create a permanent table, then process the data, and finally drop the permanent table.
Adriaan Thanks for the reply. But the problem is that I have Read Only access on that server. So creating the permanent table is not possible. George

You have read-only access to the table, but it seems like you are trying to add data, so that doesn’t quite make sense.<br /><br />Try OPENDATASOURCE – here’s the example from Books Online:<br /><br />SELECT * <br />FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,<br /> ‘Data Source="c:Financeaccount.xls";User ID=Admin<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=;Extended properties=Excel 5.0′)…xactions<br /><br />… but I couldn’t tell you what object is referred to as "xactions" here – must be defined within the XLS file.
Also try Madhivanan Failing to plan is Planning to fail