Search code examples
excelvbacsv

Avoid data type selection in VBA when building using QueryTables.Add from .CSV file

I am struggling to correctly read the CSV file. In the file I have values that look like this:

21-01
21-02
21-03
21-04
...

When I try to use the QueryTables.Add function (code shown below), I get this output:

21.01
21.02
21.03
21.04
21.05
21.06
21.07
21.08
21.09
21.10
21.11
21.12
21-13
21-14
...

The data shown with "." in them are always formatted as own formatting, though it thinks it is a date in the form of "21.1.2024". When I format them as text it shows the dates numeric values. When Excel runs out of month it formats other cells correctly, until it finds another set of values that look like date. Cells with "-" are formatted by excel as "General" formatting, those with date as my own formatting.

When I use the build in function for creating the table from .CSV, I get the option of "Data type detection" to "Do not detect data types" and that does exactly what I need when done manually. I need to emulate this function in my automation for the VBA. Is that possible? If so, how?

Sub Data_Import()

Dim Exp_File_Name As String
'...

Exp_File_Name = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")

With ThisWorkbook.Sheets(2).QueryTables.Add(Connection:="TEXT;" & Exp_File_Name, _
                            Destination:=ActiveWorkbook.Sheets(2).Range("A1"))
         .TextFileParseType = xlDelimited
         .TextFileSemicolonDelimiter = True
         .Refresh
    End With

'...

End Sub

I tried to parse it using something similar to this:


 .TextFileColumnDataTypes = _ 
 Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)

But it did not help. It seems that the moment Excel reads the files it immediately replaces "-" with ".".

Here is the CSV that I am trying to parse:

[MAIN_YN];[ACTIVE_YN];[ARCHIVED_YN];[CAN_ADD_TO_BASKET_YN];[CODE]
;1;0;1;21-01
;1;0;1;21-02
;1;0;1;21-03
;1;0;1;21-04
;1;0;1;21-05
;1;0;1;21-06
;1;0;1;21-07
;1;0;1;21-08
;1;0;1;21-09
;1;0;1;21-10
;1;0;1;21-11
;1;0;1;21-12
;1;0;1;21-13
;1;0;1;21-14
;1;0;1;21-15
;1;0;1;21-16
;1;0;1;21-17
;1;0;1;21-18
;1;0;1;21-19
;1;0;1;21-20
;1;0;1;21-21
;1;0;1;21-22
;1;0;1;21-23
;1;0;1;21-24
;1;0;1;21-25
;1;0;1;21-26
;1;0;1;21-27
;1;0;1;21-28
;1;0;1;21-29
;1;0;1;21-30
;1;0;1;21-31
;1;0;1;21-32
;1;0;1;21-33
;1;0;1;21-35
;1;0;1;21-36
;1;0;1;21-37
;1;0;1;21-38
;1;0;1;21-39
;1;0;1;21-40
;1;0;1;21-41
;1;0;1;21-42
;1;0;1;21-43
;1;0;1;21-44
;1;0;1;21-45
;1;0;1;21-46
;1;0;1;21-47
;1;0;1;21-48
;1;0;1;21-49
;1;0;1;21-50
;1;0;1;21-51
;1;0;1;21-52
;1;0;1;21-53
;1;0;1;21-54
;1;0;1;21-55
;1;0;1;21-56
;1;0;1;21-57
;1;0;1;21-58
;1;0;1;22-01
;1;0;1;22-02
;1;0;1;22-03
;1;0;1;22-04
;1;0;1;22-05
;1;0;1;22-06
;1;0;1;22-07
;1;0;1;22-08
;1;0;1;22-09
;1;0;1;22-10
;1;0;1;22-11
;1;0;1;22-12
;1;0;1;22-13
;1;0;1;22-14
;1;0;1;22-15
;1;0;1;22-16
;1;0;1;22-17
;1;0;1;22-18
;1;0;1;22-19
;1;0;1;22-20
;1;0;1;22-21
;1;0;1;22-22
;1;0;1;22-23
;1;0;1;22-24
;1;0;1;22-25
;1;0;1;22-26
;1;0;1;22-27
;1;0;1;22-28
;1;0;1;22-29
;1;0;1;22-30
;1;0;1;22-31
;1;0;1;22-32
;1;0;1;22-33
;1;0;1;22-34
;1;0;1;22-35
;1;0;1;22-36
;1;0;1;22-37
;1;0;1;22-38
;1;0;1;22-39
;1;0;1;22-40
;1;0;1;22-41
;1;0;1;22-42
;1;0;1;22-43
;1;0;1;22-44
;1;0;1;22-45
;1;0;1;22-46
;1;0;1;22-47
;1;0;1;22-48
;1;0;1;22-49
;1;0;1;22-50
;1;0;1;22-51
;1;0;1;22-52
;1;0;1;22-53
;1;0;1;22-54
;1;0;1;22-55
;1;0;1;22-56
;1;0;1;22-57
;1;0;1;22-58
;1;0;1;22-59
;1;0;1;22-60
;1;0;1;22-61
;1;0;1;22-62
;1;0;1;22-63
;1;0;1;22-64
;1;0;1;22-65
;1;0;1;22-67
;1;0;1;22-68
;1;0;1;22-69
;1;0;1;22-70
;1;0;1;22-71
;1;0;1;22-72
;1;0;1;22-73
;1;0;1;22-74
;1;0;1;22-75
;1;0;1;22-76
;1;0;1;22-77
;1;0;1;22-78
;1;0;1;22-79
;1;0;1;22-80
;1;0;1;22-81
;1;0;1;22-82
;1;0;1;22-83
;1;0;1;22-84
;1;0;1;22-85
;1;0;1;22-86
;1;0;1;22-87
;1;0;1;22-88
;1;0;1;22-89
;1;0;1;22-90
;1;0;1;22-91
;1;0;1;22-92
;1;0;1;22-93
;1;0;1;22-94
;1;0;1;27-02
;1;0;1;27-03
;1;0;1;27-05
;1;0;1;27-06
;1;0;1;27-09
;1;0;1;27-10
;1;0;1;27-12
;1;0;1;27-13
;1;0;1;27-14
;1;0;1;27-21
;1;0;1;27-22
;1;0;1;27-23
;1;0;1;27-24
;1;0;1;27-25
;1;0;1;27-26
;1;0;1;27-27
;1;0;1;27-28
;1;0;1;27-29
;1;0;1;27-30
;1;0;0;9918995604706
;1;0;1;27-32
;1;0;1;27-33
;1;0;1;27-34
;1;0;1;27-35
;1;0;1;29-10
;1;0;1;29-11
;1;0;1;29-12
;1;0;1;29-13
;1;0;1;29-14
;1;0;1;29-15
;1;0;1;29-16
;1;0;1;29-17
;1;0;1;29-18
;1;0;1;29-19
;1;0;1;29-20
;1;0;1;29-21
;1;0;1;29-22
;1;0;1;29-23
;1;0;1;29-24
;1;0;1;29-25
;1;0;1;29-26
;1;0;1;29-27
;1;0;1;29-28
;1;0;1;29-29
;1;0;1;29-30
;1;0;1;29-31
;1;0;1;29-32
;1;0;1;29-33
;1;0;1;29-34
;1;0;1;29-35
;1;0;1;29-36
;1;0;1;29-37
;1;0;1;29-38
;1;0;1;29-39
;1;0;1;29-40
;1;0;1;29-41
;1;0;1;29-42
;1;0;1;29-43
;1;0;1;48-94
Solution

  • Specifying TextFileColumnDataTypes worked for me: values were imported as-is without any date conversions.

    If you only want to keep all columns imported as text, but you're not sure how many columns you'll be importing, you can provide a large array of xlTextFormat values to TextFileColumnDataTypes and it will ignore any "extra" elements in the array.

    Sub Data_Import()
    Dim Exp_File_Name

    Exp_File_Name = "C:\Temp\info.txt" 'has a single column of values

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Exp_File_Name, _
                            Destination:=[A1])
         .TextFileParseType = xlDelimited
         .TextFileSemicolonDelimiter = True
         .TextFileColumnDataTypes = AllTextFormat()
         .Refresh
    End With
End Sub

'return an array of `xlTextFormat` values
Function AllTextFormat()
    Dim arr(0 To 100), i As Long
    For i = 0 To 100
        arr(i) = xlTextFormat
    Next i
    AllTextFormat = arr
End Function