使用Excel VBA将CSV转换为MS Access数据库中的表

I want to be able to take a csv file like this one http://ichart.yahoo.com/table.csv?s=^GSPC, which would be located in the workbook folder and make it as a table named GSPC inside an already existing blank MS Access database. The blank database file would be located in the same folder and named tblImport.accdb. I have looked at many forum threads and found nothing simple enough for a noob like me. I'm very happy for any kind of help.

我希望能够获取像这样的csv文件http://ichart.yahoo.com/table.csv?s=^GSPC，它将位于工作簿文件夹中并使其成为一个名为GSPC的表格已经存在空白的MS Access数据库。空白数据库文件将位于同一文件夹中，并命名为tblImport.accdb。我看过许多论坛帖子，发现没有像我这样的菜鸟简单。我很高兴能得到任何帮助。

1 个解决方案

#1

The easiest way to import CSV into Access is to use DoCmd.TransferText from within an Access application session.

将CSV导入Access的最简单方法是在Access应用程序会话中使用DoCmd.TransferText。

But you want to use Excel VBA. In that case, if your MS Office installation includes Access (MSACCESS.EXE), you can use Excel VBA to automate Access and still make use of DoCmd.TransferText for easy CSV import.

但是您想使用Excel VBA。在这种情况下，如果您的MS Office安装包括Access（MSACCESS.EXE），则可以使用Excel VBA自动执行Access，并仍然可以使用DoCmd.TransferText轻松导入CSV。

I tested this module in Excel 2007. It creates the GSPC table and stores the data from table.csv in that table. If the table already exists, TransferText will simply append the CSV data. You can execute DELETE FROM GSPC before running TransferText so the table will contain only the latest CSV data.

我在Excel 2007中测试了这个模块。它创建了GSPC表并将table.csv中的数据存储在该表中。如果该表已存在，TransferText将简单地附加CSV数据。您可以在运行TransferText之前执行DELETE FROM GSPC，以便该表仅包含最新的CSV数据。

This may look a bit intimidating considering you said you're a noob. However much of the following is comments I added to guide you. The actual "guts" of that procedure is fairly short and simple.

考虑到你说你是个菜鸟，这看起来有点吓人。然而，以下大部分内容都是我为了指导您而添加的评论。该程序的实际“胆量”相当简短。

Option Explicit

Public Sub ImportCsvToAccess()
    Const cstrCsvFile As String = "table.csv"
    Const cstrDbFile As String = "tblImport.accdb"
    Const cstrTable As String = "GSPC"
    Dim strFolder As String

    '* early binding *'
    ' requires reference to Microsoft Access <version> Object Library
    'Dim objAccess As Access.Application
    'Set objAccess = New Access.Application

    '* late binding *'
    ' no reference required
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")

    ' this is useful during development
    ' in production, you may prefer to hide it (Visible = False)
    objAccess.Visible = True

    strFolder = ActiveWorkbook.Path & Chr(92)
    'Debug.Print strFolder
    objAccess.OpenCurrentDatabase strFolder & cstrDbFile, _
        Exclusive:=True

    '* early binding *'
    'objAccess.DoCmd.TransferText _
    '    TransferType:=acImportDelim, _
    '    TableName:=cstrTable, _
    '    Filename:=strFolder & cstrCsvFile, _
    '    HasFieldNames:=True

    '* late binding *'
    ' acImportDelim = 0
    objAccess.DoCmd.TransferText _
        TransferType:=0, _
        TableName:=cstrTable, _
        Filename:=strFolder & cstrCsvFile, _
        HasFieldNames:=True

    objAccess.Quit
    Set objAccess = Nothing
End Sub

#1