TextToColumns函数使用错误的分隔符 [英] TextToColumns function uses wrong delimiter

问题描述

我正在尝试在目录中打开所有csv（分隔符是分号）文件，这是我认为应该工作的代码：

I am trying to open all csv (separator is semicolon) files in a directory and this is the code that I think should work:

Sub test()
Dim MyFile As String
Dim MyDir As String

MyDir = Application.ActiveWorkbook.Path
MyFile = Dir(MyDir & "\" & "*.csv")
'set current directoy
ChDir MyDir

Application.ScreenUpdating = 0
Application.DisplayAlerts = 0


Do While MyFile <> ""
    Workbooks.Open (MyFile)

    'Parse it using semicolon as delimiters
    Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
        DataType:=xlDelimited, _
         ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False '

    'next file in directory
    MyFile = Dir()
Loop

End Sub

但奇怪的是，它也使用逗号作为分隔符。我可以看到，如果我调试 TextToColumns 行。

But strangely, it also uses comma as a separator as well. I can see that if I debug the TextToColumns line.

所以对于csv文件，如

So for a csv file like

test;test,test

我希望输出

test    test,test

但我实际上得到

test    test

为什么？我的Excel设置有问题吗？

Why? Is there something wrong with my Excel settings?

谢谢！

推荐答案

问题在于这一行

 Workbooks.Open (MyFile)

当您在Excel中打开文件时，它以这种格式打开，因为它是逗号分隔的文件

The moment you open the file in Excel, it is opened in this format as it is a Comma Delimited File

然后当 .TextToColumns 代码运行时，将使用 B 数据> 栏 A 。

And then when the .TextToColumns code runs it replaces Column B data with the "test" which is after ; in Column A.

尝试这个

让我们说你的csv文件看起来像这样

Let's say your csv file looks like this

现在尝试这个代码。一旦了解了它的工作原理，只需在你的代码中进行调整。我已经评论过代码，所以你不会有一个理解它的问题。

Now try this code. Once you understand how it works, simply adapt this in your code. I have commented the code so that you will not have a problem understanding it.

Sub Sample()
    Dim wb As Workbook, ws As Worksheet
    Dim MyData As String, strData() As String
    Dim myFile As String
    Dim lRow As Long

    '~~> Replace this with your actual file
    myFile = "C:\Users\Siddharth\Desktop\test.csv"

    '~~> open text file in memory and read it in one go
    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

    '~~> Add a new workbook
    Set wb = Workbooks.Add
    '~~> Work with the 1st sheet
    Set ws = wb.Sheets(1)

    With ws
        '~~> Copy the array to worksheet
        .Range("A1").Resize(UBound(strData), 1).Value = strData

        '~~> get the last row of the data
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Use text To columns now
        .Range("A1:A" & lRow).TextToColumns DataType:=xlDelimited, _
                                            ConsecutiveDelimiter:=False, _
                                            Tab:=False, _
                                            Semicolon:=True, _
                                            Comma:=False, _
                                            Space:=False, _
                                            Other:=False '
    End With
End Sub

这是你得到的

编辑：另一个选项是重命名csv文件，然后按照打开由管道字符|分隔的csv文件或不常见的定界符

EDIT: The other option that you have is to rename the csv file and then open it as suggested in Open csv file delimited by pipe character "|" or not common delimiter

这篇关于TextToColumns函数使用错误的分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！