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

问题描述

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

  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）
 
'使用分号作为分隔符解析它
范围（Range（A1），Range（A1）End（xlDown ））TextToColumns _ 
 DataType：= xlDelimited，_ 
 ConsecutiveDelimiter：= False，Tab：= False，_ 
分号：= True，逗号：= False， ：= False'
 
'目录中的下一个文件
 MyFile = Dir（）
循环
 
结束子
  

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

所以对于csv文件像

  test; test，test 
  

我期望输出

 测试测试，测试
  pre> 
 
 
但我实际上得到了
 测试test 
  
为什么？我的Excel设置有问题吗？
 
 
 
谢谢！
解决方案
 
  Workbooks.Open（MyFile）
  
在Excel中打开文件的那一刻，它以此格式打开，因为它是逗号分隔文件
 
 
 
 > 
 
 
 
当 .TextToColumns 代码运行时，它将替换 B 数据>; 中 c> 。 
 
 
 
尝试此操作
 
 
 
假设您的csv档案如下所示：
 
 
 
  
 
 
 
现在试试这个代码。一旦你了解它的工作原理，只需在代码中适应这一点。我已经评论了代码，以便您不会有任何问题的理解。
  Sub Sample（）
 Dim wb作为工作簿，ws作为工作表
 Dim MyData As String，strData（）As String 
 Dim myFile As String 
 Dim lRow As Long 
 
'~~>将它替换为您的实际文件
 myFile =C：\Users\Siddharth\Desktop\test.csv
 
'~~>在内存中打开文本文件并一次读取
打开myFile对于二进制为＃1 
 MyData = Space $（LOF（1））
获取＃1，，MyData 
关闭＃1 
 strData（）= Split（MyData，vbCrLf）
 
'~~>添加新工作簿
设置wb = Workbooks.Add 
'~~>使用第一张表
设置ws = wb.Sheets（1）
 
使用ws 
'~~>将数组复制到工作表
 .Range（A1）。Resize（UBound（strData），1）.Value = strData 
 
'~~>获取数据的最后一行
 lRow = .Range（A& .Rows.Count）.End（xlUp）.Row 
 
'〜〜现在使用文本到列
 .Range（A1：A& lRow）.TextToColumns DataType：= xlDelimited，_ 
 ConsecutiveDelimiter：= False，_ 
 Tab：= False，_ 
分号：= True，_ 
 Comma：= False，_ 
空格：= False，_ 
其他：= False'
结束于
 
  
这是你得到的
 
 
 
  
 
 
 
  EDIT ：另一个选项是重命名csv文件，然后按照打开以管道字符|分隔的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
But strangely, it also uses comma as a separator as well. I can see that if I debug the TextToColumns line.

So for a csv file like
test;test,test
I would expect an output of
test    test,test
But I actually get
test    test
Why? Is there something wrong with my Excel settings?

Thanks!
解决方案
The problem is with this line
 Workbooks.Open (MyFile)
The moment you open the file in Excel, it is opened in this format as it is a Comma Delimited File



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

Try this

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
And this is what you get



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屋！