Excel to Sql table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Excel to Sql table

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-01-19 : 05:37:47

Dear All,

I tried following queary for updateing one column of sql table from excel sheet column. But it is gives following error. Can anybody solve it?

Queary:

Update CRD7 T1 Set t1.TaxId0=T2.TaxId0 from
(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1.xls;HDR=YES',
'SELECT * FROM [Sheet1]')) T2 Where
t1.CardCode = t2.CardCode

And Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'T1'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'T2'.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-19 : 05:51:10

try this

Update T1 Set t1.TaxId0=T2.TaxId0 from
(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1.xls;HDR=YES',
'SELECT * FROM [Sheet1]')) t2
inner join CRD7 T1 on
t1.CardCode = t2.CardCode

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-01-20 : 05:45:39

Dear raky,

I tried your queary. But it gives another error. Plz tell me how to solve this error.

Error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-20 : 05:47:03

quote:
Originally posted by hspatil31

Dear All,

I tried following queary for updateing one column of sql table from excel sheet column. But it is gives following error. Can anybody solve it?

Queary:

Update CRD7 T1 Set t1.TaxId0=T2.TaxId0 from
(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1.xls;HDR=YES',
'SELECT * FROM [Sheet1]')) T2 Where
t1.CardCode = t2.CardCode

And Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'T1'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'T2'.

Here the perfectly explained exampleee,,,,,,,,
May be you find your solution here,,,,

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

Thanks...

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-20 : 06:50:18

Does the excel file have a Sheet1, or have you renamed it?

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-01-20 : 06:57:05

Ya excel file haveing Sheet1. I am not renamed it. Therefore it is giveing error. Y ?

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-21 : 02:42:05

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

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 08:42:52

[code]
Update T1 Set t1.TaxId0=T2.TaxId0 from
(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')) t2
inner join CRD7 T1 on
t1.CardCode = t2.CardCode
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources