从不断追加文件中的数据分离到一个新文件 [英] Separating data from a constantly appended file into a new file

问题描述

我使用宏来在Microsoft Access数据库中导出表到CSV文件，以导入到mysql数据库。我结束了使用批处理文件将放置一个标记文本文件中的出口发生之前，然后将最后一个标记后，一切都变成新的文件。这工作得很好，除了访问不追加的事实，但将重新创建每次该文件，因此它不可能使用任何类型的标记。

I am using a macro to export a table in a Microsoft Access database to a csv file in order to import into a mysql database. I ended up using a batchfile that would place a marker in the text file before the exporting took place, and then place everything after the last marker into a new file. This works fine, except for the fact that access does not append, but will recreate the file each time, so it is impossible to use any kind of marker.

有没有什么办法，使用访问或批处理文件或什么的，要么一）强制访问每次追加到一个文件，或将它自己的标记，或b）出口到不同的文件，可能是文件名是这样一个变量的日期，或c）克服与外部操纵这种行为

Is there any way, using access or batch files or whatever, to either a) force access to append to a file, or to place a marker of its own, or b) export to a different file each time, possibly the filename being a variable such as the date, or c) overcome this behavior with outside manipulation

推荐答案

如何使用

只需将code复制到VBA模块中的应用程序并调用它是这样的：

Simply copy the code to a VBA module in your application and call it like this:

' Export the Table "Orders" to "orders.csv", appending the data to the       '
' existing file if there is one.                                             '
ExportQueryToCSV "Orders", "C:\orders.csv", AppendToFile:=True

' Export the result of the query to "stock.csv" using tabs as delimiters     '
' and no header or quotes around strings                                     '
ExportQueryToCSV "SELECT * FROM Stock WHERE PartID=2", _
                 "C:\stock.csv", _
                 AppendToFile:=False, _
                 IncludeHeader:=False, _
                 Delimiter:=chr(9), _
                 QuoteString:=false

code

'----------------------------------------------------------------------------'
' Export the given query to the given CSV file.                              '
'                                                                            '
' Options are:                                                               '
' - AppendToFile : to append the record to the file if it exists instead of  ' 
'                  overwriting it (default is false)                         '
' - Delimiter    : what separator to use (default is the coma)               '
' - QuoteString  : Whether string and memo fields should be quoted           '
'                  (default yes)                                             '
' - IncludeHeader: Whether a header with the field names should be the first '
'                  line (default no)                                         '
' Some limitations and improvements:                                         '
' - Memo containing line returns will break the CSV                          '
' - better formatting for numbers, dates, etc                                '
'----------------------------------------------------------------------------'
Public Sub ExportQueryToCSV(Query As String, _
                            FilePath As String, _
                            Optional AppendToFile As Boolean = False, _
                            Optional Delimiter As String = ",", _
                            Optional QuoteStrings As Boolean = True, _
                            Optional IncludeHeader As Boolean = True)
    Dim db As DAO.Database
    Dim rs As DAO.RecordSet

    Set db = CurrentDb
    Set rs = db.OpenRecordset(Query, dbOpenSnapshot)
    If Not (rs Is Nothing) Then
        Dim intFile As Integer

        ' Open the file, either as a new file or in append mode as required '
        intFile = FreeFile()
        If AppendToFile And (Len(Dir(FilePath, vbNormal)) > 0) Then
            Open FilePath For Append As #intFile
        Else
            Open FilePath For Output As #intFile
        End If

        With rs
            Dim fieldbound As Long, i As Long
            Dim record As String
            Dim field As DAO.field

            fieldbound = .Fields.count - 1

            ' Print the header if required '
            If IncludeHeader Then
                Dim header As String
                For i = 0 To fieldbound
                    header = header & .Fields(i).Name
                    If i < fieldbound Then
                        header = header & Delimiter
                    End If
                Next i
                Print #intFile, header
            End If

            ' print each record'
            Do While Not .EOF
                record = ""
                For i = 0 To fieldbound
                    Set field = .Fields(i)
                    If ((field.Type = dbText) Or (field.Type = dbMemo)) And QuoteStrings Then
                        record = record & """" & Nz(.Fields(i).value, "") & """"
                    Else
                        record = record & Nz(.Fields(i).value)
                    End If
                    If i < fieldbound Then
                        record = record & Delimiter
                    End If
                    Set field = Nothing
                Next i
                Print #intFile, record
                .MoveNext
            Loop
            .Close
        End With
        Set rs = Nothing
        Close #intFile
    End If
    Set rs = Nothing
    Set db = Nothing
End Sub

请注意，这不是完美的，你可能有适应code，以反映要如何对数据进行格式化，但是默认值应该是在大多数情况下的罚款。

Note that it's not perfect and you may have to adapt the code to reflect how you want the data to be formatted, but the defaults should be fine in most cases.

这篇关于从不断追加文件中的数据分离到一个新文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！