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

• The CSV file data will now appear in the Excel worksheet, with each line’s data in separate cells.

Read More: Excel VBA to Read CSV File into Array

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Excel VBA to Import CSV File without Opening
• Excel VBA to Convert CSV File to XLSX
• Excel VBA: Merge Multiple CSV Files into One Workbook