Export data from Excel into an existing SQL Table

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

Export data from Excel into an existing SQL Table

Author

Topic

gv19762000
Starting Member

4 Posts

Posted - 2007-12-17 : 13:09:40

Hi everyone, I am new with SQL and I tried to use the code below to export data from Excel into an existing SQL table, but I keep on receiving the following message.

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

To export data from Excel to existing SQL Server table,

Insert into dbo.Base_Intraday Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Shortcuts\6 - Bolsa de Valores\1 - Bolsa de Valores - Bovespa;HDR=YES',
'SELECT * FROM [Link$]')

Can anyone help me on this?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-17 : 16:46:23

Is the file on sql server's c: drive?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 16:57:29

The path to the file is relative from the SQL Server point of view, not your workstation.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 16:59:14

Also see http://weblogs.sqlteam.com/peterl/archive/2007/10/24/Getting-errors-when-working-with-Excel-and-SQL-Server.aspx
and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Moderator, please move this topic as it is not a working script.

E 12°55'05.25"
N 56°04'39.16"

gv19762000
Starting Member

4 Posts

Posted - 2007-12-17 : 19:05:35

Hi, how can I get the path to the file? I do not know how to do it.

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-17 : 19:55:43

If it's on remote machine, should reference it with unc name like \\host_name\sharfile.

gv19762000
Starting Member

4 Posts

Posted - 2007-12-18 : 08:57:18

Thank you all for the help. It worked perfectly, but I still have one question. On my Excel table I have three columns, named Cd., lt. and Mx. and I want to export into my SQL table only the first two columns and rename them as CDIGO and LTIMO. How can I do this?

The code I use is below, can anyone rewrite it considering the fact above?

select * into [Base Intraday] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Shortcuts\6 - Bolsa de Valores\1 - Bolsa de Valores - Bovespa\Link DDE.xls;HDR=YES',
'SELECT * FROM [Link DDE$]')

Thank you again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 09:01:04

select col1 as CDIGO , col2 as LTIMO into [Base Intraday] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Shortcuts\6 - Bolsa de Valores\1 - Bolsa de Valores - Bovespa\Link DDE.xls;HDR=YES',
'SELECT * FROM [Link DDE$]')

Madhivanan

Failing to plan is Planning to fail

gv19762000
Starting Member

4 Posts

Posted - 2007-12-18 : 10:23:23

Thank you madhivanan, it worked.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-19 : 00:56:15

For more informations refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources