由于每月从别的公司送来一个txt文件,格式如下:
0023939608011黄根养 1000 0100100 E
0025069060011黄振垣 4440 0100200 Y
0038270387011赵荣新 1680 0100300 Y
0024431448011黄观朋 360 0100400 Y
0023759723011李关兆 600 0100500 Y 我想先把txt文件处理成每列用一个空格分开,方便导入sql数据库,本来想用 Mid 函数处理,但是我不能确定送来的txt文件格式一样,尤其是每列之间的空格数,如果不同就会出问题了。
请教下各位我应该用什么方法可以处理,麻烦大家把例子代码也写下,我vb比较菜的,呵呵
0023939608011黄根养 1000 0100100 E
0025069060011黄振垣 4440 0100200 Y
0038270387011赵荣新 1680 0100300 Y
0024431448011黄观朋 360 0100400 Y
0023759723011李关兆 600 0100500 Y 我想先把txt文件处理成每列用一个空格分开,方便导入sql数据库,本来想用 Mid 函数处理,但是我不能确定送来的txt文件格式一样,尤其是每列之间的空格数,如果不同就会出问题了。
请教下各位我应该用什么方法可以处理,麻烦大家把例子代码也写下,我vb比较菜的,呵呵
dim arr_temp() as string
'用空格将字符串分割为数组。
arr_temp = split(uFileLineData," ")
for i = 0 to UBound(arr_temp)
if len(arr_temp(i))<> 0 then
'你的处理代码
end if
next2、还一个效率很烂的方法是读入文件之后,把每行用replace替换掉多的空格。s= replace(uFileLineData," "," ")3、也可以将每行数据写入到数组中,循环检查dim bTmp() as bytebTmp = uFileLineDatafor i = 0 to Ubound(bTmp) step 2
'检查字符串中的每个字符
next4、还一个方法是用instr等字符串方法做处理。
s=replace(s," "," ")
loop用正则应该方便点,可是不知道如何最大匹配空格
Dim reg As New RegExp
reg.Pattern = "\s+( )\s+"
s = reg.Replace(s, " ")
s = "0023939608011黄根养 1000 0100100 E " & vbCrLf & _
"0025069060011黄振垣 4440 0100200 Y " & vbCrLf & _
"0038270387011赵荣新 1680 0100300 Y " & vbCrLf & _
"0024431448011黄观朋 360 0100400 Y " & vbCrLf & _
"0023759723011李关兆 600 0100500 Y "
reg.Pattern = "\s+( )\s+" ' " +?"
reg.Global = True
reg.IgnoreCase = True
reg.MultiLine = True Debug.Print reg.Replace(s, vbTab)
Private Sub Command1_Click()
Open "c:\x2.txt" For Input As #1
Open "c:\x22.txt" For Output As #2
Dim iStr As String, i As Integer, iL As Integer
Dim iField() As String, iiStr As String
While Not EOF(1)
Line Input #1, iStr
While InStr(iStr, " ")
ReDim Preserve iField(i)
iField(i) = Left(iStr, InStr(iStr, " ") - 1)
iStr = Trim(Mid(iStr, InStr(iStr, " ")))
i = i + 1
Wend
ReDim Preserve iField(i)
iField(i) = iStr
Print #2, Join(iField, ",")
i = 0
Wend
Close #1, #2
End Sub
Private Sub Command1_Click()
Open "c:\x2.txt" For Input As #1
Open "c:\x22.txt" For Output As #2
Dim iStr As String, i As Integer
Dim iField() As String
While Not EOF(1)
Line Input #1, iStr
While InStr(iStr, " ")
ReDim Preserve iField(i)
iField(i) = Left(iStr, InStr(iStr, " ") - 1)
iStr = Trim(Mid(iStr, InStr(iStr, " ")))
i = i + 1
Wend
ReDim Preserve iField(i)
iField(i) = iStr
Print #2, Join(iField, ",")
i = 0
Wend
Close #1, #2
End Sub
Open "c:\Result.txt" For Output As #2 '结果
Open "c:\1.txt" For Input As #1
Do
Line Input #1, strFileF
stra = Trim(strFileF)
Do While InStr(stra, " ") <> 0
stra = Replace(stra, " ", " ")
Loop
t = t & stra & vbCrLf
Loop While Not EOF(1)
Print #2, t: MsgBox "ok"
Close #1
Close #2
End Sub