How do I export an Access database in CSV format?

Question

I have an Access database which I would like to export to a text file. I have a schema defined within Access, and currently use a macro to export it. I would like to use VBScript to always append the result of a query to the same file. If it is not possible to use my defined schema, I only need the fields to be comma separated and enclosed by the ", and the text file must be in UTF-8 format.

I found the following code snippet, but I am unsure how to adopt it for my needs.

db = "C:\Docs\LTD.mdb"
TextExportFile = "C:\Docs\Exp.txt"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source =" & db

strSQL = "SELECT * FROM tblMembers"

rs.Open strSQL, cn, 3, 3

Set fs = CreateObject("Scripting.FileSystemObject")

Set f = fs.CreateTextFile(TextExportFile, True)

a = rs.GetString

f.WriteLine a

f.Close

Answer 1

Perhaps the easiest way is to use [text...].filename approach:-

Dim db: db = "C:\Docs\LTD.mdb"
Dim exportDir: exportDir = "C:\Docs\" '" SO prettify does not do VB well
Dim exportFile: exportFile = "Exp.txt"

Dim cn: Set cn = CreateObject("ADODB.Connection")

cn.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source =" & db

cn.Execute "SELECT * INTO [text;HDR=Yes;Database=" & exportDir & _
   ";CharacterSet=65001]." & exportFile & " FROM tblMembers"

FileSystemObject won't help you since it doesn't do UTF-8. UTF-8 is acheived by specifying CharacterSet=65001 (65001 is the UTF-8 codepage). Note the file generated does not contain a UTF-8 BOM but the schema.ini file created will note that the CharacterSet is UTF-8.

Note this doesn't achieve your append requirements are you sure that makes sense anyway, won't you end up with lots of duplicates?

Edit:

The above is adjusted to include the UTF-8 requirement. You can simply append something like the date to create multiple snapshot files for the table.

Answer 2

I've numbered the lines for reference.

1. db = "C:\Docs\LTD.mdb"
2. TextExportFile = "C:\Docs\Exp.txt"
3. strSQL = "SELECT * FROM tblMembers"
4. Set f = fs.CreateTextFile(TextExportFile, True)

Line 1 - is the current access database file you are working with. this case it's LTD.mdb
Line 2 - is the name of the file that you are going to write/append. It's Exp.txt
Line 3 - is the sql statement that will be used to collect the data.
Line 4 - is the command to open the file to write to.

Change line 2 to the name of file you want.
Change line 3 to the table you want to use. Select * will use all the columns if you want only a couple identify them by name. select col1, col2 ... from mytable. You will want to look into using where clauses also.

Change line 4 from CreateTextFile to OpenTextFile and use ForAppending to append. MSDN VBA

I'm drawing a blank on formatting the line. One of the ways I use is modify the select statement to include commas. example select col1 & "," & col2 from mytable.

For UTF-8 (I don't have a working example) Try:

utf = new String(a, 0, a.length, UTF-8);
f.WriteLine utf;

UTF-8 VBA

Answer 3

DIRECTION (2) This is some VBA, run from the Access database:

Sub InsertRecs()
Set db = CurrentDb

'DSN=Suitable system DSN for MySQL
'Then, depending on your set up, you can incude:
'Database=DBName;
'Trusted_Connection=Yes;

'NameOfMySQLTable
strSQL = "INSERT INTO [ODBC;DSN=baywotch;].tblAuction Select * FROM tblAuction;"

db.Execute strSQL, dbFailOnError
End Sub

This is the same thing, but in VBScript, using DAO:

Dim objEngine
Dim objWS
Dim objDB
Dim db: db = "C:\Docs\baywotch.db5"

Set objEngine = wscript.CreateObject("DAO.DBEngine.36")

Set objDB = objEngine.OpenDatabase(db)

objDB.Execute "INSERT INTO [ODBC;DSN=baywotch].[tblAuction] SELECT * FROM tblAuction;"

DIRECTION (1)

I suggest a completely different direction, and that is to let MySQL do the work:

MySQL Migration Toolkit

I tested this against your database, and it appears to import correctly, only takes a few minutes, and will generate all sorts of reusable scripts and so on.

If you are having problems with the set-up of MySQL, you may wish to read: 9.1.4. Connection Character Sets and Collations

DiRECTION (0)

REWRITE (2)

'========================================================================'
'
'                   FROM: AnthonyWJones, see post ' 
'
'========================================================================'
Dim db: db = "C:\Docs\baywotch.db5"
Dim exportDir: exportDir = "C:\Docs\" '" SO prettify does not do VB well
Dim exportFile: exportFile=NewFileName(exportDir)


Dim cn: Set cn = CreateObject("ADODB.Connection")

cn.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source =" & db

cn.Execute "SELECT * INTO [text;HDR=Yes;Database=" & exportDir & _
   ";CharacterSet=65001]." & exportFile & " FROM tblAuction"

'Export file

'========================================================================'

'Support functions

Function NewFileName(ExportPath)
Dim fs 
Dim NewFileTemp

Set fs = CreateObject("Scripting.FileSystemObject")

NewFileTemp = "CSV" & Year(Date) _
    & Right("00" & Month(Date),2) & Right("00" & Day(Date) ,2) & ".csv"

a = fs.FileExists(ExportPath & NewFileTemp)

i = 1
Do While a
    NewFileTemp = "CSV" & Year(Date) _
        & Right("00" & Month(Date),2) & Right("00" & Day(Date) ,2) & "_" & i & ".csv"

    a = fs.FileExists(ExportPath & NewFileTemp)
    i = i + 1
    If i > 9 Then
        'Nine seems enough times per day to be 
        'exporting a table
        a = True
        MsgBox "Too many attempts"
        WScript.Quit
    End If
Loop

NewFileName = NewFileTemp
End Function