Solved

Simple MDB to CSV Conversion

Posted on 2001-09-08
12
360 Views
Last Modified: 2010-08-05
I just want to know 2 questions.

1) How to convert a mdb database to csv format.

2) How to make  row of the datagrid to be highlighted through vb code.

g_handa
0
Comment
Question by:g_handa
  • 6
  • 6
12 Comments
 

Author Comment

by:g_handa
ID: 6466463
asap

g_handa
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6466733
Step 1 - set up a Project Reference to DAO

Step 2 - create a command button: command1

Step 3 - paste the following code

The code will export either all tables our just one table

You will need to edit the code with your database name
and destination folder where the cvs files will end up.

Hope this helps.


Private Sub Command1_Click()

Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim FLD As DAO.Field
Dim RS As DAO.Recordset


Dim fh As Long ' file handle
Dim fldc As Long ' fieldcounter
Dim tn As String ' table name
Dim dt As String ' converted field data

'open the database
Set DB = DBEngine.OpenDatabase("C:\yourdb.mdb")

' Export all tables
For Each TD In DB.TableDefs
    GoSub ExportTable
Next

' Export just one
Set TD = DB.TableDefs("Your Table")
GoSub ExportTable

DB.Close
Set DB = Nothing

Exit Sub

ExportTable:
   
    ' open the csv file.
   
    tn = TD.Name ' table name
   
    fh = FreeFile
    Open "c:\yourfolder\" + tn + ".CSV" For Output As fh
       
    ' create field headings
    ' e.g.
    ' "Field1", "Field2", "fieldetc",
    For fldc = 0 To TD.Fields.Count - 1
       
        If fldc > 0 Then
            Print #fh, ", ";
        End If
        Print #fh, Chr$(33) + TD.Fields(fldc).Name + Chr$(33);
       
    Next
       
    Print #fh, "" ' end of heading line
   
    ' now get the data
   
    Set RS = DB.OpenRecordset("Select * from [" + tn + "];", dbOpenSnapshot)
   
    ' loop through all records
   
    Do While Not RS.EOF
       
        ' Loop through all of the fields    
        For fldc = 0 To RS.Fields.Count - 1
           
            If fldc > 0 Then
                Print #fh, ", ";
            End If
           
            Set FLD = RS.Fields(fldc)
           
            ' now write the data
            Select Case FLD.Type
                Case Is = dbText
                    If IsNull(FLD) Then
                        dt = ""
                    Else
                        dt = FLD
                    End If
                   
                    Print #fh, Chr$(33) + dt + Chr$(33);
                Case Is = dbMemo
                    If IsNull(FLD) Then
                        dt = ""
                    Else
                        dt = FLD
                    End If
                   
                    Print #fh, Chr$(33) + dt + Chr$(33);
                Case Is = dbDate
                    If IsNull(FLD) Then
                        dt = ""
                    Else
                        dt = Format(dt, "Medium Date") ' you may need to change this
                        ' e.g. DD-MMM-YY HH:NN:SS ' FOR EURO DATES AND TIMES
                    End If
                   
                    Print #fh, dt;
                Case Else ' numerics
                    If IsNull(FLD) Then
                        dt = "0"
                    Else
                        dt = CStr(dt)
                    End If
                   
                    Print #fh, dt;
            End Select
        Next
       
        Print #fh, "" ' end of row
       
        Set FLD = Nothing
        RS.MoveNext
    Loop
   
   
   
    RS.Close
   
    Set RS = Nothing
   
    Close fh
   
   
Return


End Sub
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6466757
The product docuentation skipped out on how to select a row but it can be done as follows:

YourGrid.SelStartRow=3
YourGrid.SelEndRow=3

' To select the current row

cr=YourGrid.Row
YourGrid.SelStartRow=cr
YourGrid.SelEndRow=cr

Clear the selection by setting to -1

You can also select a region by using the SelStartCol and SelEndCol

Hope this helps.
0
 

Author Comment

by:g_handa
ID: 6466931
thanks for ur fast reply. let me try to run the code.

g_handa
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6467071
You had better test it I just typed it in untested - hope it works.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6467106
I did notice a bug:

Case Is = dbDate
                   If IsNull(FLD) Then
                       dt = ""
                   Else
                       dt = Format(FLD, "Medium Date") ' This was wrong
                   End If
                   
                   Print #fh, dt;
               Case Else ' numerics
                   If IsNull(FLD) Then
                       dt = "0"
                   Else
                       dt = CStr(FLD) ' and this
                   End If
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:g_handa
ID: 6467296
Hello inthedark,

The datagrid control doesn't have the propery of selstartrow and selendrow

What should I do then.
0
 

Author Comment

by:g_handa
ID: 6467299
how the bugs r going to be removed..??
0
 

Author Comment

by:g_handa
ID: 6467305
how the bugs r going to be removed..??
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6467400
When I looked at this reference if had information on the selrowstart property.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/DBGrid98/html/vbprocol.asp

But I think these properties were superceeded by the selbookmarks collection see the following.


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/DBGrid98/html/daproselbookmarks.asp


The bookmark is a string reference to a record in a recordset

I think you can use it like this:


DataGrid1.Rpw=3 ' goto the row you want to select

DataGrid1.SelBookmarks.Add DataGrid1.Bookmark


Before adding a row you may wish to clear a previous selection:

' CLear previous selected records
Do While DataGrid1.SelBookmarks.Count > 0
    DataGrid1.SelBookmarks.Remove 0
Loop

' add the current row
DataGrid1.SelBookmarks.Add DataGrid1.Bookmark


Hope this helps.
0
 
LVL 17

Accepted Solution

by:
inthedark earned 300 total points
ID: 6467420
So here it is tested.  I added some frills.
If you need help understanding it let me know.
Have fun....

Private Sub Command1_Click()

Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim FLD As DAO.Field
Dim RS As DAO.Recordset


Dim fh As Long ' file handle
Dim fldc As Long ' fieldcounter
Dim tn As String ' table name
Dim dt As String ' converted field data

'open the database
Set DB = DBEngine.OpenDatabase("d:\$vb6\mdbs\webpages.mdb")

' Export all tables
For Each TD In DB.TableDefs
    ' don't export attched tables or System tables
    If Len(TD.SourceTableName) = 0 And UCase(Left(TD.Name, 4)) <> "MSYS" Then
       GoSub ExportTable
    End If
Next

' or Export just one
Set TD = DB.TableDefs("z Server")
GoSub ExportTable

DB.Close
Set DB = Nothing
MsgBox "Done"
Exit Sub

ExportTable:
   
   ' open the csv file.
   
   tn = TD.Name ' table name
   
   fh = FreeFile
   Open "d:\$vb6\mdbs\" + tn + ".CSV" For Output As fh
       
   ' create field headings
   ' e.g.
   ' "Field1", "Field2", "fieldetc",
   For fldc = 0 To TD.Fields.Count - 1
       
       If fldc > 0 Then
           Print #fh, ", ";
       End If
       Print #fh, Chr$(34) + TD.Fields(fldc).Name + Chr$(34);
       
   Next
       
   Print #fh, "" ' end of heading line
   
   ' now get the data
   
   Set RS = DB.OpenRecordset("Select * from [" + tn + "];", dbOpenSnapshot)
   
   ' loop through all records
   
   Do While Not RS.EOF
       
       ' Loop through all of the fields
       For fldc = 0 To RS.Fields.Count - 1
           
           If fldc > 0 Then
               Print #fh, ", ";
           End If
           
           Set FLD = RS.Fields(fldc)
           
           ' now write the data
           Select Case FLD.Type
               Case Is = dbText
                   If IsNull(FLD) Then
                       dt = ""
                   Else
                       dt = Replace(FLD, Chr$(34), "'", 1, -1)
                   End If
                   
                   Print #fh, Chr$(34) + dt + Chr$(34);
               Case Is = dbMemo
                   If IsNull(FLD) Then
                       dt = ""
                   Else
                       dt = Replace(FLD, Chr$(34), "'", 1, -1)
                   End If
                   
                   Print #fh, Chr$(34) + dt + Chr$(34);
               Case Is = dbDate
                   If IsNull(FLD) Then
                       dt = ""
                   Else
                       dt = Format(FLD, "Medium Date") ' you may need to change this
                       ' e.g. DD-MMM-YY HH:NN:SS ' FOR EURO DATES AND TIMES
                   End If
                   
                   Print #fh, dt;
               Case Else ' numerics
                   If IsNull(FLD) Then
                       dt = "0"
                   Else
                       dt = CStr(FLD)
                   End If
                   
                   Print #fh, dt;
           End Select
       Next
       
       Print #fh, "" ' end of row
       
       Set FLD = Nothing
       RS.MoveNext
   Loop
   
   
   
   RS.Close
   
   Set RS = Nothing
   
   Close fh
   
   
Return


End Sub
0
 

Author Comment

by:g_handa
ID: 6468594
you r an excellnt coder

thanks very much
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now