[RESOLVED] mdb database to csv file

[JohnCloud]

My program needs to export its database to a csv file. Ive done this in .net I used the listview as the source of the csv file. But I cant seem to find a way to do this in VB6.

Any way is acceptable, I just really need to export my .mdb to .csv file on button click

Thanks a lot!

[dee-u]

VB6 also has a listview, try searching for exporting data from listview to excel.

[Doogle]

Where you say '.mdb' do you mean every Table in the Database or just one particular Table?. If the latter, I don't see the need for a ListView

Private Sub Command1_Click()
'
' Assumes an Open ADODB Connection named 'con'
'
Dim rs As ADODB.Recordset
Dim strOut As String
Dim strSQL As String
Dim intFile As Integer
Dim strTable As String
Dim strFile As String
Dim lngI As Long
Set rs = New ADODB.Recordset
strTable = "MyTable"                         'this is the table to export to .csv
strFile = "C:\MyDir\" & strTable & ".csv"    'this is the file to be created
intFile = FreeFile
Open strFile For Output As intFile
strSQL = "SELECT * FROM " & "[" & strTable & "]"
rs.Open strSQL, con, adOpenStatic, adLockOptimistic
If Not (rs.EOF And rs.BOF) Then
    '
    'if you don't want the column names as the first record in the .csv file
    ' then remove this code
    '
    For lngI = 0 To rs.Fields.Count - 1
        strOut = strOut & rs.Fields(lngI).Name & ","
    Next lngI
    strOut = Left$(strOut, Len(strOut) - 1)
    Print #intFile, strOut
    strOut = vbNullString
    '
    ' end of column name output code
    '
    Do
        For lngI = 0 To rs.Fields.Count - 1
            strOut = strOut & rs.Fields(lngI).Value & ","
        Next lngI
        strOut = Left$(strOut, Len(strOut) - 1)
        Print #intFile, strOut
        strOut = vbNullString
        rs.MoveNext
    Loop Until rs.EOF
    MsgBox "Table " & strTable & " has been exported to " & strFile
Else
    MsgBox "Table " & strTable & " is Empty"
End If
Close intFile
rs.Close
Set rs = Nothing
End Sub

[JohnCloud]

Thanks Doogle, I just need to export a particular table, I will try your code and get back to this thread as quick as I can. Thank you!

[MarkT]

You may want to lookup the recordset getstring function

[dilettante]

You don't need any controls to do this. Even a VB6 program with no forms at all can be used for this kind of thing.

One of the faster to write and quite versatile options is to use the Jet 4.0 Text IISAM, which works much like the older ODBC Text Driver. These can use default options for the text files involved or you can provide more detailed control over the format of the text files by using a [tt]schema.ini[/file]. It isn't perfect but it does work very well and should already be part of your toolkit of VB6 kowledge. The plus is that in most cases you can even write a quick WSH script in VBScript to do this with no performance impact.

This even works from VB6's retarded cousin VBA.

Saying you want to export as "CSV" doesn't tell us much, since there are many options. Header row or not? Quote string fields or not? What date format is needed?


Once you have a connection opened to your database exporting is as easy as:

            DBConn.Execute "SELECT * INTO [Text;Database=" _
                         & App.Path _
                         & "].[Export1.csv] " _
                         & "FROM [Table]", , _
                           adCmdText Or adExecuteNoRecords

The attached demo creates an empty MDB with one table and imports a supplied text file into it. Then it displays this in a flegrid (just for grins), and then it exports the table in two different CSV formats.

See:

Schema.ini File (Text File Driver) for some of the basics, and:

Processing Text Databases for a tutorial and very useful download SchemaNotes.rtf.

[JohnCloud]

dilettante, Thanks for your comprehensive solution, I really appreciate it. The issue is now resolved with better result than anticipated. Thanks!

[JohnCloud]

dilettante, I have one more question, with your way of exporting to csv, how can I exclude the column name on the first line of the exported file.

[dilettante]

The column header row can be excluded by specifying ColNameHeader=False in your schema.ini file. The example project I posted above does this for the Export1.csv file it creates.

This is also covered in the notes and docs I linked to above.

[karthick200]

Hi I need to export all the tables from the mdb file.Can any one help me on this

[SamOscarBrown]

Hi I need to export all the tables from the mdb file.Can any one help me on this

Please start your OWN thread. This one is already Resolved--not appropriate to 'steal' another's. In the meantime, search the forum.