Export excel data into sql server 2000

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 excel data into sql server 2000

Author

Topic

kashif.special2005
Starting Member

10 Posts

Posted - 2012-04-20 : 01:24:45

Hi,

I don't know how to export excel data into sql server 2000, I find below code from new but it is giving me an error message that "Incorrect Syntax near 'Excel', I don't know why please help.

Sub ADOXLtoSQLSRV()
Dim cn As ADODB.Connection
Dim strConn As String
Dim strSQL As String
Dim strXLSource As String
Dim lngRecsAff As Long

strConn = strConn & "Provider=SQLOLEDB;Data Source=;"
strConn = strConn & "Initial Catolog=AdventureWorks;Trusted_Connection=YES"
Set cn = New ADODB.Connection
cn.Open strConn
strXLSource = "C:\Users\Kashif\Desktop\Book1.xls;Extended Properties=Excel 8.0"

'
'Import by using OPENDATASOURCE.
'
'INSERT INTO
'(SQL Fields)
'
'SELECT
'
'FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=$]
strSQL = " INSERT INTO dbo.kashif1 "
strSQL = strSQL & " ([name,Salary]) "
strSQL = strSQL & " SELECT [Name,Salary] "
strSQL = strSQL & " FROM "
strSQL = strSQL & " OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',Excel 8.0, 'Data Source=" & strXLSource & "')[Sheet1$] "

Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing
End Sub

Please help.

Thanks & Regards,
Kashif.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 14:54:02

why cant you use export import wizard for this?

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-04-21 : 06:11:27

One other option is to use the Excel concatenate option. You can generate SQL statements and then run them through SSMS or SQLCMD,
http://www.sqlserver-dba.com/2012/01/sql-server-generate-sql-insert-from-excel.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-24 : 06:31:18

Have you tried this?
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