VBA to fill array with delimited data from text file

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
137
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

Code:
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
Untested; try changing:
Code:
DataArray(y, rw) = TempArray(y)
to:
Code:
DataArray(rw, y) = TempArray(y)
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
137
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(y)
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
Swap the row and column dimensions in the ReDim Preserve, like this:
Rich (BB code):
ReDim Preserve DataArray(rw, col)
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
137

ADVERTISEMENT

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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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.

Rich (BB code):
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

Board Regular
Joined
Jun 7, 2009
Messages
137

ADVERTISEMENT

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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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

Board Regular
Joined
Jun 7, 2009
Messages
137
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello Geoff,

I sent my email address to you by Private Message.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,640
Members
417,159
Latest member
Mayozero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top