Excel VBA, Text File to Columns

Jul 18, 2015 by azurous in Excel

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:

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

Jump To:

Exampe 1. Comma Delimiter “,”
Example 2, Space Delimiter ” “
Example 3, Multi Space Delimiter

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:

The code below will do the following things:

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
Loops through the text file and reads each line. For more information about reading text files please see Excel VBA, Reading Text Files.
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:

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:

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:

–

Example 3, Multi Space Delimiter:

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

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:

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.