将文本文件导入SQL

[mmla]

有这样格式的文本文件:

32111111050430pp
12333333050431pp
...
21123456050401pp

要把它拆成"32","111111","050430","pp"这种形式后导入数据库,不知该怎么做?谢谢帮忙

[icedut]

1 可以先把文本文件倒入数据库
再在数据库中,实现你的要求

2 读文本文件的时候,就按照要求,分好各个部分,然后插入数据库

[icedut]

/** 导入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'

/** 导出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'

导出到TXT文本，用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'

[mmla]

能否给个详细点的例子,谢谢

[icedut]

如果楼主不会txt文件操作
随便搜索一下就会有很多的

[icedut]

给楼住一个以前用vb的例子,你可以参考一下
On Error GoTo ErrorExit
Dim strBar13 As String '条形码
Dim strScan As String '扫描员工代码
Dim i As String
Dim strTxt As String
strTxtFile = App.Path & "\Txt\" & "TransOut.txt"
If Dir(strTxtFile) = "" Then
MsgBox "文件不存在", , "提示"
Exit Sub
End If
Screen.MousePointer = vbHourglass
Cn.Execute "DELETE FROM TransBar13Affirm WHERE UC='" & LoginCode & "'"
Open strTxtFile For Input As #1
Do While Not EOF(1)
Input #1, strTxt
i = Right(strTxt, 1)
If i = "1" Then
strBar13 = Left(Right(strTxt, 14), 13)
strScan = Left(strTxt, 2)
strSql = "INSERT INTO TransBar13Affirm(UC,Bar13Trans,TransCode,Shop2," _
& "TransDate,TransMode,Affirm,ScanUser) " _
& "SELECT '" & LoginCode & "',a.Bar13Trans,a.TransCode,Shop2," _
& "a.TransDate,b.TransMode,1,'" & strScan & "' " _
& "FROM TransBar13 a INNER JOIN TransBar13Para b " _
& "ON a.TransAffirm=0 AND a.Bar13Trans=b.Bar13Trans " _
& "AND a.Bar13Trans='" & strBar13 & "' "
Cn.Execute strSql
End If
Loop
Close #1

[icedut]

http://search.csdn.net/Expert/topic/2353/2353163.xml?temp=.1009638

转贴
Imports System
Imports System.IO
Imports System.Collections
Class ReadData
Protected elements As ArrayList = new ArrayList()
Public Shared Sub Main()
Dim data As ReadData = New ReadData("test.txt")'测试程序
Console.WriteLine(data.Item(0, "SUBGRP"))'取第一组元素的SUBGRP字段值
End Sub

Public Sub New(filename As String)
Dim sr As StreamReader = new StreamReader(filename)'读文件
Dim str As String = sr.ReadLine()'读行
Dim hash As Hashtable = Nothing'存储每组元素的键值对
Dim value As String = Nothing
Dim key As String = Nothing
while Not (str is Nothing)
If (str.StartsWith("SERVICES :"))'每组的开始标志
If Not(hash is Nothing)
elements.Add(hash)'将前一个组加入链表
End If
hash = new Hashtable()'重新开始组
Else
Dim index As Integer = str.IndexOf(":")
If (index < 0)'如果没有':'，表明这行是上一个键的值
value += str
If Not(key is Nothing)
hash.Item(key) = value'重新设置值
End If
Else
key = str.Substring(0, index).Trim()'取键
value = str.Substring(index + 1).Trim()取值
hash.Add(key, value)'加入当前组
End If
End If
str = sr.ReadLine()
End While
If Not(hash is Nothing)
elements.Add(hash)'将最后一个组加入链表
End If
End Sub
Public Function Item(index As Integer, key As String) As String'取索引表示的组的某个键的值
Return CType(CType(elements.Item(index), Hashtable).Item(key), String)
End Function
End Class

[mmla]

谢谢你,