使用TextFileColumnDataTypes为每列打开具有正确数据格式的CSV文件? [英] Open CSV file with correct data format for each column using TextFileColumnDataTypes?
问题描述
我正在使用下面的VBA代码在Excel中打开一个csv文件(代码模拟Data \Text到Columns - 命令)。在代码中,需要为属性 TextFileColumnDataTypes 指定一个数组,对于csv文件中的每一列都指定数据格式(2 =文本格式)。
但是,由于我不知道csv文件将有多少列,我想为所有列指定格式2(=文本格式) csv文件。现在的问题是我只能为固定数量的列指定数据格式(在下面的示例中为3列)。
非常感谢任何解决问题的帮助:)
========= b b b b == == == == b b b b b b b
以下是我使用的完整代码:
使用ThisWorkbook.Worksheets(1).QueryTables。添加(Connection:= _
TEXT; C:\test.csv,Destination _
:= ThisWorkbook.Worksheets(1).Range($ A $ 1))
.name =从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
.Te xtFileTextQualifier = 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
以下是一种从封闭CSV中查找列数而不在Excel中打开的方法。 / p>
我假设如下。
1)您正在打开逗号分隔文件。如果没有,那么你将不得不适当地修改代码
2) CSV中的第1行有标题(至少有1个标题在任何栏)
尝试这个(我测试了,但如果你有任何错误让我们知道:) / p>
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
'~~>打开一个文本文件
打开ExlCsv为二进制为#1
MyData =空格$(LOF(1))
获取#1,,MyData
关闭#1
strData()= Split(MyData,vbCrLf)
'~~>检查任何空标题,并将,,替换为,
Do While InStr(1,strData(0),,,)> 0
strData(0)=替换(strData(0),,,,,)
循环
'~~>拆分头来查找列数
TempAr()= Split(strData(0),,)
'~~>创建我们的数组为TEXT
ReDim ArCol(1到UBound(TempAr))
对于i = 1到UBound(TempAr)
ArCol(i)= 2
下一个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
.TextFi leTabDelimiter = 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 =@
使用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 = Array(2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:= False
End with
End Sub
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).
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).
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$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
Here is one way to find the number of columns from a closed CSV without opening it in Excel.
I am assuming the following.
1) You are opening a Comma Separated File. If not then you will have to amend the code appropriately
2) Row 1 in the CSV has Headers (At least 1 header in any of the Column)
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$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
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$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 Sub
这篇关于使用TextFileColumnDataTypes为每列打开具有正确数据格式的CSV文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!