RSS

Fast text file import with Excel VBA

If you frequently import text files of the same kind to Excel, I bet you are tired of using the wizard selecting semicolon, tab or whatever as delimiter etc. It takes time!

With Excel VBA you can parse and import text files (as explained here), and that is great for "special needs," but if possible one should always use Excel's built-in functions, because they are much faster than any code.

On this page I show how to automate the import without displaying the wizard. You just need to know which delimiter (semicolon, tab etc.) is used to separate the columns.

The only thing you need to do is select the file, the macro takes care of the rest - and fast!

For starters let us see how the VBA code looks, if we import a text file (and use the macro recorder in my Danish version of Excel 2003). In this example the delimiter is semicolon:


Sub Makro1()

'
' Makro1 Makro
' Makro indspillet 13-07-2013 af Eric Bentzen
'

'
Workbooks.OpenText Filename:="C:\textexample.txt", _
   Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
   TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
   Tab:=False, Semicolon:=True, Comma:=False, Space:=False, _
   Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
   Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
   Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), _
   Array(11, 1), Array(12, 1), Array(13, 1)), _
   TrailingMinusNumbers:=True
End Sub

To use the macro for other files we need to replace "C:\textexample.txt" with the path to a file selected by the user, and we don't need the file specific "FieldInfo ...".

To get a file name selected by the user we use the function "Application.GetOpenFilename". It allows the user to select a file without actually opening it, and we store the file name and path in a variable, which we use instead of "C:\textexample.txt".

You can see the finished macro below. Mark the text, copy with CTRL+C and insert (CTRL+V) into a VBA module - or download a zipped example. The zip file also contains a semicolon delimited text file for you to play with.

If you are viewing this page on a device with a small screen, some code lines will probably be broken/wrapped, but if you copy and paste into a VBA module, it should be okay with the right linebreaks.


Sub ImportTextFile()
'Import a text file using Excel's own import function.
Dim vFileName

On Error GoTo ErrorHandle

'The function GetOpenFileName gets the file name without
'opening the file.
'Here we use a filter to display only text files with "*.txt" as
'extension. If you omit the file filter, all files will show.
'Read the VBA help for other options.
vFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")

'If the user pressed Cancel or didn't select a text file, we exit.
If vFileName = False Or Right(vFileName, 3) <> "txt" Then
   GoTo BeforeExit
End If

'Switch screen updating off for speed.
Application.ScreenUpdating = False

'We now import the selected text file, and data is
'inserted into a new spreadsheet. If you want to use
'another delimiter than semicolon, you must change
'"Semicolon:=True" to "Semicolon:=False" and set the
'other delimiter (e.g. "Tab") to True.
'I recently discovered that you can avoid
'some formatting problems (e.g. with dates),
'if you add the finel "Local:=True". It depends
on your local settings and Excel version, but
'the addition does no harm.
Workbooks.OpenText Filename:=vFileName, _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, _
    Other:=False, TrailingMinusNumbers:=True, _
    Local:=True

'Just to show how, we auto fit the width of column A.
Columns("A:A").EntireColumn.AutoFit

BeforeExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

I import a lot of text files and to facilitate things I have a spreadsheet with a macro almost identical to the one above.

I just click a button to execute, and the text file's data is put into another spreadsheet without "compromising" the one with the macro.

You can download a zip compressed example spreadsheet here, and the zipped file also contains a sample text file (with semicolon as delimiter) to play with.

If you experience formatting problems with dates or other stuff, add the "Local:=True" as described above.

Related: