Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
meef
Posting Yak Master
113 Posts |
 Posted - 2012-09-19 : 14:45:26
|
| What's the exact string I need to do this? I tried a few variations of INSERT INTO [tablename] SELECT * from [excel file] but something isn't right. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-19 : 14:47:12
|
| You will need to use OPENROWSET or one of the other methods described here: http://support.microsoft.com/kb/321686 |
 |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2012-09-19 : 14:55:32
|
| That's one of the ones I tried, but I get this error:"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode."Didn't get a chance to google it yet to see what it means. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-19 : 15:00:26
|
| See if this fixes it:http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/ |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2012-09-19 : 15:10:51
|
| I ran those statements, it said everything was changed after I ran it, I did one more RECONFIGURE, then ran this query and got the same error as above:INSERT INTO dbo.tablename select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\spreadsheet.xls', [SEPT 1 - SEP 30$]) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-19 : 15:20:14
|
| I would recommend using SSIS (Import/Export wizard) to run this then. It could be a 64-bit driver issue or another obscure issue. |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2012-09-20 : 09:18:39
|
| I'm guessing there just isn't a decent way to do what I want. This spreadsheet has 7 different sheets inside for different months of data, I was hoping I could add them all at once but it won't let me use the same destination table. I could combine all the sheets into one, but for what I'm trying to do that just won't be worth the time. Not to mention it failed on the data types when I tried to append everything.Thanks for the help though. |
|
|
|
|
|
|
Appending data from Excel to a table