How to transfer the data from Excel to SQL2005

Question

Post reply

How to transfer the data from Excel to SQL2005

Viewing 15 posts - 16 through 30 (of 49 total)

You must be logged in to reply to this topic. Login to reply

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 1

Hi Ramesh,

I tried your codes but it's still not working... 🙁

Here what I did before your comments

I tried to transfer the data from Excel spreadsheet to database in SQL 2005.

This is what I did, but no success

Insert Into ROCAPData --ROCAPData is my database name in SQL

Select SocialSecurityNumber Into dbo.ROCAPData FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=P:\TranAssignments\ROCAPData\ROCAP.xls; Extended Properties=Excel 8.0')...[Sheet1$]

it gives me this syntax error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

What I did wrong on this command?

Thanks

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

What errors did you get when try to execute the query which I posted earlier?

Do the SQL Server Account had read permissions on the directory?

--Ramesh

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 3

This is what I typed:

Select * FROM OPENROWSET

(

'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; IMEX=1; Database=P:\TranAssignments\ROCAPData\ROCAP.xls', 'Select * from [Sheet1$]')

Here is the error message:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 4

One gentlement suggested me to use Adhoc query but I don't know the command, DO YOU?

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 5

DOES ANYONE OUT THERE KNOW HOW TO TRANSFER THE DATA FROM EXCEL TO EXISTING DATABASE IN SQL 2005?

Thanks

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

Where is this file "P:\TranAssignments\ROCAPData\ROCAP.xls" located? Is it on your local machine or on the Server (where SQL resides)?

--Ramesh

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 7

Hi Ramesh,

the file "P:\TranAssignments\ROCAPData\ROCAP.xls" is located on my PC and Server is also located on my local Server

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 8

Give the read/write permissions to the directory "P:\TranAssignments\ROCAPData" to SQL Service Account or Everyone (only if security is not an issue).

--Ramesh

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 9

What is it mean when you said: "Give the read/write permissions to the directory "P:\TranAssignments\ROCAPData" to SQL Service Account or Everyone (only if security is not an issue)"

I saved Excel file in a regular P drive, so can I show you or allow you to access to my P drive? I can you a excel file (regular excel file).

can you check your email Ramesh?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 10

Go to Window Explorer > Right click on directory "P:\TranAssignments\ROCAPData" > Click on Properties > Go to Security > Click Add > Type "Everyone" > Click Check Names (you see the text "Everyone" underlined") > Click OK > Select "Allow" Full Control from "Permissions" > Click OK

Then execute the query which I posted earlier.

--Ramesh

Linson.Daniel Ten Centuries Points: 1153 More actions · Answer 11

WayneS (5/18/2009)
see if this helps you out:
execute sp_configure 'show advanced options', 1
reconfigure
execute sp_configure 'ad hoc distributed queries', 1
reconfigure
execute sp_configure 'show advanced options', 1
reconfigure

Hey Joseph,

the answer to the question you had been asking as to how to enable ad hoc queries was right under your nose......WayneS has already answered that question for you...check if you have run the above code......