CODEWITHSUNDEEP
excelvbacsvexcel-2016
Ron Rosenfeld
Ron Rosenfeld

Reputation: 60259

Workbooks.OpenText not parsing csv files properly Excel 2016

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)

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

Upvotes: 2

Views: 15243

Answers (4)

grahamj42
grahamj42

Reputation: 2762

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.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60259

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

Upvotes: 1

Axel Richter
Axel Richter

Reputation: 61900

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.

Upvotes: 0

J. Garth
J. Garth

Reputation: 803

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:

Upvotes: 0

Related Questions