Workbooks.OpenText not parsing csv files properly Excel 2016

Question

I'm pretty sure this worked properly in previous versions of Excel

Test File:

d/mm/yyyy hh:mm:ss
5/12/1999 6:01:12
30/11/2001 5:00:00

And the delimiter between the date and the time is a Space (ASCII code 32)

• If the file is saved as a .txt file, the OpenText method parses properly.

• If the file is saved as a .csv file, the OpenText method doesn't seem to work at all

• If the spaces are replaced with commas, and the file is saved as a .csv file, the OpenText method will split the lines into two columns, but will not properly interpret the date string.

My Windows Regional Settings are mdy and my Excel version is 2016

---

Option Explicit
Sub foo()
    Dim WB As Workbook
    Dim sFN As String
    Dim FD As FileDialog

Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Filters.Add "Text or CSV", "*.txt, *.csv", 1
    .Show
    sFN = .SelectedItems(1)
End With

Workbooks.OpenText Filename:=sFN, DataType:=xlDelimited, origin:=437, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
        FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlGeneralFormat))


Set WB = ActiveWorkbook

End Sub

---

Answer 1

Thanks to all for suggestions. Amongst the possible solutions, I decided, for my purposes, to remove the *.csv suffix from the file. This works and can be adaptable. QueryTable method would also work, along with the caveats posted by Axel.

Here is code that works for my method, if anyone is interested.

---

Option Explicit
Sub foo()
    Dim WB As Workbook, wbCSV As Workbook, swbCSV As String
    Dim sFN As String, sCopyFN
    Dim FD As FileDialog

Set WB = ThisWorkbook
Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Filters.Add "Text or CSV", "*.txt, *.csv", 1
    .Show
    sFN = .SelectedItems(1)
End With

'If CSV, remove suffix
sCopyFN = ""
If sFN Like "*.csv" Then
    sCopyFN = Left(sFN, Len(sFN) - 4)
    FileCopy sFN, sCopyFN
    sFN = sCopyFN
End If

Workbooks.OpenText Filename:=sFN, DataType:=xlDelimited, origin:=437, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
        FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlGeneralFormat))

Set wbCSV = ActiveWorkbook

'Get path as string since it will not be available after closing the file
swbCSV = wbCSV.FullName

'Move the data into this workbook
Dim rCopy As Range, rDest As Range

With WB.Worksheets("sheet1")
    Set rDest = .Cells(.Rows.Count, 1).End(xlUp)
End With
Set rCopy = wbCSV.Sheets(1).UsedRange

rCopy.Copy rDest

'must close the file before deleting it
wbCSV.Close False
Kill swbCSV

End Sub

---

Answer 2

It seems like this might be the same issue addressed in this thread:

Opening CSV files in Excel 2016

CSV files are Character Separated Value files, not Comma separated. For more than half the world the separator character is semicolon (;), not a comma (,)

Excel 2016 properly respects your Windows regional settings, and uses the specified "List separator" character

One solution is to change your regional settings for the "List separator" attribute to the character you want Excel to default to using, e.g. a comma (,)

This can be changed in:

Control Panel / Region / Additional Settings / List separator:

Answer 3

CSV and Text are really not the same for Excel. Not only that the delimiter settings are very special for CSV and are not setable using a parameter in Workbooks.OpenText. Also other parameters like field types (FieldInfo) will also not be respected while opening CSV files. And also the unicode handling is a very special case for CSV and is signly different from Text.

You could try using QueryTables like so:

Sub foo1()
    Dim WB As Workbook
    Dim sFN As String
    Dim FD As FileDialog

Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Filters.Add "Text or CSV", "*.txt, *.csv", 1
    .Show
    sFN = .SelectedItems(1)
End With

Set WB = Workbooks.Add

With WB.Worksheets(1).QueryTables.Add(Connection:= _
    "TEXT;" & sFN & "", Destination:=Range("$A$1"))
    .Name = "test"
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = True
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = True
    .TextFileColumnDataTypes = Array(xlDMYFormat, xlGeneralFormat)
    .Refresh BackgroundQuery:=False
End With

End Sub

But using QueryTables of course you must be careful not adding them multiple times without necessary but refreshing them instead or first deleting them and then adding them again.

Answer 4

Consolidating my experimentation and Axel Richter's answer https://stackoverflow.com/a/42198852/2026637 with dwirony's answer to a related question https://stackoverflow.com/a/55166268/2026637 :

1. Import of a CSV file having a .csv extension by OpenText or by Open treats all dates as MDY (American) format.
2. OpenText ignores all format options with a CSV file having a .csv extension.
3. With a CSV file having a .txt extension and using the xlDelimited option, OpenText takes account of the FileInfo parameter, but the column numbers are ignored, so an array element must be present for each column starting from 1 until all columns with non-default formats are described.

It's frustrating that the user interface works correctly (including defaulting to the regional date format), but the VBA interface has these problems.

The problems are present in Excel 2013 as well as Excel 2016.