Using a Loop (or similar) to assign FieldInfo when opening TXT in VBA?

[RickyFish89]

I open a lot of csv and txt documents that I don't like excel ruining, so I wrote a macro that will give me a file dialog to select my file, then it will open, split by column, and assign every column as Text rather than General, Date, etc.

the code I'm using to assign Type is 'FieldInfo' as part of Workbooks.OpenText. In this, you use 'Array(X, Y)' where X is the column number and Y is the type. The good news is that you can go out as far as you want and even if the text file does not contain that many columns it won't error out, so I would like to have an 'Array(x,y)' for a very large number of columns, just to be sure. The documents I work with can be anywhere from a couple dozen columns to literally hundreds.

So, the question- is it possible to use a for loop or something similar to fix this array? or is there another option within Workbooks.OpenText that would just default ALL columns to type 'Text' (2)?

here is my current code:

Sub OpenCSVTXT()
Dim strFilePath As String, strFinalPath As String

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Text Files", "*.prn; *.txt; *.csv", 1
        .Show
        strFilePath = .SelectedItems.Item(1)
    End With
    
    If strFilePath = "" Then
        MsgBox ("File not found or operation cancelled, aborting...")
        Exit Sub
    End If
    
    If Right(strFilePath, 4) = ".csv" Then
        strFinalPath = Left(strFilePath, Len(strFilePath) - 4) & ".txt"
        FileCopy strFilePath, strFinalPath
    Else
        strFinalPath = strFilePath
    End If
        
    Workbooks.OpenText Filename:= _
        strFinalPath, Origin:= _
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2 _
        ), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
        Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
        , 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _
        Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2), Array(27, 2), Array( _
        28, 2), Array(29, 2), Array(30, 2), Array(31, 2), Array(32, 2), Array(33, 2), Array(34, 2), _
        Array(35, 2), Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2), Array(40, 2), Array( _
        41, 2), Array(42, 2), Array(43, 2), Array(44, 2), Array(45, 2), Array(46, 2), Array(47, 2), _
        Array(48, 2), Array(49, 2), Array(50, 2), Array(51, 2), Array(52, 2), Array(53, 2), Array( _
        54, 2), Array(55, 2), Array(56, 2), Array(57, 2), Array(58, 2), Array(59, 2), Array(60, 2), _
        Array(61, 2), Array(62, 2), Array(63, 2), Array(64, 2), Array(65, 2), Array(66, 2), Array( _
        67, 2), Array(68, 2), Array(69, 2), Array(70, 2), Array(71, 2), Array(72, 2), Array(73, 2), _
        Array(74, 2), Array(75, 2), Array(76, 2), Array(77, 2), Array(78, 2), Array(79, 2), Array( _
        80, 2), Array(81, 2), Array(82, 2), Array(83, 2), Array(84, 2), Array(85, 2), Array(86, 2), _
        Array(87, 2), Array(88, 2), Array(89, 2), Array(90, 2), Array(91, 2), Array(92, 2), Array( _
        93, 2), Array(94, 2), Array(95, 2), Array(96, 2), Array(97, 2), Array(98, 2), Array(99, 2)), TrailingMinusNumbers:=True
        
End Sub

I also tried a looped string and/or variant to loop that and replace the wall of text with the variable, but that either doesn't work or I'm missing something (totally possible)
so, any suggestions that don't involve simply typing out "Array(X, 2)" a couple hundred times and having a wall of text as code?

    strFieldType = "Array(1, 2)"
    For x = 2 To 999
        strFieldType = strFieldType & ", Array(" & x & ", 2)"
    Next x
        
    Workbooks.OpenText Filename:= _
        strFinalPath, Origin:= _
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(strFieldType), TrailingMinusNumbers:=True

 

[Domenic]

Try adapting the following code, which calls on two functions. One to get the number of columns in a text file, and the other to get a two-dimensional array containing the column number and data type. Here's the code...

Option Explicit

Sub test()

    Dim fullName As String
    fullName = "c:\users\domenic\desktop\sample.txt"
    
    Dim columnCount As Long
    columnCount = GetColumnCountFromTextFile(fullName, vbTab)
    
    If columnCount = 0 Then
        MsgBox "No data found!", vbExclamation
        Exit Sub
    End If
    
    Workbooks.OpenText _
        Filename:=fullName, _
        DataType:=xlDelimited, _
        Tab:=True, _
        FieldInfo:=GetFieldInfo(columnCount)
    
    
End Sub

Private Function GetColumnCountFromTextFile(ByVal PathAndFilename As String, Optional ByVal Delim As String = ",") As Long

'   Returns the number of columns contains in a text file
'   First non-blank line used to determine number of columns based on delimiter

    Dim textLine As String
    Dim fileNumber As Long
    Dim columnCount As Long
    
    columnCount = 0
    fileNumber = FreeFile()
    Open PathAndFilename For Input As #fileNumber
        Do Until EOF(fileNumber)
            Line Input #fileNumber, textLine
            If Len(textLine) > 0 Then
                columnCount = UBound(Split(textLine, Delim)) + 1
                Exit Do
            End If
        Loop
    Close #fileNumber
    
    GetColumnCountFromTextFile = columnCount

End Function

Private Function GetFieldInfo(ByVal columnCount As Long) As Variant

'   Returns a two-dimensional array containing a column number and data type pair
'   Sets all columns to text format

    ReDim fieldInfoArray(1 To columnCount, 1 To 2)
    
    Dim i As Long
    For i = LBound(fieldInfoArray, 1) To UBound(fieldInfoArray, 1)
        fieldInfoArray(i, 1) = i
        fieldInfoArray(i, 2) = xlTextFormat
    Next i
    
    GetFieldInfo = fieldInfoArray()
    
End Function

Hope this helps!

 

[RickyFish89]

that helped greatly, thank you! everything seems to work perfectly now, having tested on multiple files. Question though- what's exactly happening with the fieldInfoArray? specifically, the , 1) and , 2) portions of the For statement. I don't exactly follow how that's working versus the initial array of just being (colNum, 2) ad nauseam. It all works correctly and every column is text as it should be, I'm just not quite understanding how it works.

thanks again!

 

[Domenic]

As you know, with Array(Array(1, 2), Array(2, 2), Array(3, 2), . . . ) , we have an array of one-dimensional arrays. A two-dimensional array is basically the same thing. It's also an array of one-dimensional arrays. And so the 1 and 2 that you see there are just subscripts used to access the desired elements so that the appropriate values can be assigned (ie. column number and data type). I would suggest that you search Google for multi-dimensional arrays for detailed information.