Problem in Import from Excel to SQL DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Problem in Import from Excel to SQL DB

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-14 : 03:06:58

Hi Experts,

Below is the SQL script.

INSERT INTO Test SELECT * FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\testing.xls;Extended Properties=''Excel 8.0;HDR=YES''')...[Sheet1]

If i run i am getting below error.
Msg 7314, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Sheet1".
The table either does not exist or the current user does not have permissions on that table.

I tried many solutions available in Google, but i was not able to resolve it.

Can any one please provide a solution

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-14 : 03:44:54

try like


INSERT INTO Test SELECT * FROM 
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\testing.xls;Extended Properties=''Excel 8.0;HDR=YES''')...[Sheet1$]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-14 : 04:45:49

again i am getting error

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Test". The table either does not exist or the current user does not have permissions on that table.

Solutions are easy. Understanding the problem, now, that's the hard part

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-14 : 05:10:40

Hi,

I identified the solution, we should not keep the excel file in C:\ or D:\ etc....., we have to create one folder and save your excel in that folder and Try, for Ex: D:\Test\Testing.xls

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 08:58:28

quote:
Originally posted by ganeshkumar08

Hi,

I identified the solution, we should not keep the excel file in C:\ or D:\ etc....., we have to create one folder and save your excel in that folder and Try, for Ex: D:\Test\Testing.xls

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

Not neccessarily. Make sure you have access to the root drives

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources