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?

 

[Rick Rothstein]

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,

I am unclear about the output you want. For the above, is it this in each cell of Column A...

CA
0
258
21122
0
0
0
0
0
0
DF
45BTS
etc.

If so, what should happen on this line at the double comma?

CI,2, ,0,88,1,6000,0,1,230,0,0,0,

Do you want this...

CI
2
0
88
etc.

or this...

CI
2

0
88
etc.

where the missing data produces a blank row.

 

[Rick Rothstein]

If you don't mind letting me have an e-mail address I'll send you my text file.

I sent my email address to you by Private Message.

Just reminding you both that this violates Forum Rule #4 . The reason for that rule is those of us who want to help can't because the information we need is only being shared with one person. The proper way is for the OP to put his file on a well-known, respect file sharing service like DropBox and then post the link they give you for the sharable file.

 

[Westbury]

Rick,

I'm trying to get each row of data from the text file into a row in Excel and separating each item of data into its own cell based on the comma locations in the text file. I know that there's a wizard in Excel but I've got >13,000 files!

Your comment below noted; apologies. I'll ensure that the solution is posted for others to see.

 

[Leith Ross]

Hello Wetbury,

Okay, that was my fault. For some reason I thought you wanted the data transposed on the worksheet. I changed the macro so the output is line by line.

My apologies Rick for violating the rule.

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 n           As Long
    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 = vbTab
        
            ' // 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 output array.
            ReDim DataOut(RowCnt, ColCnt)
            
            ' // fill the output array.
            For RowCnt = 0 To UBound(DataOut, 1) - 1
                Line = Split(Lines(RowCnt), Delimiter)
                For cnt = 0 To UBound(Line)
                    DataOut(RowCnt, cnt) = Line(cnt)
                Next cnt
            Next RowCnt
            
            ' // Output the array to the worksheet.
            RngOut.Resize(RowCnt + 1, ColCnt + 1).Value = DataOut
            
End Sub

 

[Westbury]

Leith,

My delimiter is a comma so I've made an edit

Delimiter = ","

Your code does the trick. Many Thanks.

Geoff

 

[John_w]

Rick,

I'm trying to get each row of data from the text file into a row in Excel and separating each item of data into its own cell based on the comma locations in the text file. I know that there's a wizard in Excel but I've got >13,000 files!

Your thread title and initial code is at odds with your overall aim, and the code posted so far will only import the data from one text file.

This macro effectively uses the Text Import Wizard to import the csv data from all the .txt files in a folder into rows and columns in the active sheet, starting at A1.

Public Sub Import_Text_Files_To_Rows()
    
    Dim fd As FileDialog
    Dim folderPath As String, fileName As String
    Dim destCell As Range, qt As QueryTable
    
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        .AllowMultiSelect = False
        .Title = "Select the folder containing text files to be imported"
        If Not .Show Then Exit Sub
        folderPath = .SelectedItems(1)
    End With
    
    With ActiveSheet
        .Cells.ClearContents
        Set destCell = .Range("A1")
    End With
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    fileName = Dir(folderPath & "*.txt")
    Do While fileName <> vbNullString
        Set qt = destCell.Worksheet.QueryTables.Add(Connection:="TEXT;" & folderPath & fileName, Destination:=destCell)
        With qt
            .TextFileParseType = xlDelimited
            .TextFileConsecutiveDelimiter = True
            .TextFileCommaDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
        Set destCell = destCell.Offset(qt.ResultRange.Rows.Count)
        qt.Delete
        fileName = Dir()
    Loop
    
End Sub