将表导出到csv更改文件扩展名 [英] Exporting tables to csv changes file extension

问题描述

我正在尝试将我的所有表从访问数据库导出到单独的.csv文件.我有一个循环遍历所有表，并通过使用TransferText，我想为每个表创建一个.csv文件.

I'm trying to export all my tables, from my access database, to separate .csv-files. I have a loop that runs through all tables and by using TransferText I want to create a .csv-file for each table.

我可以通过编写TransferText方法来创建一个文件.

I am able to create a single file by writing the TransferText method.

DoCmd.TransferText acExportDelim, "ExportCsv", [Table name], filePath + "Test.csv", True

但是当我试图创建一个循环为每个表生成一个文件时，我遇到了麻烦. (文件路径设置为桌面)

But when I'm trying to create a loop to generate a file for each table I get into trouble. (Filepath is set to desktop)

' Loops through all tables and extracts them as .csv-files    
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
    ' ignore system and temporary tables
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        ' Export table as CSV
        'MsgBox (tdf.Name)
        fileName = tdf.Name & ".csv"
        DoCmd.TransferText acExportDelim, "ExportCsv", tdf.Name, filePath + fileName, True

    End If
Next
Set tdf = Nothing
Set db = Nothing

这样做会给我错误'3011'，表明找不到该对象.然后，它给了我对象名称:[表名] #csv.因此出于某种原因，它会将".csv"更改为"#csv".

Doing it like this gives me Error '3011' saying it can't find the object. Then it gives me the object name: [table name]#csv. So for some reason it changes ".csv" to "#csv".

如果我从文件名中删除文件扩展名，我得到的只是错误3027，表明对象或数据库是只读的.

If I remove the file extension from the file name all I get is Error 3027 saying that the object or database is read-only.

有人知道我的问题是否有解决方案，或者有其他方法可以做同样的事情?还是我必须走一条完全不同的路线?

Does anyone know if there is a solution to my problem or another way to do the same thing? Or am I gonna have to go a completely different route?

其他经过测试的变体

DoCmd.TransferText acExportDelim, "ExportCsv", tdf.Name, "C:/tempFile.csv", True 
DoCmd.TransferText acExportDelim, "ExportCsv", tdf.Name, "C:/" & tdf.Name & ".csv", True

:出现"#csv"错误.

: Gives a "#csv" error.

DoCmd.TransferText acExportDelim, "ExportCsv", tdf.Name, "C:/tempFile", True
DoCmd.TransferText acExportDelim, "ExportCsv", tdf.Name, "C:/" & tdf.Name, True 

:给出只读错误

推荐答案

因此，在经过反复试验后，我找到了一种适用于我的方法.

So after lots of trial and error I have found a way that works for me.

在@Gustav的启发下，我开始创建.xls文件，由于某种原因，该文件可以工作.然后使用自定义脚本将这些文件转换为.csv文件.然后，我删除.xls文件，只剩下我的.csv文件.

With some inspiration from @Gustav I went with creating .xls files, which for some reason works. And then convert those files with a custom script to .csv-files. Then I remove the .xls files leaving only my .csv-files left.

所以我的循环现在看起来像这样:

So my loop now looks like this:

For Each tdf In db.TableDefs
    ' ignore system and temporary tables
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        ' Export as xls-files
        fileName = tdf.Name & ".xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, tdf.Name, filePath & env & fileName, True

        ' Convert xls-files to .csv and remove the xls-files.
        ConvertXls2CSV (filePath & env & fileName)
        VBA.Kill filePath & env & fileName
    End If
Next

这是转换代码:(贷给:

And here is the converting code: (Credit to: https://www.devhut.net/2012/05/14/ms-access-vba-convert-excel-xls-to-csv/)

Function ConvertXls2CSV(sXlsFile As String)
    On Error Resume Next
    Dim oExcel          As Object
    Dim oExcelWrkBk     As Object
    Dim bExcelOpened    As Boolean    'Was Excel already open or not
    'Review 'XlFileFormat Enumeration' for more formats
    Const xlCSVWindows = 23 'Windows CSV Format
    Const xlCSV = 6 'CSV
    Const xlCSVMac = 22 'Macintosh CSV
    Const xlCSVMSDOS = 24 'MSDOS CSV

    Set oExcel = GetObject(, "Excel.Application")    'Bind to existing instance of Excel

    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        'On Error GoTo Error_Handler
        Set oExcel = CreateObject("excel.application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If

    'On Error GoTo Error_Handler
    oExcel.ScreenUpdating = False
    oExcel.Visible = False   'Keep Excel hidden from the user
    oExcel.Application.DisplayAlerts = False

    Set oExcelWrkBk = oExcel.Workbooks.Open(sXlsFile)
    'Note: you may wish to change the file format constant for another type declared
    'above based on your usage/needs in the following line.
    oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) & "csv", xlCSVWindows, Local:=True
    oExcelWrkBk.Close False

    If bExcelOpened = False Then
        oExcel.Quit
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set oExcelWrkBk = Nothing
    Set oExcel = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ConvertXls2CSV" & vbCrLf & _
            "Error Table: " & sXlsFile & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit

End Function

这篇关于将表导出到csv更改文件扩展名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！