krutika patel

krutika patel

  • NA
  • 5
  • 37.2k

How to import excel sheet data in to table using sql server 2008

Oct 25 2010 1:43 AM
I want to import excel sheet data in to table using sql server 2008


Made me think this would be quite easy... so

1. I created a Excel Sheet named testfile.xls with the column headings from my temp table
2. Saved and closed this xls
3. Tried to run the following:

USE [MainAdmin];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\RAJ Infotech\testfile.xls',

'SELECT * FROM [Sheet1$]')

SELECT * FROM MainAdmin.dbo.Table_1 

GO

Where E:\Raj Infotech\testfile.xls is where I saved test.xls, Table_1  is the table I have populated in the firstplace and MainAdmin  is the database name. 

When I run this the following error crops up:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Before running either of these 'export' queries I did as instructed in the first link:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Which produced results:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

I assumed from that I had successfully enabled Ad Hoc Distributed Queries - Is that correct?

Both methods seem to throw up a similar error, an error which makes me think its some kind of SQL authentication issue. 

Has anyone successfully exported from SQL to Excel - if so, any tips?!


Answers (1)