大哥大姐: 请教如何用VB 把如下的保存为Txt 型式的数据导入SQL Server:
如 文件名称为 a.txt 资料如下
"001:0000050297:20040816:1:092112:11"
"001:0000044912:20040816:1:094057:11"
"001:0000045100:20040816:1:094848:11"
"001:0000046094:20040816:1:103659:11"
"001:0000050207:20040816:1:103957:11"
"001:0000045072:20040816:1:104002:11"
"001:0000044577:20040816:1:104258:11"我想通过VB 代码把此资料读出,并且按照分号分开存入DB里面的字段如下
001 0000050297 20040816 1 092112 11
也就是说以分号隔开的数据就算一个字段.
如 文件名称为 a.txt 资料如下
"001:0000050297:20040816:1:092112:11"
"001:0000044912:20040816:1:094057:11"
"001:0000045100:20040816:1:094848:11"
"001:0000046094:20040816:1:103659:11"
"001:0000050207:20040816:1:103957:11"
"001:0000045072:20040816:1:104002:11"
"001:0000044577:20040816:1:104258:11"我想通过VB 代码把此资料读出,并且按照分号分开存入DB里面的字段如下
001 0000050297 20040816 1 092112 11
也就是说以分号隔开的数据就算一个字段.
解决方案 »
- vb串口接收不断发过来的数据
- VB中的Adodc1.RecordSource中可以使用SQL的插入insert或delete语句?
- 如何找到窗体中一个textbox的句柄
- 如何得到win2000的服务列表?高手帮忙啊~~~~~
- 怎么样把数据写入DATAGRID的第X行Y 列
- word的读写?(在线等)
- 请有经验的,做过大程序的大蚱进来指点指点
- 简单问题 array
- picturebox当做视频窗口时如何响应鼠标事件?
- 100分求救!我在万网购买了国际域名(100那种),我在别处有虚拟主机,我想让 域名+相应 目录可以指向虚拟主机的相应目录.请问具体应如何设置?详细说明在内容中,清帮帮忙,一定给分!!!
- .
- 如何強行結束EXCEL進程啊????
方法二:改成用","分隔,然后:
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'
数据是有规律的你可以搜索一下 CSDN应该又这样的例子
另外如何把两边的引号去掉啊?
//读到字符串中然后replace(str,""","")
'调用方法:ReadFile(文件的路径和文件名)
Function ReadFile(pFile As String) As String
Dim fN As Integer
fN = FreeFile
Open pFile For Binary Access Read As #fN
ReadFile = Space(FileLen(pFile))
Get #fN, , ReadFile
Close #fN
End Function
可是ReadFile 读出的是所有txt 文件,不是一条一条读出的,
// myhgyp(也许是这样的,信不信由你) 的方法是可行的
可是我不知道如何一条一条读出,所有的代码都是读出全部的,'引 用:Microsoft Scripting RuntimeFunction FsoRead(ByVal pFile As String) As String
On Error Resume Next
Dim u As Object
Dim X As TextStream
Set u = CreateObject("scripting.filesystemobject")
Set X = u.OpenTextFile(pFile, ForReading, True)
FsoRead = X.ReadAll
Exit Function
End Function
FsoRead 读出的也是全部的,
Public strError As String
Dim myDataBase As New clsDataBase
Dim myOperation As New clsOperation
Public Function ImportTextFile(FileFullPath As String, strSql As String, _
Optional FieldDelimiter As String = ",", _
Optional RecordDelimiter As String = vbCrLf) As Boolean 'PURPOSE: Imports a delimited text file into a database 'PARAMTERS: cn -- an open ado connection
' : tblName -- import destination table name
' : FileFullPath -- Full Path of File to import form
' : FieldDelimiter -- (Optional) String character(s) in
' file separating field values
' within a record; defaults
' to ","
' : RecordDelimiter -- (Optional) String character(s)
' separating records within text
' file; defaults to vbcrlf 'RETURNS: True if successful, false otherwise
'EXAMPLE:
'dim cn as new adodb.connection
'cn.connectionstring = _
' "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb"
'cn.open
'ImportTextFile cn, "MyTable", "C:\myCSVFile.csv" 'REQUIRES: VB6
On Error GoTo errHandle:
Dim rs As New ADODB.Recordset
Dim sFileContents As String
Dim iFileNum As Integer
Dim sTableSplit() As String
Dim sRecordSplit() As String
Dim lCtr As Integer
Dim iCtr As Integer
Dim iFieldCtr As Integer
Dim lRecordCount As Long
Dim iFieldsToImport As Integer
'These variables prevent
'having to requery a recordset
'for each record
Dim asFieldNames() As String
Dim abFieldIsString() As Boolean
Dim iFieldCount As Integer
Dim sSQL As String
Dim bQuote As Boolean
If Dir(FileFullPath) = "" Then
strError = "临时存储文件:" & FileFullPath & "没有找到!"
Exit Function
End If
Call myDataBase.ExecuteRst(strSql, rs)
iFieldCount = rs.Fields.Count iFileNum = FreeFile
Open FileFullPath For Input As #iFileNum
sFileContents = Input(LOF(iFileNum), #iFileNum)
Close #iFileNum
'split file contents into rows
sTableSplit = Split(sFileContents, RecordDelimiter)
lRecordCount = UBound(sTableSplit)
'make it "all or nothing: whole text
'file or none of it
myDataBase.BeginTransaction
For lCtr = 0 To lRecordCount - 1
'split record into field values
sRecordSplit = Split(sTableSplit(lCtr), FieldDelimiter)
iFieldsToImport = IIf(UBound(sRecordSplit) + 1 < _
iFieldCount, UBound(sRecordSplit) + 1, iFieldCount)
rs.AddNew
rs.Fields("ModalLength") = myOperation.GetModalLength '型号长度
For iCtr = 0 To iFieldsToImport - 1
If LCase(rs.Fields(iCtr).Name) = LCase("goodsbar") Then
rs.Fields("modal_id") = Left(Trim(CStr(sRecordSplit(iCtr))), myOperation.GetModalLength)
End If
If LCase(rs.Fields(iCtr).Name) <> "modallength" Then '长度已经增加完毕
rs.Fields(iCtr).Value = IIf(Trim(CStr(sRecordSplit(iCtr))) = "", Null, Trim(CStr(sRecordSplit(iCtr))))
End If
Next iCtr
rs.Update
Next lCtr
myDataBase.CommitTransaction
Close #iFileNum
Set rs = Nothing
ImportTextFile = True
Exit Function
errHandle:
' On Error Resume Next
myDataBase.RollBackTransaction
If iFileNum > 0 Then Close #iFileNum
Set rs = Nothing
strError = Err.Description
End FunctionPrivate Function FieldIsString(FieldObject As ADODB.Field) _
As Boolean Select Case FieldObject.Type
Case adBSTR, adChar, adVarChar, adWChar, adVarWChar, _
adLongVarChar, adLongVarWChar
FieldIsString = True
Case Else
FieldIsString = False
End SelectEnd FunctionPrivate Function prepStringForSQL(ByVal sValue As String) _
As String Dim sAns As String
sAns = Replace(sValue, Chr(39), "''")
sAns = "'" & sAns & "'"
prepStringForSQL = sAnsEnd FunctionPrivate Sub Class_Terminate()
Set myDataBase = Nothing
Set myOperation = Nothing
End Sub