IMport from Excel into SQL

I am trying to import a spreadsheet into a SQL Table.

I have been trying to do this for 3 days no with no luck. Its beginning to get really frustrating.

By the looks of it the best way is to use linked Servers. I will attach how I have setup the linked server as a picture).

I then use the attached code to do the import and recieve the following error:

" Msg 208, Level 16, State 1, Line 3
Invalid object name 'EXCELLINK'. "

I have run " select * from sys.servers " and it returns that there is a Linked Server called EXCELLINK.

Can anybody suggest anything?

SELECT * INTO sanctionslist FROM EXCELLINK[sanctionslist$]

Open in new window

untitled.JPG
LVL 1
ict-torquilclarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mfreudenCommented:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel
8.0;DataSource=C:\purchases.xls','SELECT * FROM [sheet1$]')

This is for excel 2003 and a 32 bit SQL Server.


0
ict-torquilclarkAuthor Commented:
I have run the attached code and have received the following error

"
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'SELECT * FROM [sanctionlist$]'.
"

The worksheet is definetly called sanctionlist
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel
8.0;DataSource=C:\SANCTIONSLIST.xls','SELECT * FROM [sanctionlist$]'

Open in new window

0
RiteshShahCommented:
why don't you create Excel linked server again. just drop existing and create new one with the steps I have mentioned in my article at below link.

http://www.sqlhub.com/2009/04/create-linked-server-with-excel-2007.html

I was using Excel 2007, if you are using lesser version, keep your provider to Microsoft.Jet.OLEDB.4.0 rather than Microsoft.ACE.OLEDB.12.0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.