Opening Comma Separate File (CSV) through ADO

Sort CSV File through Excel VBA ADO/ Open CSV File using ADO

CSV files are easy way of storing data apart from XML files. There are many times we need the data from CSV file to be extracted in a specific format / specific data alone to be extracted. In such cases we can use ADO to extract restricted data using SQL Query

The main changes in connection string from using Access to using CSV would be the following :

Source=c:\temp\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)""

Source : Folder where the file exists

Extended Properties: Text (denoting Text File), HDR =Yes (If Header is present in the Text File),

FMT=Delimited(,) (denoting comma delimiter)

The following would be the SQL Query

sQuery = "Select * From VBA.csv ORDER BY ID"

We have only specified the folder name in the connection string and here we specify the file name (VBA.csv)

The following code needs reference to Microsoft ActiveX Data Objects library

Sub Open_Sort_CSV()

Dim cN As ADODB.Connection '* Connection String

Dim RS As ADODB.Recordset '* Record Set

Dim sQuery As String '* Query String

On Error GoTo ADO_ERROR

' -----------------------------------------

' Coded by Shasur for vbadud.blogspot.com

' -----------------------------------------

cN = New ADODB.Connection

cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"

cN.ConnectionTimeout = 40

cN.Open()

RS = New ADODB.Recordset

sQuery = "Select * From VBA.csv ORDER BY ID"

RS.ActiveConnection = cN

RS.Source = sQuery

RS.Open()

If RS.EOF <> True Then

While RS.EOF = False

Open "c:\temp\vba_sorted.csv" For Append As 1

Print #1, RS.Fields(0) & "," & RS.Fields(1)

RS.MoveNext()

Close #1

End While

End If

If RS.State <> adStateClosed Then

RS.Close()

End If

If Not RS Is Nothing Then RS = Nothing

If Not cN Is Nothing Then cN = Nothing

ADO_ERROR:

If Err <> 0 Then

Debug.Assert(Err = 0)

MsgBox(Err.Description)

Resume Next

End If

End Sub

Sorting Text files (comma separated) is not that easy. One option is to open the file in Excel, sort the columns and save it back (Refer ). However, this cannot be done if the rows are more than that Excel can accomodate in that case you can use ADO to sort the file and write the file back to CSV

Common Errors

You will get the following error if the specified file is not available “The Microsoft Jet database engine could not find the object 'VBA.csv'. Make sure the object exists and that you spell its name and the path name correctly.”

ADO Error :- Query must have at least one destination field

The above error occurs when there are no records in the Text file

ADO Error :- Operation is not allowed when the object is closed.

The above error occurs when some operations are done on closed recordset etc











ADO Connection string for Text File, ADO Connection string for CSV File, Excel VBA ADO for Text Files, ADO Text File Connection String, Connection String CSV File ADO, Open Text File using ADO, Sort Text File using ADO, Open and Sort Text Files using ADO,

See also:

C# CSV to XML Conversion, CSV to XML Conversion using LINQ

Insert CSV File to Array using C#