VBA to fill array with delimited data from text file

[Westbury]

I'm trying to use VBA to extract data from a series of text files. So far I've found the code below at The VBA Guide To Interacting With Text (.Txt) Files — The Spreadsheet Guru

Sub DelimitedTextFileToArray()
'PURPOSE: Load an Array variable with data from a delimited text file
'SOURCE: www.TheSpreadsheetGuru.com

Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String
Dim rw As Long, col As Long

'Inputs
 Delimiter = ";"
 FilePath = "C:\Users\chris\Desktop\MyFile.txt"
 rw = 0
 
'Open the text file in a Read State
 TextFile = FreeFile
 Open FilePath For Input As TextFile
 
'Store file content inside a variable
 FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
 Close TextFile
 
'Separate Out lines of data
 LineArray() = Split(FileContent, vbCrLf)

'Read Data into an Array Variable
 For x = LBound(LineArray) To UBound(LineArray)
 If Len(Trim(LineArray(x))) <> 0 Then
 'Split up line of text by delimiter
 TempArray = Split(LineArray(x), Delimiter)
 
 'Determine how many columns are needed
 col = UBound(TempArray)
 
 'Re-Adjust Array boundaries
 ReDim Preserve DataArray(col, rw)
 
 'Load line of data into Array variable
 For y = LBound(TempArray) To UBound(TempArray)
 DataArray(y, rw) = TempArray(y)
 Next y
 End If
 
 'Next line
 rw = rw + 1
 
 Next x

End Sub

But this takes my data and puts it into columns in my spreadsheet. I need it in rows so I've tried to change LineArray with ColArray but it hasn't worked. What should I do?

My data is in blocks separated by blank lines in the text file; do I need to allow for these blanks to make sure the array sees all?

 

[John_w]

Untested; try changing:

DataArray(y, rw) = TempArray(y)

to:

DataArray(rw, y) = TempArray(y)

 

[Westbury]

I've made the edit but now get an error message "Run time error 9 Subscript out of range" at this line

DataArray(rw, y) = TempArray

 

[John_w]

Swap the row and column dimensions in the ReDim Preserve, like this:

ReDim Preserve DataArray(rw, col)

 

[Westbury]

Done that, but the error message has moved with it!

As a VBA novice I'm begining to understand the code and the changes that you've suggested, but I think that the problem also lies in the layout of my data.

e.g. a small extract

CA,0,268,2,1122,0,0,0,0,0,0,
DF,45BTS,51.37675000,-2.85247222,0, ,0.0,0.0,2,3000,0,0,0,0,0,1,
CI,2, ,0,88,1,6000,0,1,230,0,0,0,
CF,HON60,51.55056667,-2.61861667,0,HON,218.3,59.9,38,10.00,2,8000,0,0,0,0,0,0,
TF,BADIM,51.64388889,-2.51111111,0, ,0.0,0.0,0,0.00,0,0,0,0,0,0,0,0,

There are varying amounts of data on different lines. I think that I've found 18 in one line and 4 in another. How does the array cope with tihs?

 

[Leith Ross]

Hello Westbury,

The new array must be sized to hold the transposed data. In this macro, the data is read in as a byte array. This allows for fast checking of end of line characters and delimiters. The bytes are converted back into a string then split into lines. The values are used to dimension the new array. The new array is loaded with two For Next loops. One indexes the rows and the other the columns. Each line is split to extract the column data which is loaded into the new (transpose) array and output to the worksheet.

Option Explicit


Sub DelimitedTextFileToArray()


    Dim ColCnt      As Long
    Dim cnt         As Long
    Dim DataIn()    As Byte
    Dim DataOut     As Variant
    Dim Delimiter   As String
    Dim File        As String
    Dim Line        As Variant
    Dim Lines       As Variant
    Dim RngOut      As Range
    Dim RowCnt      As Long
    Dim Text        As String
    Dim Wks         As Worksheet
    
        ' // Use the ActiveSheet. You specifiy any sheet you want here.
        Set Wks = ActiveSheet
        
        ' // Starting cell for output.
        Set RngOut = Wks.Range("A1")
        
        File = "C:\Users\chris\Desktop\MyFile.txt"
        
        ' // Character that separates the data fields.
        Delimiter = ";"
        
            ' // Read the whole file into memory as byte data.
            Open File For Binary Access Read As #1 
                ReDim DataIn(LOF(1))
                Get #1 , , DataIn
            Close #1 
            
            ' // Find the rows and the greastest column width needed for the transpose array.
            For n = 0 To UBound(DataIn, 1) - 1
                ' // Is this byte a delimiter?
                If DataIn(n) = Asc(Delimiter) Then cnt = cnt + 1 ' Count the number of columns in this line.
                
                '// Is this the end of the line?
                If DataIn(n) = 13 Then
                    ' // Save the greatest number of columns found.
                    If cnt > ColCnt Then ColCnt = cnt
                    cnt = 0                 ' Reset the line column count.
                    RowCnt = RowCnt + 1     ' Increase the row count.
                End If
            Next n
            
            ' // Convert the bytes into a string.
            Text = StrConv(DataIn, vbUnicode)
            
            ' // Create an array of the lines.
            Lines = Split(Text, vbCrLf)
            
            ' // Dimension the transpose array.
            ReDim DataOut(ColCnt, RowCnt)
            
            ' // fill the transpose array.
            For RowCnt = 0 To UBound(DataOut, 2) - 1
                Line = Split(Lines(RowCnt), Delimiter)
                For cnt = 0 To UBound(Line)
                    DataOut(cnt, RowCnt) = Line(cnt)
                Next cnt
            Next RowCnt
            
            ' // Output the array to the worksheet.
            RngOut.Resize(ColCnt + 1, RowCnt + 1).Value = DataOut
            
End Sub

 

[Westbury]

Leith,

Thanks for your reply. I've tried it and found that the code takes each line of data from the text file and puts it in consecutive cells in row A of the spreadsheet. I need each line of data on its own row and the figures separated into columns at the comma locations. Can you advise what changes are required please?

Geoff (Westbury)

 

[Leith Ross]

Hello Geoff,

Since all my test files were transposed correctly, including the one I created from your posted data, something has changed. Can your post your text file on a file sharing site or would you rather send it to me via email?

 

[Westbury]

Keith,

I've found that the delimiter was set to ; I've changed it to comma.

The code now puts the lines of data into individual columns; just need to get it into rows. If you don't mind letting me have an e-mail address I'll send you my text file.

Geoff

 

[Leith Ross]

Hello Geoff,

I sent my email address to you by Private Message.