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

Question

0.00/5 (No votes)

See more:

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 Table_1(Id,FirstName,LastName) SELECT A.[Id], A.[FirstName], A.[LastName] FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\RAJ Infotech\testfile.xls;HDR=YES', 'select * from [Sheet1$]') AS A ; 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 run following :
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?

Posted 24-Oct-10 20:02pm

krutikapatel

Updated 24-Oct-10 20:04pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ahiri · Answer 1 · 2010-10-24T20:30:00

XML

Dim cn As ADODB.Connection
 Dim strSQL As String
 Dim lngRecsAff As Long
 Set cn = New ADODB.Connection
 cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=C:\test\xltestt.xls;" & _
     "Extended Properties=Excel 8.0"
 'Import by using Jet Provider.
 strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
     "Server=<server>;Database=<database>;" & _
     "UID=<user>;PWD=<password>].XLImport9 " & _
     "FROM [Customers$]"
 Debug.Print strSQL
 cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
 Debug.Print "Records affected: " & lngRecsAff
 cn.Close
 Set cn = Nothing

for more info check this link

http://support.microsoft.com/kb/321686

i hope this will help you

all the best
:)