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:
We want to read the data from the text file and print them in the same column format in an Excel sheet:
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:
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.
See also:
- Excel VBA, Reading Text Files
- Excel VBA, Open File Dialog
- Excel VBA, Writing to Text Files
- VBA, Modify Existing Text File
- VBA Excel String Processing and Manipulation.
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