VBA读取不带后缀名文本文件的方法(解决Unix文本文件在Windows下变成一行的读取)

南太湖小蚂蚁

已于 2024-07-29 15:22:26 修改

阅读量393

点赞数 4

分类专栏：办公自动化文章标签： excel vba

于 2024-07-29 15:21:10 首次发布

本文链接：https://blog.csdn.net/weixin_42255757/article/details/140772007

版权

办公自动化专栏收录该内容

1 篇文章 0 订阅

订阅专栏

VBA在操作Excel等Office软件方面有天然的优势，虽说现在Python的Pandas，openpyxl和Java的poi包都可以处理Excel文件，但有两个问题：首先，目标电脑上必须先按照Java或python环境，如果在一些机构内部处于安全原因不允许自主安装Java或python等环境则会收到较大的制约，更严格的情况是可能会禁用jar文件或exe文件；其次，Java和Python程序由于需要引入第三方包，因此打包后不可避免的把第三方包也会一起打包进去，我做过一个测试，同样的功能，用Python打包约10M，Java如果用poi则是20M，如果用jxl等工具则大小在1M左右，但是如果用VBA实现，大约39K，且运行速度也很快，所以说，虽然Java和python已经可以完美解决对于Excel等Office的处理，但是VBA同样有其自身的价值。

今天我就来讨论一个问题，VBA读取不带后缀的文本文件方法。因为我自己遇到一个需求，遇到读取一些文本文件：

文件内容是这样的：

按照网上的说法，这种文本文件读取的方法是运行如下的语句：

Open SelectedFile For Input As #1
 
Do While Not EOF(1)
    Line Input #1, s
    MsgBox s
    Debug.Print s
Loop

那么我们可以看看，这样写的话，得到的是什么结果：

可以看到结果并没有一行行输出，而是作为一整行输出的，后来我查了文档发现原因在于这文件是在Unix下创建的，Unix/Mac系统下的文件在Windows里打开的话，所有文字会变成一行，如果是在windows下创建的文件则不会遇到该问题，即便同样是不带后缀名的纯文本文件。在记事本右下角可以看到差别。

而正常在Windows中编辑的文本文件是这样的：

所以遇到这个问题后，我们之前的那种方法就不起作用了，所以需要对字符串进行特殊处理，我使用的方法是用Split方法进行字符串分割，因为虽然Unix下把字符串变成了一行，但是换行符还是存在的，在vba中，换行符用Chr(10)表示。因为要导入到Excel中，所以我首先要把Excel工作表中原来存在的数据清空，遍历当前工作表的行和列，并把单元格的值设置为空。之后打开文件选择器，可以允许多个文件选择，对每个选择的文件进行遍历。获取到文件内容，然后使用 varSplit = Split(s, Chr(10)) 对文件内容进行分隔。得到每一行的内容，之后再根据“|”对每行中的具体值进行分隔并获取，填入到相应的单元格中去。我这里设置一个新的变量Count是因为可能读入多个文件，Count就是当前读取了n个文件后的总行数，下一个文件的内容要从count+1行开始写入了，并且我这里第一行不读取，因为Excel工作表中已经有标题行了。5+k是因为我的内容从第五列往后填入的。完整代码如下：

Sub ImportData()

    ' 清理
    Dim c, r As Integer
    ' 获取最大行列数
    c = Worksheets(1).UsedRange.Columns.count
    r = Worksheets(1).UsedRange.Rows.count
    For i = 2 To 50
        For j = 5 To c
            Cells(i, j) = ""
        Next
    Next

    '读取文本文件
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .AllowMultiSelect = True
        
        ' 显示文件对话框
        If .Show = -1 Then
            Dim count As Integer
            count = 0
            For i = 1 To .SelectedItems.count
                SelectedFile = .SelectedItems(i)
                fn = FreeFile()
                Open SelectedFile For Input As #fn
                Do While Not EOF(fn)
                    Line Input #fn, s
                    MsgBox s
                    Debug.Print s
                Loop
                varSplit = Split(s, Chr(10))
                Total = UBound(varSplit)
                For j = 1 To Total
                    MsgBox (varSplit(j))

                    Dim text As String
                    text = varSplit(j)
                    If Len(text) > 0 Then
                        '继续分割text
                        textSplit = Split(text, "|")
                        For k = 0 To UBound(textSplit)
                            t = textSplit(k)
                            If k = 9 Then
                                Cells(count + j + 1, 5 + k).NumberFormat = "0"
                                Cells(count + j + 1, 5 + k) = t
                            Else
                                Cells(count + j + 1, 5 + k).NumberFormat = "@"
                                Cells(count + j + 1, 5 + k) = t
                            End If
                        Next
                    End If
                Next
                Close #fn
                count = count + Total - 1
            Next
        End If
    End With
End Sub

可以看到现在已经实现了逐行读取，并且数据被完整的填入了Excel工作表中。