Saving a recordset as a .CSV file

[rickkcir]

I'm trying to save a recordset I have created into a Comma-Separated Values (.csv) file. This is the format I need the data in to upload it into another application.

Here is my code so far:

rs.save ("C:\test.csv")

This saves the data, I believe, but it is in a very crude, and unusable format.

I know the code I have isn't a lot, but I can't seem to find much documentation on this, and I haven't had any luck searching this forum.

Any help is greatly appreciated.

Thanks.

 

[dilettante]

There is no clean way to "write a recordset as a CSV file."

There are several options for persisting an ADO Recordset though, the Save method being one of them.

By default you are saving in the proprietary ADTG format. You can also save in an ADO XML persistence format or by using some types of providers an HTML format.

Nothing close to CSV though.


What you can do is to use the Jet OLEDB provider (for example) which supports CSV files via the Text engine type. There is also a Text ODBC driver which operates almost identically.

But you would need to initially open the recordset with a SQL CREATE statement and then add data to it while open. I don't think this scenario supports anything like using a disconnected recordset and then later reconnecting it and flushing the data.

You use a connection string on the order of:

strConn = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & App.Path & ";" & _
    "Extended Properties=""Text;HDR=NO;FMT=Delimited"""

The "Data Source" is the directory you want to create the CSV file in.

Create the CSV file using something on the order of:

rs.Open "CREATE TABLE [myData.CSV] " & _
          "(LastName TEXT(17), FirstName TEXT(17))", _
          strConn, adOpenStatic, adLockOptimistic

 

[rickkcir]

Thanks very much for your response Dilettante. I am definitely headed in the right direction I believe.

However, when I try to run the code, I get a Run-Time error '3709' -- The connection cannot be used to perform this operation. It is either closed or invalied in this context.

Any ideas on why it doesn't like the connection?

Thanks so much.

 

[rickkcir]

Nevermind...I was trying to call the wrong recordset with this connection. My mistake. Thanks for the help.

 

[Swi]

Here is another method:

http://www.freevbcode.com/ShowCode.asp?ID=2073

Swi