How to Read a CSV File Line by Line Using Excel VBA (3 Methods)

Dataset Overview

To illustrate, we will use a sample dataset as an example. The below dataset within a CSV file represents the Salesman, Product, and Sales of a company.

Method 1 – Simple VBA Code

STEPS

Open a blank Excel workbook.
Right-click on the Sheet tab (located just above the Excel status bar).
Select View Code from the options.

In the VBA window that appears, copy and paste the following code:

Sub ReadLinebyLine()
   Dim file As String, line As String
   file = "D:\46\vba read csv file line by line\Sample.csv"
   Open file For Input As #1
   Do Until EOF(1)
      Line Input #1, line
      ActiveCell = line
      ActiveCell.Offset(1, 0).Select
   Loop
   Close #1
End Sub

Save the file and close the VBA window.
Select any cell where you want to paste the data from the CSV (e.g., select cell A2).
Go to the Developer tab and select Macros.

Choose the ReadLinebyLine option and click Run.

The data from the CSV file will be pasted into the Excel sheet.

Note: This method returns the respective lines in a single cell, separated by commas.

Method 2 – Using FileSystemObject (FSO)

In this method, we’ll utilize FileSystemObject (FSO), which isn’t inherent to Excel VBA. The FSO adheres to international standards and serves as an interface to a computer’s file system. In VBA, we can instantiate an object, referred to as late binding, to leverage the FSO.

STEPS

Right-click on a worksheet and select View Code.
In the VBA window, copy and paste the following code:

Sub FileSystemObj()
  Dim line As String
  Dim FSO As Object
  Dim TS As Object
  Set FSO = CreateObject("Scripting.FileSystemObject")
  Set TS = FSO.OpenTextFile("D:\46\vba read csv file line by line\Sample.csv")
  Do While Not TS.AtEndOfStream
     line = TS.ReadLine
     ActiveCell = line
     ActiveCell.Offset(1, 0).Select
  Loop
  TS.Close
  Set TS = Nothing
  Set FSO = Nothing
End Sub

Close the VBA window after saving the file.
Go to the Developer tab and click on Macros.
Select FileSystemObj in the Macro dialog box.
Click Run.

The data from the CSV file will be pasted into Excel.

Method 3 – Read CSV File Line by Line with VBA and Paste in Individual Cells

In the previous methods, we pasted the dataset lines into a single cell, separated by commas. Now, let’s explore how to paste each line’s data into individual cells using VBA. Follow these steps:

STEPS

Right-click on a sheet and select View Code.
The VBA window will appear, displaying a dialog box.
Copy the following code and paste it there:

Sub IndividualCells()
  Dim line As String
  Dim FSO As Object
  Dim TS As Object
  Dim LineElements As Variant
  Dim Ind As Long
  Dim p As Long
  Dim Delimiter As String
  Set FSO = CreateObject("Scripting.FileSystemObject")
  Set TS = FSO.OpenTextFile("D:\46\vba read csv file line by line\Sample.csv")
  Delimiter = ","
  Ind = 1
  Do While TS.AtEndOfStream = False
    line = TS.ReadLine
    LineElements = Split(line, Delimiter)
    For p = LBound(LineElements) To UBound(LineElements)
       Cells(Ind, p + 1).Value = LineElements(p)
    Next p
    Ind = Ind + 1
  Loop
  TS.Close
  Set TS = Nothing
  Set FSO = Nothing
End Sub

Close the VBA window after saving the file.
Select Macros under the Developer tab.
Select IndividualCells from the list in the Macro dialog box and click Run.