May 4, 2020

Automating Analysis of CSV Files with ADO SQL

How to automate the analysis of CSV files with ADO and SQL using VBA.

Automating Analysis of CSV Files with ADO SQL

What is a CSV file? CSV stands for comma separated values. Here is a picture of a CSV file.

CSV File Image
CSV File Image (cdsportfolio.csv)

CSV files can be used by applications like Notepad, Microsoft Excel and Google spreadsheets, for example. When opened with a spreadsheet application CSV files display only one worksheet. No formulas are saved in this format. They can be used to export large amounts of data to a database quickly. As the image shows CSV files are plain text files. They can be used on any computer and can be imported into a database quickly and easily. CSV files don’t have a special format like a Word document which helps to convert them into other formats easily.

What is ADO?

The full form of ADO is ActiveX Data Objects. ADO is Microsoft’s Client-Server technology to access the data between Client and Server. ADO takes help of OLE DB Provider to communicate with the data source. OLE Database (OLE DB) is a programming interface for data access from Microsoft. Applications can use OLE DB to access data sources directly or they can use OLE DB to call ODBC to access ODBC databases. ODBC or Open Data Base Connectivity is a general purpose provider. With help of this, ADO can access any Data source which can understand ODBC. ODBC permits maximum interoperability, which means a single application can access different Database Management Systems or DBMS.

What is SQL?
SQL Stands for Structured Query Language. ADO use SQL commands to communicate with the databases.
SELECT command is used to retrieve the data from a data source.

Databases are mostly stored in tables with rows functioning as records and field or columns functioning as headers. An Excel workbook can act as a data source. The worksheet becomes a table and the rows and the columns of the worksheet function as records and the fields of the table. The process of analyzing CSV files into Excel using ADO involves the following steps:

  1. First we open the connection to the Data Source
  2. Next we need to run the relevant SQL command
  3. We copy the resulting record set or rows into our worksheet
  4. Finally we close the record set and connection

Here’s the complete VBA code to automating analysis of CSV files with ADO and SQL:

Sub CSVDataAnalysisWithADO()

Dim sSQLString As String
Dim Conn As New ADODB.Connection
Dim myrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String

DBPath = “C:\zerodha\”

sconnect = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & DBPath & “;Extended Properties=’text;HDR=YES;FMT=Delimited’;”

Conn.Open sconnect

sSQLString = “SELECT * from [cdslportfolio.csv] where ([ISIN Name] like ‘H%’ or [ISIN Name] like ‘S%’) and [Value]>300000”

myrs.Open sSQLString, Conn

ActiveSheet.Range(“A2”).CopyFromRecordset myrs


End Sub

Further reading:

ADO in Excel