| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛 | 
登录
注册
用户控制面板
搜索
道具
风格
短消息
论坛展区
帮助
编程论坛 → 开发语言 → 『 VB6论坛 』 → 请教：vb如何快速处理较大的txt文件？
我的收件箱(0)
欢迎加入我们，一同切磋技术
用户名：   
 
密　码：  
共有 782 人关注过本帖
标题：请教：vb如何快速处理较大的txt文件？
只看楼主 加入收藏
qbh555
Rank: 1
等　级：新手上路
帖　子：7
专家分：0
注　册：2012-12-26
结帖率：100%
楼主
收藏
 已结贴√  问题点数：20 回复次数：10 
请教：vb如何快速处理较大的txt文件？
附件.rar (184.9 KB)
用vb快速处理txt，提取需要的信息输出保存在excel中如附件，请问代码怎么写呢？之前我用 Open Name For Input As #1 ,Do While Not EOF(1) ,Line Input #1, txtline 来处理，但处理速度非常的慢，半个小时都出不来结果。求教了，谢谢各位。

[ 本帖最后由 qbh555 于 2013-5-20 20:44 编辑 ]
搜索更多相关主题的帖子: 如何　
2013-05-20 20:27
bczgvip
Rank: 14Rank: 14Rank: 14Rank: 14
等　级：贵宾
威　望：66
帖　子：1310
专家分：5312
注　册：2009-2-26
第 2 楼
收藏
 得分:5 
文件怎么大么，个人推荐用正则表达式来提取需要的数据，不过对新手来说就是坑人了。
2013-05-21 00:29
风吹过b
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等　级：贵宾
威　望：364
帖　子：4947
专家分：30084
注　册：2008-10-15
第 3 楼
收藏
 得分:15 
操作 excel 慢，超级慢。
TXT ，还可以，虽然慢的可以 。

授人于鱼，不如授人于渔
早已停用QQ了
2013-05-21 08:18
qbh555
Rank: 1
等　级：新手上路
帖　子：7
专家分：0
注　册：2012-12-26
第 4 楼
收藏
 得分:0 
回复 3楼 风吹过b
是啊，文件很大，（其实是有好几个这样的文件），边读边输入到EXCEL很慢，全部读取完再一下写入也很慢啊。
2013-05-21 08:59
qbh555
Rank: 1
等　级：新手上路
帖　子：7
专家分：0
注　册：2012-12-26
第 5 楼
收藏
 得分:0 
回复 2楼 bczgvip
悲催啊
2013-05-21 09:04
风吹过b
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等　级：贵宾
威　望：364
帖　子：4947
专家分：30084
注　册：2008-10-15
第 6 楼
收藏
 得分:0 
如果不是很必要，可以选择使用 CSV 格式的文件操作。

这个文件，与 EXCEL 文件部分兼容，不支持设置各种格式，不支持设置网格，不支持公式，不支持合并单元格，不支持单元格大小。

授人于鱼，不如授人于渔
早已停用QQ了
2013-05-21 09:45
qbh555
Rank: 1
等　级：新手上路
帖　子：7
专家分：0
注　册：2012-12-26
第 7 楼
收藏
 得分:0 
回复 6楼 风吹过b
CSV格式输出也可以只要得到结果，然后处理速度只要不用登上半个小时没反应就可以，请教代码如何编写呢？
2013-05-22 14:31
qbh555
Rank: 1
等　级：新手上路
帖　子：7
专家分：0
注　册：2012-12-26
第 8 楼
收藏
 得分:0 
回复 3楼 风吹过b
以下可以处理，就是速度非常的慢，请教快速处理方法。


Private Sub Command1_Click()
'/////////////////////////////////////////////////////////////////////////
  MsgBox "选择输出路径"
   With Application.FileDialog(msoFileDialogFolderPicker)
    If (.Show = -1) Then
        mypathout = .SelectedItems(1)
        End If
    End With
    If mypathout = "" Then
        Exit Sub
    End If
   mypathout = mypathout + "\"
'///////////////////////////////////////////////////////////////////////////
  Dim sig1(65535) As Variant
  Dim sig2(65535) As Variant
  Dim sig3(65535) As Variant
  Dim sig4(65535) As Variant
  Dim sig5(65535) As Variant
  Dim sig6(65535) As Variant
  Dim sig7(65535) As Variant
  Dim sig8(65535) As Variant
  Dim sig9(65535) As Variant
  Dim sig10(65535) As Variant
  Dim sig11(65535) As Variant
  Dim sig12(65535) As Variant
  g1 = 1
  g2 = 1
  g3 = 1
  g4 = 1
  g5 = 1
  g6 = 1
  g7 = 1
  g8 = 1
  g9 = 1
  g10 = 1
  g11 = 1
  g12 = 1
  MsgBox "选择统计文件"
  With Application.FileDialog(msoFileDialogFolderPicker)
           If (.Show = -1) Then
            MyPath = .SelectedItems(1)
           End If
     End With
    If MyPath = "" Then
        Exit Sub
    End If
   MyPath = MyPath + "\"
   MyName = Dir(MyPath, vbDirectory)
Do While MyName <> ""
 If MyName <> "." And MyName <> ".." Then
     Open MyPath + MyName For Input As #1
         Do While Not EOF(1)
                   Line Input #1, mmlline
            If InStr(mmlline, "网元") > 0 Then
                   Line Input #1, mmlline
                   If InStr(mmlline, "CD-RNC") Then
                   GoTo qqq
                   End If
                   nodebname = Trim(Mid(mmlline, 2, 50))
                   Line Input #1, mmlline
                   Line Input #1, mmlline
              If InStr(mmlline, "网元断连") <= 0 And InStr(mmlline, "网元响应MML命令超时") <= 0 And InStr(mmlline, "没有权限") <= 0 Then
                   Line Input #1, mmlline
                   Line Input #1, mmlline
                   Line Input #1, mmlline
                   Line Input #1, mmlline
                   Line Input #1, mmlline
                              If InStr(mmlline, "没有查到相应的结果") > 0 Then
                              GoTo qqq
                              End If
                   Line Input #1, mmlline
                   Line Input #1, mmlline
               If InStr(mmlline, "=") <= 0 Then
                 If InStr(mmlline, "相关DSP号") > 0 Then
                   Line Input #1, mmlline
                   Line Input #1, mmlline
qiu:                    mmlline1 = mmlline
                             For j = 50 To 1 Step -1
                             mmlline1 = Replace(Trim(mmlline1), Application.Rept(" ", j), "|")
                             Next
                   a = Split(mmlline1, "|")
                   sig1(g1) = nodebname
                   sig2(g2) = a(0)
                   sig3(g3) = a(1)
                   sig4(g4) = a(2)
                   sig5(g5) = a(3)
                   sig6(g6) = a(4)
                   sig7(g7) = a(5)
                   sig8(g8) = a(6)
                   sig9(g9) = a(7)
                   sig10(g10) = a(8)
                   sig11(g11) = a(10)
                   sig12(g12) = a(11)
                   g1 = g1 + 1
                   g2 = g2 + 1
                   g3 = g3 + 1
                   g4 = g4 + 1
                   g5 = g5 + 1
                   g6 = g6 + 1
                   g7 = g7 + 1
                   g8 = g8 + 1
                   g9 = g9 + 1
                   g10 = g10 + 1
                   g11 = g11 + 1
                   g12 = g12 + 1
                   Line Input #1, mmlline
                                If InStr(mmlline, "结果个数") <= 0 Then
                                GoTo qiu
                                End If
              Else
                   Line Input #1, mmlline
                   Line Input #1, mmlline
qiu1:                    mmlline1 = mmlline
                   For j = 50 To 1 Step -1
                   mmlline1 = Replace(Trim(mmlline1), Application.Rept(" ", j), "|")
                   Next
                   a = Split(mmlline1, "|")
                   sig1(g1) = nodebname
                   sig2(g2) = a(0)
                   sig3(g3) = a(1)
                   sig4(g4) = a(2)
                   sig5(g5) = a(3)
                   sig6(g6) = a(4)
                   sig7(g7) = a(5)
                   sig8(g8) = a(6)
                   sig9(g9) = a(7)
                   sig10(g10) = a(8)
                   sig11(g11) = a(10)
                   sig12(g12) = a(11)
                   g1 = g1 + 1
                   g2 = g2 + 1
                   g3 = g3 + 1
                   g4 = g4 + 1
                   g5 = g5 + 1
                   g6 = g6 + 1
                   g7 = g7 + 1
                   g8 = g8 + 1
                   g9 = g9 + 1
                   g10 = g10 + 1
                   g11 = g11 + 1
                   g12 = g12 + 1
                   Line Input #1, mmlline
                   If InStr(mmlline, "结果个数") <= 0 Then
                   GoTo qiu1
                   End If
                End If

             Else
                   sig1(g1) = nodebname
                   sig2(g2) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig3(g3) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig4(g4) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig5(g5) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig6(g6) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig7(g7) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig8(g8) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig9(g9) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig10(g10) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   If Len(mmlline) > 12 Then
                    Line Input #1, mmlline
                   sig11(g11) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig12(g12) = Trim(Split(mmlline, "=")(1))
                   Else
                   sig11(g11) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig12(g12) = Trim(Split(mmlline, "=")(1))
                   End If
                   g1 = g1 + 1
                   g2 = g2 + 1
                   g3 = g3 + 1
                   g4 = g4 + 1
                   g5 = g5 + 1
                   g6 = g6 + 1
                   g7 = g7 + 1
                   g8 = g8 + 1
                   g9 = g9 + 1
                   g10 = g10 + 1
                   g11 = g11 + 1
                   g12 = g12 + 1
             End If
       End If
   End If

                   If InStr(mmlline, "仍有后续报告输出") > 0 Then
                      For w = 1 To 11
                      Line Input #1, mmlline
                      Next
                      gg = InStr(mmlline, "相关DSP号")
                       Line Input #1, mmlline
                       Line Input #1, mmlline
                    If gg > 0 Then
                     GoTo qiu
                    Else
                     GoTo qiu1
                    End If
                   End If
qqq:                Loop
                Close #1
 End If
     MyName = Dir
Loop
'//////////////////////////////////////////////////////////////////
  txtline = "站名" & "|" & "小区设备ID" & "|" & "载波资源ID" & "|" & "相关BBP槽号" & "|" & "相关DSP号" & "|" & "相关DSPGRP号" & "|" & "频点" & "|" & "载波服务类型" & "|" & "载波状态" & "|" & "载波类型" & "|" & "操作态" & "|" & "可用态" & vbCrLf
For i = 1 To g1
  txtline = txtline & sig1(i) & "|" & sig2(i) & "|" & sig3(i) & "|" & sig4(i) & "|" & sig5(i) & "|" & sig6(i) & "|" & sig7(i) & "|" & sig8(i) & "|" & sig9(i) & "|" & sig10(i) & "|" & sig11(i) & "|" & sig12(i) & vbCrLf
Next

Dim fso1 As Object
        Set fso1 = CreateObject("Scripting.FileSystemObject")
        Set Ts1 = fso1.CreateTextFile("D:\Q结果.txt")
        Ts1.WriteLine txtline
        Ts1.Close


    Workbooks.OpenText FileName:="D:\Q结果.txt", Origin:=936 _
        , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
        ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs FileName:=mypathout + "基站载波统计结果" & Date & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Kill "D:\q结果.txt"

 MsgBox "统计完成！结果保存在：" & mypathout & "基站载波统计结果" & Date & ".xlsx"
End Sub
2013-05-22 14:37
qbh555
Rank: 1
等　级：新手上路
帖　子：7
专家分：0
注　册：2012-12-26
第 9 楼
收藏
 得分:0 
回复 2楼 bczgvip
程序代码：
以下可以处理，就是速度非常的慢，请教快速处理方法。


Private Sub Command1_Click()
'/////////////////////////////////////////////////////////////////////////
  MsgBox "选择输出路径"
   With Application.FileDialog(msoFileDialogFolderPicker)
    If (.Show = -1) Then
        mypathout = .SelectedItems(1)
        End If
    End With
    If mypathout = "" Then
        Exit Sub
    End If
   mypathout = mypathout + "\"
'///////////////////////////////////////////////////////////////////////////
  Dim sig1(65535) As Variant
  Dim sig2(65535) As Variant
  Dim sig3(65535) As Variant
  Dim sig4(65535) As Variant
  Dim sig5(65535) As Variant
  Dim sig6(65535) As Variant
  Dim sig7(65535) As Variant
  Dim sig8(65535) As Variant
  Dim sig9(65535) As Variant
  Dim sig10(65535) As Variant
  Dim sig11(65535) As Variant
  Dim sig12(65535) As Variant
  g1 = 1
  g2 = 1
  g3 = 1
  g4 = 1
  g5 = 1
  g6 = 1
  g7 = 1
  g8 = 1
  g9 = 1
  g10 = 1
  g11 = 1
  g12 = 1
  MsgBox "选择统计文件"
  With Application.FileDialog(msoFileDialogFolderPicker)
           If (.Show = -1) Then
            MyPath = .SelectedItems(1)
           End If
     End With
    If MyPath = "" Then
        Exit Sub
    End If
   MyPath = MyPath + "\"
   MyName = Dir(MyPath, vbDirectory)
Do While MyName <> ""

 If MyName <> "." And MyName <> ".." Then
     Open MyPath + MyName For Input As #1
         Do While Not EOF(1)
                   Line Input #1, mmlline
            If InStr(mmlline, "网元") > 0 Then
                   Line Input #1, mmlline
                   If InStr(mmlline, "CD-RNC") Then
                   GoTo qqq
                   End If
                   nodebname = Trim(Mid(mmlline, 2, 50))
                   Line Input #1, mmlline
                   Line Input #1, mmlline
              If InStr(mmlline, "网元断连") <= 0 And InStr(mmlline, "网元响应MML命令超时") <= 0 And InStr(mmlline, "没有权限") <= 0 Then
                   Line Input #1, mmlline
                   Line Input #1, mmlline
                   Line Input #1, mmlline
                   Line Input #1, mmlline
                   Line Input #1, mmlline
                              If InStr(mmlline, "没有查到相应的结果") > 0 Then
                              GoTo qqq
                              End If
                   Line Input #1, mmlline
                   Line Input #1, mmlline
               If InStr(mmlline, "=") <= 0 Then
                 If InStr(mmlline, "相关DSP号") > 0 Then
                   Line Input #1, mmlline
                   Line Input #1, mmlline
qiu:                    mmlline1 = mmlline
                             For j = 50 To 1 Step -1
                             mmlline1 = Replace(Trim(mmlline1), Application.Rept(" ", j), "|")
                             Next
                   a = Split(mmlline1, "|")
                   sig1(g1) = nodebname
                   sig2(g2) = a(0)
                   sig3(g3) = a(1)
                   sig4(g4) = a(2)
                   sig5(g5) = a(3)
                   sig6(g6) = a(4)
                   sig7(g7) = a(5)
                   sig8(g8) = a(6)
                   sig9(g9) = a(7)
                   sig10(g10) = a(8)
                   sig11(g11) = a(10)
                   sig12(g12) = a(11)
                   g1 = g1 + 1
                   g2 = g2 + 1
                   g3 = g3 + 1
                   g4 = g4 + 1
                   g5 = g5 + 1
                   g6 = g6 + 1
                   g7 = g7 + 1
                   g8 = g8 + 1
                   g9 = g9 + 1
                   g10 = g10 + 1
                   g11 = g11 + 1
                   g12 = g12 + 1
                   Line Input #1, mmlline
                                If InStr(mmlline, "结果个数") <= 0 Then
                                GoTo qiu
                                End If
              Else
                   Line Input #1, mmlline
                   Line Input #1, mmlline
qiu1:                    mmlline1 = mmlline
                   For j = 50 To 1 Step -1
                   mmlline1 = Replace(Trim(mmlline1), Application.Rept(" ", j), "|")
                   Next
                   a = Split(mmlline1, "|")
                   sig1(g1) = nodebname
                   sig2(g2) = a(0)
                   sig3(g3) = a(1)
                   sig4(g4) = a(2)
                   sig5(g5) = a(3)
                   sig6(g6) = a(4)
                   sig7(g7) = a(5)
                   sig8(g8) = a(6)
                   sig9(g9) = a(7)
                   sig10(g10) = a(8)
                   sig11(g11) = a(10)
                   sig12(g12) = a(11)
                   g1 = g1 + 1
                   g2 = g2 + 1
                   g3 = g3 + 1
                   g4 = g4 + 1
                   g5 = g5 + 1
                   g6 = g6 + 1
                   g7 = g7 + 1
                   g8 = g8 + 1
                   g9 = g9 + 1
                   g10 = g10 + 1
                   g11 = g11 + 1
                   g12 = g12 + 1
                   Line Input #1, mmlline
                   If InStr(mmlline, "结果个数") <= 0 Then
                   GoTo qiu1
                   End If
                End If

             Else
                   sig1(g1) = nodebname
                   sig2(g2) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig3(g3) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig4(g4) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig5(g5) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig6(g6) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig7(g7) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig8(g8) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig9(g9) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig10(g10) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   If Len(mmlline) > 12 Then
                    Line Input #1, mmlline
                   sig11(g11) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig12(g12) = Trim(Split(mmlline, "=")(1))
                   Else
                   sig11(g11) = Trim(Split(mmlline, "=")(1))
                    Line Input #1, mmlline
                   sig12(g12) = Trim(Split(mmlline, "=")(1))
                   End If
                   g1 = g1 + 1
                   g2 = g2 + 1
                   g3 = g3 + 1
                   g4 = g4 + 1
                   g5 = g5 + 1
                   g6 = g6 + 1
                   g7 = g7 + 1
                   g8 = g8 + 1
                   g9 = g9 + 1
                   g10 = g10 + 1
                   g11 = g11 + 1
                   g12 = g12 + 1
             End If
       End If
   End If

                   If InStr(mmlline, "仍有后续报告输出") > 0 Then
                      For w = 1 To 11
                      Line Input #1, mmlline
                      Next
                      gg = InStr(mmlline, "相关DSP号")
                       Line Input #1, mmlline
                       Line Input #1, mmlline
                    If gg > 0 Then
                     GoTo qiu
                    Else
                     GoTo qiu1
                    End If
                   End If
qqq:                Loop
                Close #1

 End If
     MyName = Dir
Loop
'//////////////////////////////////////////////////////////////////
  txtline = "站名" & "|" & "小区设备ID" & "|" & "载波资源ID" & "|" & "相关BBP槽号" & "|" & "相关DSP号" & "|" & "相关DSPGRP号" & "|" & "频点" & "|" & "载波服务类型" & "|" & "载波状态" & "|" & "载波类型" & "|" & "操作态" & "|" & "可用态" & vbCrLf
For i = 1 To g1
  txtline = txtline & sig1(i) & "|" & sig2(i) & "|" & sig3(i) & "|" & sig4(i) & "|" & sig5(i) & "|" & sig6(i) & "|" & sig7(i) & "|" & sig8(i) & "|" & sig9(i) & "|" & sig10(i) & "|" & sig11(i) & "|" & sig12(i) & vbCrLf
Next

Dim fso1 As Object
        Set fso1 = CreateObject("Scripting.FileSystemObject")
        Set Ts1 = fso1.CreateTextFile("D:\Q结果.txt")
        Ts1.WriteLine txtline
        Ts1.Close


    Workbooks.OpenText FileName:="D:\Q结果.txt", Origin:=936 _
        , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
        ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs FileName:=mypathout + "基站载波统计结果" & Date & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Kill "D:\q结果.txt"


 MsgBox "统计完成！结果保存在：" & mypathout & "基站载波统计结果" & Date & ".xlsx"
End Sub
2013-05-22 14:40
风吹过b
Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20
等　级：贵宾
威　望：364
帖　子：4947
专家分：30084
注　册：2008-10-15
第 10 楼
收藏
 得分:0 
txtline = "站名" & "|" & "小区设备ID" & "|" & "载波资源ID" & "|" & "相关BBP槽号" & "|" & "相关DSP号" & "|" & "相关DSPGRP号" & "|" & "频点" & "|" & "载波服务类型" & "|" & "载波状态" & "|" & "载波类型" & "|" & "操作态" & "|" & "可用态" & vbCrLf
For i = 1 To g1
  txtline = txtline & sig1(i) & "|" & sig2(i) & "|" & sig3(i) & "|" & sig4(i) & "|" & sig5(i) & "|" & sig6(i) & "|" & sig7(i) & "|" & sig8(i) & "|" & sig9(i) & "|" & sig10(i) & "|" & sig11(i) & "|" & sig12(i) & vbCrLf
Next
这是以 | 为分隔符， 当这个 分隔符为 逗号时，

并且文件的缀名 为  CSV 时，也就是 执行完这段时， Q结果.txt 改为 Q结果.csv
Dim fso1 As Object
        Set fso1 = CreateObject("Scripting.FileSystemObject")
        Set Ts1 = fso1.CreateTextFile("D:\Q结果.txt")
        Ts1.WriteLine txtline
        Ts1.Close

生成的就是 CSV 文件。
你试试。

授人于鱼，不如授人于渔
早已停用QQ了
2013-05-22 15:47
快速回复：请教：vb如何快速处理较大的txt文件？
数据加载中...
 
　　　



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有，并保留所有权利。
Powered by Discuz, Processed in 0.024280 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved