Exporting a csv file (comma separated)

micharl · May 5th, 2008, 04:02 AM

Good day to all!

Got another question to post here, I hope the senior and advance member here will read this. How can I export the data that I showed in my Datagrid as csv file (comma separated). This will help me to show large number of data to view excel or notepad.

Best Regards,
Micharl

BrianWren · May 5th, 2008, 12:06 PM

I don't know that the datagrid can be used this way, but exporting these data is not hard.

Open a recordset in code, open a file to receive the output as write (not append), then, in a loop (Do Until rs.EOF = True / Loop) construct a line to go the the file in a string variable, the write it out to the file.

It's not much code to write, you will have intimate control over the formatting of the outpu, it will run fast, and so on.

micharl · May 7th, 2008, 04:27 AM

Brian, is it ok to give me an example on how to do this? I'm trying to get the data from the database server and show it to datagrid. After that, the output data should be save as .csv file. Thank you so much in advance. I hope you can give time to this.

Quote:

quote:Originally posted by BrianWren
I don't know that the datagrid can be used this way, but exporting these data is not hard.

Open a recordset in code, open a file to receive the output as write (not append), then, in a loop (Do Until rs.EOF = True / Loop) construct a line to go the the file in a string variable, the write it out to the file.

It's not much code to write, you will have intimate control over the formatting of the outpu, it will run fast, and so on.

pons_saravanan · May 7th, 2008, 08:50 AM

hi

First load all data to a collection something like

dim col as new collection()

loop thro the source(recordset or dataset) and add the comma seperated string to

col.add(rs(1) & "," rs(2)....)

then pass the collection to the function (can be taken from here)
http://www.vbknowledgebase.com/WebAp...n-To-Text-File

in the function if you dont want to have the overwrite functionality remove this line
If Not IsOverWrite Then strContent = Text2String(strPath)

Col2Text col,"c:\myexport.csv",0

if you need further assistance please dont hesitate

Pon Saravanan

BrianWren · May 7th, 2008, 02:32 PM

Add the Microsoft Windows Scripting Host to your project as a reference. It make working with files easier.

Add a reference to Microsoft ActiveX Data Objects

Create a connection to your data. I am using Oracle, so for me it is:

Code:

    Dim c    As ADODB.Connection
    Set c = New ADODB.Connection

    c.Provider = "MSDASQL"              ' OLE-DB Provider for ODBC.
                                        ' My DB is named DIS...
    c.ConnectionString = "DRIVER={Microsoft ODBC for Oracle};" & _
                         "SERVER=DIS;" & _
                         "UID=USERNAME;" & _
                         "PASSWORD=SecrEt$tring;"
    c.ConnectionTimeout = 10
    c.Open

Then you use the connection to open a recordset:

Code:

    Dim r As ADODB.Recordset

Code:

    r.Open "SELECT FIELD1, DATASTUFF, DATES_OF_FUN " & _
           "FROM   THE_TABLE_NAME " & _
           "WHERE  DATES_OF_FUN = " Now(), _
           c

Open the output file:

Code:

    Dim fso As New FileSyTemObject

Code:

    Dim ts  As TextStream
    Set ts = FSO.CreateTextFile("C:\Temp\OutPut\TheData.csv", False, False)

Now youâre ready to spit out the data:

Code:

    Do Until r.EOF

Code:

        ts.WriteLine """" & r!FIELD1 & """," & _
                            r!DATASTUFF & "," & _
                            r!DATES_OF_FUN 
        r.MoveNext
    Loop

When you are all done:

Code:

    ts.Close

Code:

    Set ts = Nothing
    Set fso = Nothing
    r.close
    Set r = Nothing
    c.close
    Set c = Nothing

BrianWren · May 7th, 2008, 02:35 PM

By the way, introducing a Collection to this process will slow your code down, and will not provide any real benefit. If, at the point Pon Saravanan suggested adding the data to the Collection you add it to the file instead, then you are doneâwithout having to further process the Collection.

micharl · May 7th, 2008, 10:10 PM

I got it! Thank you so much Pon & Brian, I combine the idea of the examples that you gave me. Again, thank you so much!!!

Best Regards,

Micharl