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

Get FREE Advanced Excel Exercises with Solutions!

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 to perform various operations taking the help of Excel features and functions. In this article, we will show you simple yet effective examples of applying Excel VBA to Read CSV files line by Line.


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

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.

vba read csv file line by line


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.

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

  • 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

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

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

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

  • 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 shown in the picture below.

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


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 files 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

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

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


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

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

  • 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: Excel VBA to Read CSV File into Array


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to apply Excel VBA to Read CSV files 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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo