TextToColumns function uses wrong delimiter

Question

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!

Answer 1

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