使用TextFileColumnDataTypes为每列打开具有正确数据格式的CSV文件？
使用TextFileColumnDataTypes为每列打开具有正确数据格式的CSV文件？
提问于 2012-04-22 15:42:02
我使用下面的VBA-代码在Excel中打开csv-文件(代码模拟数据\文本到列-命令)。在代码中，必须为属性TextFileColumnDataTypes指定一个数组，该数组针对csv-文件中的每一列指定一种数据格式(2 =文本格式)。
但是，由于我不知道csv-文件将有多少列，所以我想为csv-文件中的所有列指定格式2 (= text格式)。现在的问题是，我只能为固定数量的列指定数据格式(在下面的示例中是3列)。
)任何解决这一问题的帮助都受到高度赞赏:)
===============================================
下面是我使用的完整代码：
With ThisWorkbook.Worksheets(1).QueryTables.Add(Connection:= _
"TEXT;C:\test.csv", Destination _
:=ThisWorkbook.Worksheets(1).Range("$A$1"))
.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回答 2
Stack Overflow用户
回答已采纳
发布于 2012-04-22 16:21:45
这里有一种方法可以在不打开Excel的情况下从封闭的CSV中找到列数。
我假设如下。
1)您正在打开逗号分隔的文件。如果没有，则必须适当地修改代码。
2) CSV中的第1行有标头(在任何列中至少有一个标头)
试试这个(我测试了它，但是如果您有任何错误，请告诉我们:)
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$1") _
)
.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编辑
或者，这里有一个简单得多的方法(不知道为什么我以前没有想过.)
Option Explicit
Const ExlCsv As String = "C:\test.csv"
Sub Sample()
ActiveSheet.Cells.NumberFormat = "@"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & ExlCsv, Destination:=Range("$A$1") _
)
.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 SubStack Overflow用户
发布于 2014-11-30 15:12:01
对我来说，最脏的方法是初始化一个比以往任何时候都大得多的数据类型数组。忽略剩余列数据类型。
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/10269366
复制相关文章
相似问题
腾讯云开发者
