vba 使用TextFileColumnDataTypes为每列打开具有正确数据格式的CSV文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10269366/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Open CSV file with correct data format for each column using TextFileColumnDataTypes?
提问by tyrex
I am using the VBA-code below to open a csv-file in Excel (the code simulates the Data\Text to Columns - command). In the code it's necessary to specify an array for the property TextFileColumnDataTypes, which for every column in the csv-file specifies a data format (2 = text format).
我正在使用下面的 VBA 代码在 Excel 中打开一个 csv 文件（该代码模拟了 Data\Text to Columns - 命令）。在代码中，有必要为属性TextFileColumnDataTypes指定一个数组，它为 csv 文件中的每一列指定一个数据格式（2 = 文本格式）。
However, since I don't know how many columns the csv-file will have, I would like to specify the format 2 (= text format) for ALL columns in the csv-file. The problem right now is that I can only specify the data format for a fixed number of columns (in the example below it's 3 columns).
但是，由于我不知道 csv 文件将有多少列，我想为 csv 文件中的所有列指定格式 2（= 文本格式）。现在的问题是我只能为固定数量的列指定数据格式（在下面的示例中为 3 列）。
Any help to solve that problem is highly appreciated :)
任何解决该问题的帮助都受到高度赞赏:)
===============================================
================================================
Here is the full code I am using:
这是我正在使用的完整代码：
With ThisWorkbook.Worksheets(1).QueryTables.Add(Connection:= _
"TEXT;C:\test.csv", Destination _
:=ThisWorkbook.Worksheets(1).Range("$A"))
.name = "Query Table from Csv"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete
End With
采纳答案by Siddharth Rout
Here is one way to find the number of columns from a closed CSV without opening it in Excel.
这是一种无需在 Excel 中打开即可从关闭的 CSV 中查找列数的方法。
I am assuming the following.
我假设如下。
1)You are opening a Comma Separated File. If not then you will have to amend the code appropriately
1)您正在打开一个逗号分隔的文件。如果没有，那么您将不得不适当地修改代码
2)Row 1 in the CSV has Headers (At least 1 header in any of the Column)
2)CSV 中的第 1 行有标题（任何列中至少有 1 个标题）
Try this (I tested it but if you get any error let us know :)
试试这个（我测试过，但如果你有任何错误，请告诉我们：）
Option Explicit
Const ExlCsv As String = "C:\test.csv"
Sub Sample()
Dim MyData As String, strData() As String, TempAr() As String
Dim ArCol() As Long, i As Long
'~~> Open the text file in one go
Open ExlCsv For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
'~~> Check for any empty headers and replace ",," by ","
Do While InStr(1, strData(0), ",,") > 0
strData(0) = Replace(strData(0), ",,", ",")
Loop
'~~> Split the headers to find the number of columns
TempAr() = Split(strData(0), ",")
'~~> Create our Array for TEXT
ReDim ArCol(1 To UBound(TempAr))
For i = 1 To UBound(TempAr)
ArCol(i) = 2
Next i
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & ExlCsv, Destination:=Range("$A") _
)
.Name = "Output"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = ArCol
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
EDIT
编辑
Alternatively, here is a much simpler method (Wondering why didn't I think of it before...)
或者，这是一个更简单的方法（想知道为什么我之前没有想到它......）
Option Explicit
Const ExlCsv As String = "C:\test.csv"
Sub Sample()
ActiveSheet.Cells.NumberFormat = "@"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & ExlCsv, Destination:=Range("$A") _
)
.Name = "Output"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'<~~ This doesn't make any difference anymore
.TextFileColumnDataTypes = Array(2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
回答by Davor Dundovic
The dirty way that works for me is to initialize a much larger data type array than ever needed. Surplus column data types are ignored.
对我有用的肮脏方法是初始化一个比以往任何时候都大得多的数据类型数组。多余的列数据类型将被忽略。
Sub CSV_Import(strFile As String)
Dim ws As Worksheet
Dim colDataTypesArr(1 to 100) As Long
Dim i As Long
Set ws = Sheet1
For i = 1 To UBound(colDataTypesArr)
colDataTypesArr(i) = 2
Next i
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileColumnDataTypes = colDataTypesArr
.Refresh
End With
End Sub
