Read Csv Data by VBA

Ever got a csv file with large data?

It is quite painful when we got larger data in excel, Our fav excel just cant handle larger data on worksheet and perform filteration, formulation and other things you know.

There was scenario where we downloaded a csv file thorough an API, That csv file is having 250K rows of data. but we just needed some certrain rows out of this data in our output based on a criteria.

Though, Array can work on this much data. but still slower when you have to loop and get filtered data. This artical shows how you can use ADODB Connection to read csv file.

What do we need?

We need an ADODB Connection and ADODB Recordset object to read csv files.

ADODB is ActiveX data object for Database object, which can connect with any database by read the parameter in the connection string. ADODB recordset is the object where the data will get stored after it is read.

How to do it?

Read my earlier post about how to create Adodb objects

objConnection.Provider = “Microsoft.Jet.OLEDB.4.0”
objConnection.ConnectionString = “Data Source=” & strFolderName & “;” & “Extended Properties=””text;HDR=Yes;FMT=Delimited;”””

If objConnection.State = 1 Then
     Set objRecordSet = CreateObject(“ADODB.Recordset”)
     strQuery = “Select * From ” & strFileName
     objRecordSet.Open strQuery, objConnection
     Sheet1.Range(“rngStart”).Offset(1).CopyFromRecordset objRecordSet
     MsgBox “Done”, vbInformation
End If

in the connection string above, Notice that we are providing the folder path where the csv are saved. objConnection will open a connection with that folder.

We can create a query (see strQuery variable) to specify which csv file we need to read data from. this query can be anything, you can use WHERE clause to filter your data.

After Creating query, we opened the recordset and reading and executing the query by providing the connection object. Once data is in the recordset object, we paste it on the sheet1 rngRange reference.

Bottom Line

ADODB is the faster way to read large data, Generally we open files in VBA an get data in array. do operation and convert that into our output. but i would suggest to filter data by using ADODB and then use that data further in the program to make faster program.

Download working files from here

Read csv files data (79 downloads)