将数据从常量附加文件分离到新文件中

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.

我使用宏将Microsoft Access数据库中的表导出到csv文件,以便导入到mysql数据库。我最终使用了一个批处理文件,它会在导出发生之前在文本文件中放置一个标记,然后将最后一个标记之后的所有内容放入一个新文件中。这种方法很好,除了访问不附加的事实,但每次都会重新创建文件,因此不可能使用任何类型的标记。

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

有没有办法,使用访问或批处理文件或其他任何方式a)强制访问附加到文件,或放置自己的标记,或b)每次导出到不同的文件,可能文件名是变量,如日期,或c)克服此行为与外部操纵

3 个解决方案

#1

Instead of using a macro to export the table you could simply create some code to open the file, and append the data to it.

您可以简单地创建一些代码来打开文件,然后将数据附加到其中,而不是使用宏来导出表。

How to use

如何使用

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

只需将代码复制到应用程序中的VBA模块,然后调用它:

' 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

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.

请注意,它并不完美,您可能需要调整代码以反映您希望如何格式化数据,但在大多数情况下,默认值应该没问题。

#2

a) force access to append to a file, or to place a marker of its own

a)强制访问附加到文件,或放置自己的标记

No. The exports is expecting to write a completely new file each time, and has problems if the file already exists.

不会。导出每次都要写一个全新的文件,如果文件已经存在则会出现问题。

b) export to a different file each time, possibly the filename being a variable such as the date

b)每次导出到不同的文件,文件名可能是日期等变量

Yes, build the path/filename up as a string, and append the date/time to the end

是的,将路径/文件名构建为字符串,并将日期/时间附加到结尾

c) overcome this behavior with outside manipulation

c)通过外部操纵克服这种行为

Well, you could dump out what's new to a dummy file, and then with a batch script or system call that does:

好吧,您可以将新文件转储到虚拟文件中,然后使用批处理脚本或系统调用执行以下操作:

echo marker >> goodfile.csv
type dummy >> goodfile.csv
del dummy

However, if you just want to add the new records, a better way over all would be to just process the dummy file instead trying to seek the last marker and process everything below that.

但是,如果您只想添加新记录,那么更好的方法就是处理虚拟文件,而不是尝试寻找最后一个标记并处理下面的所有内容。

#3

You could also use VBScript to execute the query you use to export, and append those records to an existing file.

您还可以使用VBScript执行用于导出的查询,并将这些记录附加到现有文件中。