Saturday, September 06, 2008

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#

4 comments:

  1. Anonymous7:15 PM

    The code above doesn't even work.

    ReplyDelete
  2. Anonymous8:53 PM

    I am trying to compare two text files, with similar Headers. What is the format of giving header name in SQL statement, when you are using in SQL statment for text files?

    Ex: Select file1.txt!Field1,file2.txt!Filed1 From file1.txt,file2.txt Where file1.txt!Field1 =file2.txt!Field2

    ReplyDelete
  3. Anonymous8:32 PM

    Agreed, please test your code before posting otherwise its a waste of time viewing it.

    ReplyDelete
  4. Anonymous8:26 AM

    The code does not work because
    1. it contains a couple of "()" too much,
    2. it needs a couple of "Set" statements, and
    3. additionally "End While" should be replaced by "Wend".

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group