Excel VBA, Text File to Columns

In this article I will explain how you can read the values in a text file and print them in columns in Excel. For example lets say we have the following data in a text file:

VBA Excel, Text File Columns

We want to read the data from the text file and print them in the same column format in an Excel sheet:

VBA Excel, Columns in Excel

Jump To:

You can download the file and code related to this article here.


Contents

Example 1, Comma Delimiter “,”:

In the first example the comma character “,” is used as a delimiter to separate the columns in the text file:

VBA Excel Comma Delimiter Text File Columns
The code below will do the following things:

  1. Displays an open file dialog to select the location of the text file. For more information about open file dialogs please see VBA Exel Open File Dialog
  2. Loops through the text file and reads each line. For  more information about reading text files please see Excel VBA, Reading Text Files.
  3. Using string processing the lines are split based on the comma delimiter “,” and the separate parts are printed in separate columns in the excel sheet. For more information about string processing please see VBA Excel String Processing and Manipulation.

Sub Example1()
'dialog result
Dim intDialogResult As Integer
'path selected by user
Dim strPath As String
'single line of data from the text file
Dim strLine As String
'string seperated by the comma delmiter
Dim arrString() As String
'curren row in excel sheet
Dim i As Integer

'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'remove previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'assign custom filters
Call Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
"Text Files", "*.txt")

'display the open file dialog
intDialogResult = Application.FileDialog(msoFileDialogOpen).Show
'if the user selected a file
If intDialogResult <> 0 Then
    'path selected by the user
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    'close the file index #1 if its already opened
    Close #1
    'open the file for reading
    Open strPath For Input As #1
    i = 1
    'loop while the end of file has not been reached
    While EOF(1) = False
        'read one line of data from the text file
        Line Input #1, strLine
        'split string based on delimiter
        arrString = Split(strLine, ",")
        Cells(i, 1) = arrString(0)
        Cells(i, 2) = arrString(1)
        Cells(i, 3) = arrString(2)
        i = i + 1
    Wend
End If
Close #1
End Sub

Result:

VBA Excel, Columns in Excel
The lines below display an open file dialog. For more information about open file dialogs please see VBA Exel Open File Dialog:

'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'remove previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'assign custom filters
Call Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
"Text Files", "*.txt")
'display the open file dialog
intDialogResult = Application.FileDialog(msoFileDialogOpen).Show

The If statement checks if the user has selected a file:

'if the user selected a file
If intDialogResult <> 0 Then
...
End If

The While loop iterates through all the lines in the text file:

While EOF(1) = False
...
Wend

The line below reads the current line from the text file. For  more information about reading text files please see Excel VBA, Reading Text Files:

'read one line of data from the text file
Line Input #1, strLine

The lines below split the string read from the text file based on the space delimiter ” “and prints the results in Excel columns. For more information about string processing please see VBA Excel String Processing and Manipulation:

'split string based on delimiter
arrString = Split(strLine, ",")
Cells(i, 1) = arrString(0)
Cells(i, 2) = arrString(1)
Cells(i, 3) = arrString(2)


Example 2, Space Delimiter ” “:

This example is similar to Example 1. The only difference is  instead of using a comma character “,” as the delimiter s a space character is used:

Text File With Space Delimters, Columns
The code needed to print the data from the text file to Excel columns is similar to the previous example:

Sub Example2()
'dialog result
Dim intDialogResult As Integer
'path selected by user
Dim strPath As String
'single line of data from the text file
Dim strLine As String
'string seperated by the comma delmiter
Dim arrString() As String
'current row in the excel sheet
Dim i As Integer

'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'remove previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'assign custom filters
Call Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
"Text Files", "*.txt")

'display the open file dialog
intDialogResult = Application.FileDialog(msoFileDialogOpen).Show
'if the user selected a file
If intDialogResult <> 0 Then
    'path selected by the user
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    'close the file index #1 if its already opened
    Close #1
    'open the file for reading
    Open strPath For Input As #1
    i = 1
    'loop while the end of file has not been reached
    While EOF(1) = False
        'read one line of data from the text file
        Line Input #1, strLine
        'split string based on delimiter
        arrString = Split(strLine, " ")
        Cells(i, 1) = arrString(0)
        Cells(i, 2) = arrString(1)
        Cells(i, 3) = arrString(2)
        i = i + 1
    Wend
End If
Close #1
End Sub

In the highlighted line, the delimiter character has been changed to the space character ” “. The result is the same as the previous example:

VBA Excel, Columns in Excel


Example 3, Multi Space Delimiter:

At the beginning of this article I used the example text file below:

VBA Excel, Text File Columns
The difference between this text file and and the one used in Example 2 is the fact that in this example multiple spaces are used as delimiters between columns. The code below will read the data in the text file above and print the results in Excel columns:

Sub Example3()
'dialog result
Dim intDialogResult As Integer
'path selected by user
Dim strPath As String
'single line of data from the text file
Dim strLine As String
'string seperated by the comma delmiter
Dim arrString() As String
'current row in the excel sheet
Dim i As Integer
'current index in string array
Dim j As Integer
'current column in the excel sheet
Dim intCurrColumn As Integer

'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'remove previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'assign custom filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Add("Text Files", "*.txt")
'display the open file dialog
intDialogResult = Application.FileDialog(msoFileDialogOpen).Show
'if the user selected a file
If intDialogResult <> 0 Then
    'path selected by the user
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    'close the file index #1 if its already opened
    Close #1
    'open the file for reading
    Open strPath For Input As #1
    i = 1
    'loop while the end of file has not been reached
    While EOF(1) = False
        'read one line of data from the text file
        Line Input #1, strLine
        'split string based on delimiter
        arrString = Split(strLine, " ")
        intCurrColumn = 1
        For j = LBound(arrString) To UBound(arrString)
           If arrString(j) <> "" Then
            Cells(i, intCurrColumn) = arrString(j)
            intCurrColumn = intCurrColumn + 1
           End If
        Next
        i = i + 1
    Wend
End If
Close #1
End Sub

The line below splits the current line of the text file based on the space ” ” delimiter. For more information about string processing please see VBA Excel String Processing and Manipulation:

arrString = Split(strLine, " ")

The difference between this example and Example 2 is that multiple space characters may exist between the values in the text file used in this example. For example the second line “John      86102455         88%” is split into 17 strings:

Split String Array in Watch VBA Excel
The loop below iterates through the arrString array. It checks if the value at the current index is something other than “” and prints the value in the associated column:

intCurrColumn = 1
For j = LBound(arrString) To UBound(arrString)
    If arrString(j) <> "" Then
        Cells(i, intCurrColumn) = arrString(j)
        intCurrColumn = intCurrColumn + 1
    End If
Next

You can download the file and code related to this article here.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *