Import CSV or delimited text file in Excel - VBA

This post demonstrates how to import CSV or any delimited text file in Excel using VBA code.

Each line in the text file is written into one row in a worksheet. The imported data is separated into columns in a worksheet depending on the delimited character that you specify.

But there are two limitations with the below example. The first limitation is code works with 64000 records in Excel 2003 and 1048576 records in the later versions. If the file is larger than this, modify the code to split the imported data across multiple worksheets. The second limitation is code works if the text file is in ANSI character representation which is default when you save a text file on WINDOWS.

Sample Text File -

 

4 Rows

3 Columns

Delimited by ',' (Comma)

   

After Import -

When the import button is clicked, the text appears in a worksheet as shown in the picture above.

Import Button Code -

Sub ImportText()
    ImportFile FName:="D:\data.txt", Sep:=","
End Sub

 

ImportFile is the sub-routine which imports the data. Change the sep:= as per your requirement. In this example comma is the separator.

ImportFile Code -

Public Sub ImportFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

'disable screen updates
Application.ScreenUpdating = False
'error handling
On Error GoTo EndMacro

'Importing data starts from the selected cell in a worksheet
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

'open the file in read mode
Open FName For Input Access Read As #1

'Read the file until End of Line
While Not EOF(1)
    'read line by line
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    'finding each column data
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend

Close #1
Exit Sub
EndMacro:
Application.ScreenUpdating = True
MsgBox Err.Description, vbOKOnly, "Error"
Close #1

End Sub

 

Summary -

This way you can import any delimited text file. Make sure that the text file is not exceeding the maximum number of rows of excel worksheet.

---

Please share your thoughts on this post in the comments section.

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.