TextToColumns函数使用错误的分隔符

本文介绍了TextToColumns函数使用错误的分隔符的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试在目录中打开所有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 代码运行时，将使用 栏 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