sp;在实际应用当中我们经常需要对数据库进行导入导出操作SQL自带DTS向导应该说是很方便工具但是有时候这种导入操作是要求客户自己来完成这样就要求我们在代码中实现这种功能而且根据实际需要操作要更加友好下面就以VB+SQL为例根据本人点经验做些简单介绍

1. 原文档提供
实际当中最常用两种格式是txt、xls即文本文件和Excel文件

1.1 文本文件
文本文件在导入时候最要注意就是行和列分隔即用什么来区分行、列从而和数据库中目标表中字段来进行对应般说来分隔行般用回车、换行符而列则般可用tab、\",\"、\";\"、\"|\"等符号

1.2 Excel文件
Excel文件和数据库中表有以下对应关系

Excel table
\"sheet name\" \"table\"
\"A1\" \"field name 1\"
\"B2\" \"field name 2\"
...

另外要注意是在导入时无论文本文件或是Excel文件第行都是可选做目标表字段名


2. 导入数据思路方法
如果你愿意完全可以用代码来控制实现从文本文件或Excel文件条条地读出数据然后在条条地insert到表中但这显然是不能满足大量数据导入在实际当中比较常用有:SQLopendatasource方式bcp方式以及DTS方式等等各有各特点

3. 举例
由于篇幅等限制下面仅举个用DTS导入文本文件例子(如果你缺少点DTS基础知识请先参阅下这方面介绍)

假设文本文件名为test.txt其中数据格式为:

0001 ANNA MALTELLI
0002 MARIA SHEARER
0003 SHANDA CARROLL
...

我们首先分析这个文件是以tab来分隔列以(Cr)(Lf)来分隔行
以下是个SQL目标数据库些信息:
Private strServerName As String ´Server Name
Private strDBName As String ´Database Name
Private strDBUser As String ´Database User Name
Private strDBUserPsd As String ´Database User Password
这些变量假设在次的前都已经正确附值了在下面代码中就直接用了

下面开始动手:

引用:Microsoft DTSPackage Object Library

下面是完成数据导入主function
进参:文本文件路径及文件名
返回:成功True
失败False 及 号

Private Function Import_DTS(strPath As String) As Boolean
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step
Dim oTask As DTS.Task
Dim oPackage As DTS.Package2
Dim oCustomTask As DTS.BulkInsertTask
_disibledevent=>
´建立数据包
Set oPackage = New DTS.Package
oPackage.FailOnError = True
Set oConnection = oPackage.Connections.New(\"SQLOLEDB\")
Set oStep = oPackage.Steps.New
Set oTask = oPackage.Tasks.New(\"DTSBulkInsertTask\")
Set oCustomTask = oTask.CustomTask

´和数据库进行连接为了清楚起见这里我单独写了个过程即 SetConnections
´具体可以参考Private Sub SetConnections
SetConnections oPackage, oConnection

With oStep
.Name = \"GenericPkgStep\"
.ExecuteInMainThread = True
End With

´Customize the Task Object
With oCustomTask
.Name = \"GenericPkgTask\"
´文本文件路径
.DataFile = strPath
.ConnectionID = 1


´注意目标表写法
.DestinationTableName = strDBName & \"..DriverName\"
´以tab分隔列
.FieldTerminator = VBA.Chr(9)
´以换行回车分隔行
.RowTerminator = vbCrLf
End With

oStep.TaskName = oCustomTask.Name

´添加包
With oPackage
.Steps.Add oStep
.Tasks.Add oTask
.FailOnError = True
End With

´运行包
oPackage.Execute

´释放
Set oConnection = Nothing
Set oCustomTask = Nothing
Set oTask = Nothing
Set oStep = Nothing
oPackage.UnInitialize
Set oPackage = Nothing
Exit Function
´以下是出错处理
PackageError:
Dim strMsg As String
strMsg = \"Package failed error: \" & ErrorNumConv(Err.Number) & _
vbCrLf & Err.Description & vbCrLf & AccumStepErrors(oPackage)
MsgBox strMsg, vbExclamation, oPackage.Name
Set oConnection = Nothing
Set oCustomTask = Nothing
Set oTask = Nothing
Set oStep = Nothing
oPackage.UnInitialize
Set oPackage = Nothing
Import_DTS = False
End Function

´以下是数据包和数据库连接部分:
Private Sub SetConnections(oPackage As DTS.Package, oConnection As DTS.Connection)

With oConnection
.ConnectionProperties(\"Persist Security Info\") = True
.ConnectionProperties(\"User ID\") = strDBUser
.ConnectionProperties(\"Initial Catalog\") = strDBName
.ConnectionProperties(\"Data Source\") = strServerName
.Catalog = strDBName
.DataSource = sServerName
.UserID = strDBUser
.Password = strDBUserPsd
.ID = 1
.Reusable = True
.ConnectImmediate = False
.UseTrustedConnection = False
End With
oPackage.Connections.Add oConnection

Set oConnection = Nothing
End Sub

´以下是出错处理
Private Function ErrorNumConv(ByVal lngErrNum As Long) As String

If lngErrNum < 65536 And lngErrNum > -65536 Then
ErrorNumConv = \"x\" & Hex(lngErrNum) & \", \" & CStr(lngErrNum)
Else
ErrorNumConv = \"x\" & Hex(lngErrNum) & \", x\" & _
Hex(lngErrNum And -65536) & \" + \" & CStr(lngErrNum And 65535)
End If

End Function

Private Function AccumStepErrors(ByRef oPackage As DTS.Package) As String
Dim oStep As DTS.Step
Dim strMessage As String
Dim lngErrNum As Long
Dim strDescr As String
Dim strSource As String

´查找出错地
For Each oStep In oPackage.Steps
If oStep.ExecutionStatus = DTSStepExecStat_Completed Then
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
´得到出错信息
oStep.GetExecutionErrorInfo lngErrNum, strSource, strDescr
strMessage = strMessage & vbCrLf & _
\"Step \" & oStep.Name & \" failed, error: \" & _


ErrorNumConv(lngErrNum) & vbCrLf & strDescr & vbCrLf
End If
End If
Next
AccumStepErrors = strMessage

End Function


以上代码在win2k pro + sql2k下调试通过

本人在学习DTS过程中参考了大量SQL自带sample代码确实收获不小特此撰文给朋友们做为点参考我想在以后时间里尽可能地多整理总结下自己在学习应用过程中心得体会体会希望和大家多交流也希望大家接受点批评指正


minajo21(大眼睛)