怎样把excel导入到数据库中?

Question

请各位帮忙,怎样把excel导入到数据库中?最好有代码!谢谢!

Answer 1

用DTS啊，根据到处数据向导把DTS包生成vb模块就可以看到代码了

Answer 2

做了个例子，你看一下
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: E:\我的文档\dts.bas
'Package Name: dts
'Package Description: DTS 包描述
'Generated Date: 2005-9-23
'Generated Time: 10:49:32
'****************************************************************Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
        Set goPackage = goPackageOld        goPackage.Name = "dts"
        goPackage.Description = "DTS 包描述"
        goPackage.WriteCompletionStatusToNTEventLog = False
        goPackage.FailOnError = False
        goPackage.PackagePriorityClass = 2
        goPackage.MaxConcurrentSteps = 4
        goPackage.LineageOptions = 0
        goPackage.UseTransaction = True
        goPackage.TransactionIsolationLevel = 4096
        goPackage.AutoCommitTransaction = True
        goPackage.RepositoryMetadataOptions = 0
        goPackage.UseOLEDBServiceComponents = True
        goPackage.LogToSQLServer = False
        goPackage.LogServerFlags = 0
        goPackage.FailPackageOnLogFailure = False
        goPackage.ExplicitGlobalVariables = False
        goPackage.PackageType = 0
        
Dim oConnProperty As DTS.OleDBProperty'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------Dim oConnection As DTS.Connection2'------------- a new connection defined below.
'For security purposes, the password is never scriptedSet oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")        oConnection.ConnectionProperties("Data Source") = "E:\我的文档\aaa.xls"
        oConnection.ConnectionProperties("Extended Properties") = "Excel 3.0;HDR=YES;"
        
        oConnection.Name = "连接1"
        oConnection.ID = 1
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "E:\我的文档\aaa.xls"
        oConnection.ConnectionTimeout = 60
        oConnection.UseTrustedConnection = False
        oConnection.UseDSL = False
        
        'If you have a password for this connection, please uncomment and add your password below.
        'oConnection.Password = "<put the password here>"goPackage.Connections.Add oConnection
Set oConnection = Nothing'------------- a new connection defined below.
'For security purposes, the password is never scriptedSet oConnection = goPackage.Connections.New("SQLOLEDB")        oConnection.ConnectionProperties("Integrated Security") = "SSPI"
        oConnection.ConnectionProperties("Persist Security Info") = True
        oConnection.ConnectionProperties("Initial Catalog") = "szexam"
        oConnection.ConnectionProperties("Data Source") = "(local)"
        oConnection.ConnectionProperties("Application Name") = "DTS 导入/导出向导"
        
        oConnection.Name = "连接2"
        oConnection.ID = 2
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "(local)"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "szexam"
        oConnection.UseTrustedConnection = True
        oConnection.UseDSL = False
        
        'If you have a password for this connection, please uncomment and add your password below.
        'oConnection.Password = "<put the password here>"goPackage.Connections.Add oConnection
Set oConnection = Nothing'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint'------------- a new step defined belowSet oStep = goPackage.Steps.New        oStep.Name = "创建表 [szexam].[dbo].[Sheet1$] 步骤"
        oStep.Description = "创建表 [szexam].[dbo].[Sheet1$] 步骤"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "创建表 [szexam].[dbo].[Sheet1$] 任务"
        oStep.CommitSuccess = False
        oStep.RollbackFailure = False
        oStep.ScriptLanguage = "VBScript"
        oStep.AddGlobalVariables = True
        oStep.RelativePriority = 3
        oStep.CloseConnection = False
        oStep.ExecuteInMainThread = False
        oStep.IsPackageDSORowset = False
        oStep.JoinTransactionIfPresent = False
        oStep.DisableStep = False
        oStep.FailPackageOnError = False
        
goPackage.Steps.Add oStep
Set oStep = Nothing'------------- a new step defined belowSet oStep = goPackage.Steps.New        oStep.Name = "Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 步骤"
        oStep.Description = "Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 步骤"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 任务"
        oStep.CommitSuccess = False
        oStep.RollbackFailure = False
        oStep.ScriptLanguage = "VBScript"
        oStep.AddGlobalVariables = True
        oStep.RelativePriority = 3
        oStep.CloseConnection = False
        oStep.ExecuteInMainThread = True
        oStep.IsPackageDSORowset = False
        oStep.JoinTransactionIfPresent = False
        oStep.DisableStep = False
        oStep.FailPackageOnError = False
        
goPackage.Steps.Add oStep
Set oStep = Nothing'------------- a precedence constraint for steps defined belowSet oStep = goPackage.Steps("Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 步骤")
Set oPrecConstraint = oStep.precedenceConstraints.New("创建表 [szexam].[dbo].[Sheet1$] 步骤")
        oPrecConstraint.StepName = "创建表 [szexam].[dbo].[Sheet1$] 步骤"
        oPrecConstraint.PrecedenceBasis = 0
        oPrecConstraint.Value = 4
        
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------'------------- call Task_Sub1 for task 创建表 [szexam].[dbo].[Sheet1$] 任务 (创建表 [szexam].[dbo].[Sheet1$] 任务)
Call Task_Sub1(goPackage)'------------- call Task_Sub2 for task Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 任务 (Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 任务)
Call Task_Sub2(goPackage)'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
Set goPackage = NothingSet goPackageOld = NothingEnd Sub

Answer 3

接上
'------------- define Task_Sub1 for task 创建表 [szexam].[dbo].[Sheet1$] 任务 (创建表 [szexam].[dbo].[Sheet1$] 任务)
Public Sub Task_Sub1(ByVal goPackage As Object)Dim oTask As DTS.Task
Dim oLookup As DTS.LookupDim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask        oCustomTask1.Name = "创建表 [szexam].[dbo].[Sheet1$] 任务"
        oCustomTask1.Description = "创建表 [szexam].[dbo].[Sheet1$] 任务"
        oCustomTask1.SQLStatement = "CREATE TABLE [szexam].[dbo].[Sheet1$] (" & vbCrLf
        oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[F1] nvarchar (255) NULL" & vbCrLf
        oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & ")"
        oCustomTask1.ConnectionID = 2
        oCustomTask1.CommandTimeout = 0
        oCustomTask1.OutputAsRecordset = False
        
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = NothingEnd Sub'------------- define Task_Sub2 for task Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 任务 (Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 任务)
Public Sub Task_Sub2(ByVal goPackage As Object)Dim oTask As DTS.Task
Dim oLookup As DTS.LookupDim oCustomTask2 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask        oCustomTask2.Name = "Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 任务"
        oCustomTask2.Description = "Copy Data from Sheet1$ to [szexam].[dbo].[Sheet1$] 任务"
        oCustomTask2.SourceConnectionID = 1
        oCustomTask2.SourceSQLStatement = "select `F1` from `Sheet1$`"
        oCustomTask2.DestinationConnectionID = 2
        oCustomTask2.DestinationObjectName = "[szexam].[dbo].[Sheet1$]"
        oCustomTask2.ProgressRowCount = 1000
        oCustomTask2.MaximumErrorCount = 0
        oCustomTask2.FetchBufferSize = 1
        oCustomTask2.UseFastLoad = True
        oCustomTask2.InsertCommitSize = 0
        oCustomTask2.ExceptionFileColumnDelimiter = "|"
        oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
        oCustomTask2.AllowIdentityInserts = False
        oCustomTask2.FirstRow = 0
        oCustomTask2.LastRow = 0
        oCustomTask2.FastLoadOptions = 2
        oCustomTask2.ExceptionFileOptions = 1
        oCustomTask2.DataPumpOptions = 0
        
Call oCustomTask2_Trans_Sub1(oCustomTask2)
                
                
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = NothingEnd SubPublic Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object)        Dim oTransformation As DTS.Transformation2
        Dim oTransProps As DTS.Properties
        Dim oColumn As DTS.Column
        Set oTransformation = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
                oTransformation.Name = "DirectCopyXform"
                oTransformation.TransformFlags = 63
                oTransformation.ForceSourceBlobsBuffered = 0
                oTransformation.ForceBlobsInMemory = False
                oTransformation.InMemoryBlobSize = 1048576
                oTransformation.TransformPhases = 4
                
                Set oColumn = oTransformation.SourceColumns.New("F1", 1)
                        oColumn.Name = "F1"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 102
                        oColumn.Size = 255
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing                Set oColumn = oTransformation.DestinationColumns.New("F1", 1)
                        oColumn.Name = "F1"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 102
                        oColumn.Size = 255
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing        Set oTransProps = oTransformation.TransformServerProperties
                
        Set oTransProps = Nothing        oCustomTask2.Transformations.Add oTransformation
        Set oTransformation = NothingEnd Sub

Answer 4

太长了,好麻烦啊!我看不太懂!有没有简单一些的!而且我对哪个DTS不太懂!

Answer 5

1、如Excel文件与服务器（sql server)在同一台机器 上
2、数据库是Access
满足以上条件之一，可参考：
http://blog.csdn.net/myhgyp/archive/2005/01/13/252302.aspx

Answer 6

to  bbhere(阿忠) :
 用你的代码，编译没问题 ，可是执行后数据库并没有 什么变化啊？