使用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屋！