Thursday 26 February 2015

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 -

DelimitedtextFile  
4 Rows
3 Columns
Delimited by ',' (Comma)
   

After Import -

importedtext
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.

4 comments:

  1. Howto add more data to the same sheet with this code, because it seems the data will be over written

    ReplyDelete
  2. To add more data to the same sheet, change the following two lines -


    SaveColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row


    Modify the column and row values.


    In your case, find the last available row in the sheet.


    For example, RowNdx = lastRowofSheet

    ReplyDelete
  3. Thank for the reply,
    I add the following code:

    ;== below
    Public Sub ImportFile(FName As String, Sep As String)

    ; == add
    Dim lr As Long
    lr = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
    lr = lr + 1

    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A" & lr).Select
    ;== END

    works like a charm, but every txtfile i add, the first cell/row starts with:
    ÿþ22-2-2015


    Where is that "ÿþ" comming from ??

    Frank

    ReplyDelete
  4. check this link for detailed explanation and how to resolve -
    https://coderadar.wordpress.com/2015/03/26/import-csv-or-any-delimited-text-file-with-unicode-encodings-in-excel-using-vba/

    ReplyDelete

 
biz.