Workbooks.OpenText无法正确解析CSV文件Excel 2016 [英] Workbooks.OpenText not parsing csv files properly Excel 2016

问题描述

我很确定这可以在早期版本的Excel中正常工作

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

测试文件:

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

日期和时间之间的分隔符是一个空格(ASCII码32)

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

• 如果文件另存为.txt文件，则OpenText方法将正确解析.

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

如果文件另存为.csv文件，则OpenText方法似乎根本不起作用

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

如果用逗号替换空格，并且文件另存为.csv文件，则OpenText方法会将行分为两列，但不能正确解释日期字符串.

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.

我的Windows区域设置是mdy，我的Excel版本是2016

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

---

推荐答案

感谢所有人的建议.在可能的解决方案中，出于我的目的，我决定从文件中删除*.csv后缀.这有效并且可以适应. QueryTable方法以及Axel发布的警告也可以使用.

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

这篇关于Workbooks.OpenText无法正确解析CSV文件Excel 2016的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！