Excel VBA to Read CSV File Line by Line (3 Ideal Examples)

Nowadays, the CSV (Comma Separated Values) file format is quite popular due to its simplicity. Readers can easily understand and analyze the data. However, we may want to import the data into an Excel file from the CSV file for performing various operations taking the help of Excel features and functions. In this article, we will show you the simple yet effective examples to apply Excel VBA to Read CSV File Line by Line.

Download Practice Workbook

Download the following workbook to practice by yourself.

3 Ideal Examples of Excel VBA to Read CSV File Line by Line

To illustrate, we will use a sample dataset as an example. For instance, the below dataset represents the Salesman, Product, and Sales of a company and it’s present in a CSV file. Here, we’ll read this data line by line with VBA and paste it into an Excel worksheet.

1. Apply VBA to Read CSV File Line by Line and Paste It into Excel

In our first example, we’ll show you the process to apply a simple Code in Excel VBA to carry out the operation. Therefore, follow the below steps carefully.

STEPS:

• First, open a blank Excel workbook.
• Then, right-click on the sheet (present just above the Excel status bar).
• After that, select View Code from the options.

• As a result, the VBA window will pop out and a dialog box will appear.
• Now, copy the following code and paste it into the box.

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

• Next, save the file and close the VBA window.
• Subsequently, select any cell where you want to paste the data from the CSV In this example, select A2.
• Afterward, select Developer ➤ Macros.

• Consequently, the Macro dialog box will emerge.
• There, choose the ReadLinebyLine option and press Run.

• Lastly, you’ll get your required data in the Excel sheet as it’s shown in the picture below.

NOTE: It returns the respective lines in a single cell, separated by a comma.

Read More: How to Read CSV File in Excel (4 Fastest Ways)

2. Use FileSystemObject (FSO) in Excel VBA for Reading CSV File

In this example, we’ll make use of FileSystemObject (FSO) which is not a part of Excel VBA. The FSO is an object of international standards that provides the gateway to a computer’s file system. In VBA, we can create an object, which is known as late binding, to use the FSO. So, learn the following steps to apply Excel VBA to Read CSV File Line by Line.

STEPS:

• Firstly, right-click on a worksheet and select View Code.
• As a result, a dialog box will pop out in the VBA window.
• Now, copy the below code and paste it there.

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

• Next, close the VBA window after saving the file.
• Then, go to Developer ➤ Macros.
• After that, select FileSystemObj in the Macro dialog box.
• Subsequently, press Run.

• At last, it’ll return your data from the CSV file and paste it into Excel like it’s displayed in the following image.

Read More: How to Open CSV File in Excel with Columns Automatically (3 Methods)

Further Readings

• How to Import Text File to Excel Automatically (2 Suitable Ways)
• Excel VBA: Read Text File into String (4 Effective Cases)
• How to Import Text File to Excel Using VBA (3 Easy Ways)
• Excel VBA to Import CSV File without Opening (3 Suitable Examples)

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

So far, we’ve pasted the dataset lines in a single cell and they are separated by a comma. In this example, we’ll show you a VBA code that will paste the data of a single line in the individual cells. Hence, learn the steps to perform the task.

STEPS:

• Select View Code after right-clicking on a sheet at first.
• Consequently, the VBA window will appear where you’ll get a dialog box.
• Then, copy the below 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

• After that, close the VBA window after saving.
• Now, select Macros under the Developer tab.
• Next, click IndividualCells from the list in the Macro dialog box and press Run.

• In the end, the CSV file data will appear in the Excel worksheet in the individual cells as per our expectations.

Read More: How to Open CSV File with Columns in Excel (3 Easy Ways)

Conclusion

Henceforth, you will be able to apply Excel VBA to Read CSV File Line by Line following the above-described examples. Keep using them and let us know if you have any more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Convert CSV to XLSX (4 Quick Methods)
• Excel VBA to Convert CSV File to XLSX (2 Easy Examples)
• Import CSV into Existing Sheet in Excel (5 Methods)
• How to Convert CSV to XLSX without Opening (5 Easy Methods)
• Open Notepad or Text File in Excel with Columns (3 Easy Methods)
• Excel VBA: Import Comma Delimited Text File (2 Cases)