import from excel to sql server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
go


INSERT INTO [sqlDb].[dbo].[pages]([page_title],[model_name],[page_num])

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\MF 240\Excel file\b.xls;IMEX=1;HDR=YES','select [Sub Group Name],[Model],[pagenum] from [Sheet1$]')

i have been trying to make this import from excel to sql server work for some time now but with no success. it keeps giving me this error even though my database table column name is exactly the same . i have tried various tweaking but still its giving problem.

Msg 207, Level 16, State 1, Line 5
Invalid column name 'page_title'.

i have express edition of sql server so the import export wizzard is not even working nor could i get the dts working .. so any headsup would be great thank you ...

Posted 7-Dec-11 8:37am

mubarakahmad

Updated 7-Dec-11 8:55am

Wendelius

v2

Add a Solution

4 solutions

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)

RaviRanjanKr · Answer 1 · 2011-12-07T09:03:00

Solution 2

Try
read-import-excel-file[^]
Import Data to SQL Server from Excel or Access using TSQL Script[^]

Posted 7-Dec-11 9:03am

RaviRanjanKr

Comments

mubarakahmad 7-Dec-11 21:05pm

thankyou, i tried that but it didnt work aswell

RaviRanjanKr 8-Dec-11 7:21am

Thank :)

Wendelius · Answer 2 · 2011-12-07T08:57:00

Solution 1

Have you double checked that the table pages in your database contains a column named page_title? For example that the column name isn't Page_Title.

Posted 7-Dec-11 8:57am

Wendelius

Comments

mubarakahmad 7-Dec-11 20:45pm

yes , i did that the column name is the same.that is what makes it even more annoying :)

Wendelius 8-Dec-11 0:02am

One thing you could try is to run the following. Still getting the same error?


INSERT INTO [sqlDb].[dbo].[pages]([page_title],[model_name],[page_num])
SELECT [Sub Group Name] AS [page_title],[Model] AS [model_name],[pagenum] AS [page_num]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\MF 240\Excel file\b.xls;IMEX=1;HDR=YES','select [Sub Group Name],[Model],[pagenum] from [Sheet1$]')

Nikil S · Answer 3 · 2011-12-07T10:34:00

Solution 3

Is it possible that you have the table [pages] in a different database?

Also Try,

INSERT INTO [dbo].[pages]([page_title],[model_name],[page_num])

Posted 7-Dec-11 10:34am

Nikil S

Comments

mubarakahmad 7-Dec-11 20:47pm

No the database is same i have checked that aswell.

Eduard Lu · Answer 4 · 2011-12-07T14:25:00

Solution 4

Try SELECT only page_title. You must have a return value thru this. If this doesn't, check if page_title do really exists. :)

Regards,
Eduard

Posted 7-Dec-11 14:25pm

Eduard Lu

Comments

mubarakahmad 7-Dec-11 20:58pm

well when i comment out this line SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\MF 240\Excel file\b.xls;IMEX=1;HDR=yes','select [Sub Group Name],[Model],[pagenum] from [Sheet1$]') and run just this select 'page_title' as page_title,'model_name' as model_name,'page_num' as page_num FROM [sqlDb].[dbo].[pages] i can see the columns on execution... so my column names right its something to do with the openrowset line