What Is a CSV File?
A CSV file is a comma-separated values file, which saves data in an organized format. CSV files are compatible with many worksheet programs, including Microsoft Excel and Google Spreadsheet. It’s essentially a text file in the background and can be opened by simple text editors like Notepad.
A CSV file uses commas to divide data. It is a mechanism that allows otherwise incompatible applications to share data sets, such as the elements of a database.
Read a CSV File into an Array with VBA: 4 Examples
To read the CSV file into an array, we will utilize the CSV file below to demonstrate our points. There are 4 columns in the file: Employee ID, First Name, Last Name, and Birth Date.
We saved our CSV file into the path shown in the picture below and named it Employee Details. While working with the VBA code, we need that specific file path, and the location for the file is “C:\Exceldemy\Employee Details.csv”. When testing these methods, you will need to change the file path and name accordingly.
Example 1 – Read the Entire Line of a CSV File into an Array Using Excel VBA
STEPS:
- Go to the Developer tab from the ribbon.
- From the Code category, click on Visual Basic to open the Visual Basic Editor, or press Alt + F11.
- You can also right-click on your worksheet name and go to View Code.
- Click on Module from the Insert drop-down menu.
- This will create a Module in your workbook.
- Copy and paste the VBA code shown below into the module.
VBA Code:
Sub Read_CSV()
Dim sf, fi
Set sf = CreateObject("Scripting.FileSystemObject")
Set fi = sf.opentextfile("C:\Exceldemy\Employee Details.csv")
value = fi.readline
End Sub
- Run the code by pressing the F5 key on your keyboard.
- This code will read the entire lines of the CSV file.
VBA Code Explanation
Sub Read_CSV()
Sub is a part of code that is used to handle the work in the code but will not return any value. It is also known as subprocedure. So we name our procedure Read_CSV().
Dim sf, fi
The DIM statement in VBA refers to “declare,” and it must be used to declare a variable. So, we declare the variable to create an object for the file system sf and the variable for opening the CSV file fi.
Set sf = CreateObject("Scripting.FileSystemObject")
Set fi = sf.opentextfile("C:\Exceldemy\Employee Details.csv")
The term Set is used to provide a connection to an entity or cell range that will remain fixed throughout the script or code in Excel. So, we set our two variables in these two lines of code.
value = fi.readline
This line of code will read the file’s lines.
End Sub
This will end the procedure.
Method 2 – Excel VBA to Read Line by Line of a CSV File into an Array
STEPS:
- Open a VBA window (press Alt + F11).
- Go to Insert and select Module from the drop-down menu.
- Copy and paste the VBA code below into the module.
VBA Code:
Private Sub Read_CSV2()
Dim fnam As String
Dim f As Integer
Dim fi As String
Dim ln As Variant
Dim fln As Variant
Dim row As Long
Dim col As Long
Dim arr() As String
Dim l As Long
Dim cl As Long
fnam = "C:\Exceldemy\Employee Details.csv"
If Right$(fnam, 1) <> "\" Then fnam = _
fnam & "\"
fnam = fnam & "Employee Details.csv"
f = FreeFile
Open fnam For Input As f
fi = Input$(LOF(f), #f)
Close f
ln = Split(fi, vbCrLf)
row = UBound(ln)
fln = Split(ln(0), ",")
col = UBound(fln)
ReDim arr(row, col)
For l = 0 To row
If Len(ln(l)) > 0 Then
fln = Split(ln(l), ",")
For cl = 0 To col
arr(l, cl) = fln(cl)
Next cl
End If
Next l
End Sub
- Press the F5 key or click on the Run Sub button to run the code.
- This code will read from the first row of the array from the CSV File.
VBA Code Explanation
f = FreeFile
Open fnam For Input As f
fi = Input$(LOF(f), #f)
Close f
These blocks of the codes will load the file.
ln = Split(fi, vbCrLf)
This will break the CSV file into lines.
row = UBound(ln)
fln = Split(ln(0), ",")
col = UBound(fln)
ReDim arr(row, col)
This will find the dimension of the array.
For l = 0 To row
If Len(ln(l)) > 0 Then
fln = Split(ln(l), ",")
For cl = 0 To col
arr(l, cl) = fln(cl)
Next cl
Those blocks are for copying and reading the data from the array.
Read More: Excel VBA to Read CSV File Line by Line
Method 3 – Read a CSV File into an Array with Built-in VBA Statements
STEPS:
- Open a VBA window (press Alt + F11).
- Go to Insert and select Module from the drop-down menu.
- Copy and paste the VBA code below into the module.
VBA Code:
Sub Read_Csv1()
Dim sf As String
Dim arr() As String
Dim cnt As Long
sf = "c:\data\book1.csv"
ReDim arr(1)
Open sf For Input As #1
Do
cnt = cnt + 1
ReDim Preserve arr(cnt)
Line Input #1, arr(cnt)
Loop Until EOF(1)
Close #1
End Sub
- Press the F5 key to run the code.
VBA Code Explanation
sf = "c:\data\book1.csv"
This defines the file path.
ReDim arr(1)
The ReDim expression sized or resized a dynamic array.
Open sf For Input As #1
This takes the file path from sf and then inputs the line statement in #1.
Do
cnt = cnt + 1
We use the Do loop to count the lines of the array in our CSV file.
ReDim Preserve arr(cnt)
This sizes or resizes the array for each count.
Line Input #1, arr(cnt)
The Line statement takes the input line from the array and reads every line of the array count.
Loop Until EOF(1)
The Loop will run Until a file has reached its end without throwing an error using the EOF expression. Then, insert code here to separate the read line into discrete components of the array of our CSV file for reading the file.
Close #1
This closes the input line statement.
Method 4 – Use the VBA Macro Command Button to Read a CSV File
STEPS:
- Go to the Developer tab from the ribbon.
- Under the Controls category, click the Insert drop-down menu.
- Select the command button from the ActiveX Controls.
- Place the button in your desired place in the worksheet.
- Click on the button. This will take you to the Visual Basic Editor.
- Insert the following VBA code.
VBA Code:
Private Sub CommandButton1_Click()
Dim a, mfile As String
mfile = "C:\Exceldemy\Employee Details.csv"
With GetObject(mfile).Sheets(1)
a = .Range(.Cells(1, 1), .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
.Parent.Close
End With
End Sub
- Run the code by clicking the RubSub button or using the F5 keyboard shortcut.
VBA Code Explanation
With GetObject(mfile).Sheets(1)
a = .Range(.Cells(1, 1), .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
.Parent.Close
End With
The block will read every cell of the array from the CSV file.
Read More: Excel VBA to Convert CSV File to XLSX
Download the Practice Workbook
Not impressed with your code. In “Excel VBA to Read Line by Line of CSV File into Array” – single letter variables (l, 1, I ???) , non- meaningful variable names, “row” actually means “row_count”. It costs nothing to use readable and meaningful variable names – so why not do it?
file_no = FreeFile
Open file_name For Input As #file_no
text_data = Input$(LOF(file_no), file_no)
Close #file_no
arr_file = Split(text_data, ROW_DELIMITER)
row_count = UBound(arr_file)
arr_header = Split(arr_file(0), COL_DELIMITER)
col_count = UBound(arr_header)
ReDim arr_data(row_count, col_count)
For row_index = 0 To row_count
If Len(arr_file(row_index)) > 0 Then
arr_row = Split(arr_file(row_index), COL_DELIMITER)
For col_index = 0 To col_count
arr_data(row_index, col_index) = arr_row(col_index)
Next col_index
End If
Next row_index
Hello, JEFF WATKINS!
Yeah! It’s a bad practice, I know!
I will further keep that in mind.
That’s great, you noticed and explain this more specifically.
Thank you so much!