如何以 CSV 格式导出 Access 数据库? [英] How do I export an Access database in CSV format?

问题描述

我有一个 Access 数据库，我想将其导出为文本文件.我在 Access 中定义了一个架构，目前使用宏来导出它.我想使用 VBScript 始终将查询结果附加到同一个文件中.如果无法使用我定义的模式，我只需要将字段用逗号分隔并用 " 括起来，并且文本文件必须是 UTF-8格式.

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

推荐答案

DIRECTION (2)这是一些 VBA，从 Access 数据库运行:

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

这是同样的事情，但在 VBScript 中，使用 DAO:

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;"

方向 (1)

我建议一个完全不同的方向，那就是让 MySQL 来做这项工作:

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

MySQL 迁移工具包

我对您的数据库进行了测试，它似乎可以正确导入，只需几分钟，并且会生成各种可重复使用的脚本等.

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.

如果您在设置 MySQL 时遇到问题，您可能希望阅读:9.1.4.连接字符集和排序规则

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

方向 (0)

重写(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 

这篇关于如何以 CSV 格式导出 Access 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！