Import from Excel to Sql server Tbl

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 2000 Forums

SQL Server Development (2000)

Import from Excel to Sql server Tbl

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-06-16 : 01:04:15

I have read enough articles to do Export to Sql from Excel function. but the problem is im getting "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."
i dont have access to create linked server in the Database. Is there any other way to fix this.
pls help

SELECT * INTO TBLDeptList FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\kradhiga\Desktop\TestDataTransfer.xls', 'SELECT * FROM [Sheet1$]')

TestDataTransfer.xls

got the Data to be inserted in the table.
Table TblDeptList is already there in the DB with some data. Now i want to update few more rows
with existing.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 02:18:28

If table already there, DO NOT USE SELECT INTO!
Use INSERT TABLE SELECT * FROM OPENROWSET syntax.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 02:24:09

Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-06-16 : 02:44:04

YEs, I tried. Even that also gives me the same error

quote:
Originally posted by Peso

If table already there, DO NOT USE SELECT INTO!
Use INSERT TABLE SELECT * FROM OPENROWSET syntax.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 03:26:24

quote:
Originally posted by Sarakumar

YEs, I tried. Even that also gives me the same error

Since you will not help us helping you by giving us the specific error message, here is an extended version of OPENROWSET.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$a1:q50]')
--where f1 <> 'a'

-- Try this for extended error messages, such as permission errors on file.
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 
    'SELECT * FROM [Sheet1$]')

-- IMEX is IMport EXport mode
--
-- IMEX = 0, Export
-- IMEX = 1, Import as TEXT
-- IMEX = 2, Majority Rules (default)

-- HDR is Header
--
-- HDR = Yes, Use First Row as header
-- HDR = No, Use default F1..Fn as headers

Peter Larsson
Helsingborg, Sweden

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-16 : 03:53:33

I don't think that SQL Server has access to your 'C:\Documents and Settings\kradhiga\Desktop\TestDataTransfer.xls' folder. Use the Unicode Path like the one show above.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources