TextToColumns function uses an invalid delimiter

Question

I am trying to open all csv files (semicolon delimited) 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 enough, it also uses a comma as a separator. I see that if I debug a TextToColumns string.

So for a csv file for example

 test;test,test 

I would expect a way out

 test test,test 

But I really get

 test test 

Why? Is there something wrong with my settings in Excel?

Thanks!

Answer 1

Problem with this line

  Workbooks.Open (MyFile) 

The moment you open the file in Excel, it opens in this format, since it is a comma-delimited file

And then, when the .TextToColumns code is .TextToColumns , it replaces the data of column B “test” that .TextToColumns after ; in column A

try it

Let's say your csv file looks like this:

Now try this code. Once you understand how this works, just adapt it in your code. I commented on the code so that you don't 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 here is what you get

EDIT . Another option you have is to rename the csv file and then open it as suggested in Open the csv file, limited by the pipe symbol "|" or not a common delimiter