Updating table from excel sheet

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

Transact-SQL (2005)

Updating table from excel sheet

Author

Topic

ricky_1605
Starting Member

30 Posts

Posted - 2011-08-20 : 00:52:23

Hey guys

I have to update an SQL server table from an excel sheet that is generated from SAP database. I am using the following code but it is giving syntax error in line 2 and line 5:

Update Employee1 T1 Set t1.Location = T2.PSA_Text from
(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\20110819_HR_HMD.xls;HDR=YES',
'SELECT * FROM [20110819_HR_HMD$]')) T2 Where t1.PS_No = t2.PERNR

Thanks

Nipun Chawla

ricky_1605
Starting Member

30 Posts

Posted - 2011-08-20 : 01:12:28

After that i tried:

Update Employee1 Set Employee1.Location = T2.PSA_Text from Employee1
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\20110819_HR_HMD.xls;HDR=YES',
'SELECT PERNR, PSA_Text FROM [20110819_HR_HMD$]') T2 ON Employee1.PS_No = t2.PERNR
GO

This time it is giving error:
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Nipun Chawla

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:14:23

[code]Update T1 Set t1.Location = T2.PSA_Text from
Employee1 T1
JOIN (Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\20110819_HR_HMD.xls;HDR=YES',
'SELECT * FROM [20110819_HR_HMD$]'))
ON t1.PS_No = t2.PERNR
[/code]
one question though. is your excel file and sheet name same?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:15:01

is the excel on d:\ of local machine or on your server?

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

ricky_1605
Starting Member

30 Posts

Posted - 2011-08-20 : 01:20:50

Yes the excel file and sheet name are same.
The location is of local machine but the database is also on local machine for trial.

Nipun Chawla

ricky_1605
Starting Member

30 Posts

Posted - 2011-08-20 : 01:22:24

It is giving error:
Incorrect syntax near the keyword 'ON'.

Nipun Chawla

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:56:07

alias is missing


Update T1 Set t1.Location = T2.PSA_Text from 
 Employee1 t1
JOIN (Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\20110819_HR_HMD.xls;HDR=YES', 
'SELECT * FROM [20110819_HR_HMD$]')) t2
ON  t1.PS_No = t2.PERNR

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

ricky_1605
Starting Member

30 Posts

Posted - 2011-08-20 : 02:05:46

I did that but then it gives:
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Nipun Chawla

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 02:32:11

that means either excel is not in same machine where code is running or it may be that somebody have kept it open.

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

ricky_1605
Starting Member

30 Posts

Posted - 2011-08-20 : 02:38:31

No i got it. The user did not have enough rights.

Thanks a lot

Nipun Chawla

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 03:05:47

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources