Excel VBA to Read CSV File into Array (4 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

While working with the dataset, we occasionally receive data in CSV format. A CSV file is a text file with commas separating information. However, the CSV format is not very intuitive, whether we open it in Excel, Notepad, or any other Text application. A CSV file is mainly used not to disrupt anyone’s work. But many times we may need to read the CSV file using VBA in Microsoft Excel. In this article, we will demonstrate some different examples to read a CSV file into an array using Excel VBA.


What Is CSV File?

A CSV file is a comma-separated values file, which allows for saving data in an organized format. CSVs take after traditional worksheets but come across the .csv suffix. CSV files are compatible with many of these worksheet programs, including Microsoft Excel and Google Spreadsheet applications. This is a text file with commas separating information. Spreadsheets and database systems are the most common places to find CSV files. A CSV file is a simple text document that can be opened in several programs, including Notepad, TextEdit, and other tools that operate with text.

As the name indicates, a CSV file uses commas to divide data. It is a mechanism for applications that can not understand one another immediately to share data sets, such as the elements of a database.


Read CSV File into Array with VBA: 4 Ideal 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, Birth Date. And, in each row, there are some employee details following the column structure. Now, we will read the data from the CSV file into an array.

4 Different Examples to Read CSV File into Array

Suppose, we save our CSV file into the path shown in the picture below. We name our CSV file Employee Details. While working with the VBA code to read that file we need that specific file path, and the location for the file is “C:\Exceldemy\Employee Details.csv”. Now, let’s illustrate four different examples for reading a CSV file into an array.

4 Different Examples to Read CSV File into Array


1. Read Entire Line of CSV File into Array Using Excel VBA

VBA Macros are Excel programming platforms that employ the Visual Basic Application to indicate user-generated tasks and simplify manual activities. VBA uses macros to simplify common everyday chores that are less repetitive. It is a useful tool in Excel for programmers to do the work faster. With this, we can read CSV files into an array. Let’s go along with the steps to use the VBA to read the CSV file into an array.

STEPS:

  • Firstly, we need to open the Visual Basic Editor to do the codes there. For this, go to the Developer tab from the ribbon in an Excel workbook.
  • Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.
  • Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

4 Different Examples to Read CSV File into Array

  • This will appear in the Visual Basic Editor where we write our codes to create a table from range.
  • Thirdly, click on Module from the Insert drop-down menu bar.

  • This will create a Module in your workbook.
  • And, copy and paste the VBA code shown below.

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.


2. Excel VBA to Read Line by Line of CSV File into Array 

Let’s see another example to read a CSV file into an array.

STEPS:

  • Similarly, in the earlier example, go to the Developer tab from the ribbon.
  • Second, click on Visual Basic to open the Visual Basic Editor.
  • Another way to open the Visual Basic Editor is simply to press Alt + F11.
  • Or, right-click on the sheet, then select View Code.
  • Next, go to Insert and select Module from the drop-down menu.
  • And, this will open up the visual basic window.
  • After that, copy and paste the VBA code below.

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
  • Further, press the F5 key or click on the Run Sub button to run the code.

4 Different Examples to Read CSV File into Array

  • 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

Those 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


3. Read a CSV File into an Array with VBA Built-in Statements

Let’s see another example to read a CSV file into an array.

STEPS:

  • Likewise, in the previous examples, to begin with, open the ribbon and choose Developer from the drop-down menu.
  • Then select Visual Basic to open the Visual Basic Editor.
  • The Visual Basic Editor may also be accessed by pressing Alt + F11.
  • Alternatively, you may right-click the sheet and select View Code from the pop-up menu.
  • After that, select Module from the Insert drop-down menu.
  • Then copy and paste the following VBA code.

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
  • Finally, press the F5 key to run the code.

VBA Code Explanation

sf = "c:\data\book1.csv"

Here, it defines the file path.

ReDim arr(1)

The ReDim expression sized or resized a dynamic array.

Open sf For Input As #1

This just 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)

Again, this sized or resized 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.


4. Use VBA Macro Command Button to Read a CSV File

Let’s look at another example of utilizing Excel VBA to read a CSV file into an array. In this example, we will use a command button to read the CSV file.

STEPS:

  • In the first place, go to the Developer tab from the ribbon.
  • Then, under the Controls category, you will find the Insert drop-down menu.
  • Now, click on the drop-down menu bar and select the command button from the ActiveX Controls.

4 Different Examples to Read CSV File into Array

  • Further, this will allow you to place a command button into your spreadsheet. So, place the button in your desired place.

4 Different Examples to Read CSV File into Array

  • Furthermore, click on the button. And, this will take you to the Visual Basic Editor.
  • After that, write down the VBA code there.

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
  • After that, 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 Practice Workbook

You can download the workbook and practice with them.


Conclusion

The above examples will assist you in reading CSV Files into Array in Excel VBA. I hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Have a nice day!


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

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

2 Comments
  1. 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!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo