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