CODEWITHSUNDEEP
vbaexcelsplit
Dave-Oh
Dave-Oh

Reputation: 3

Converting .txt file directly into an array with custom delimiter

I am converting a .txt file directly into an array in excel VBA. The default delimiter is a "," (comma) and I need to change it to "vblf". I am having trouble figuring out how to do that with the code I have today.

Please help

    Const strFileName As String = [file]
    Dim CONFIGTXT(1 To 13000) As String
    Dim intFileNum As Integer
    Dim intCount As Integer
    Dim strRecordData As String

    intFileNum = FreeFile
    intCount = 1
    Open strFileName For Input As #intFileNum
    Do Until EOF(intFileNum) Or intCount > 13000
        Input #intFileNum, strRecordData
        CONFIGTXT(intCount) = strRecordData
        intCount = intCount + 1

    Loop
    Close #intFileNum

    Range("Q2:Q" & UBound(CONFIGTXT) + 1) = WorksheetFunction.Transpose(CONFIGTXT)

Upvotes: 0

Views: 199

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Change

Input #intFileNum, strRecordData

to

Line Input #intFileNum, strRecordData

Input is intended to read in data that is comma-delimited, one variable at a time. For example, if you had data of

12345,789

and used the statement

Input #intFileNum var1, var2

then var1 would be given the value 12345 and var2 would be given the value 789.

Line Input is intended to read a line at a time, delimited by the new line character (normally CR/LF).

Note: If your data has information separated by line feeds, this will NOT separate those portions into separate entries in the array. So if your data contains

xxx/xxx/xxx

where the / is actually a line feed, that entire record will be placed into one cell in the final output.

Upvotes: 1

Related Questions