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