Splitting data from a constantly added file to a new file

Question

I am using a macro to export a table in a Microsoft Access database to a csv file to import into a mysql database. I ended up using a batch file that put a marker in a text file before exporting, and then put everything after the last marker in a new file. This works great, except that access is not added, but will recreate the file each time, so you cannot use any marker.

Is there any way using access files or batch files or something else: a) force access to add to the file or put its own marker or b) export to another file every time, maybe filename is a variable such as date, or c) overcome this behavior with external manipulation

Answer 1

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

How to use

Just copy the code into the 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 

Note that this is not ideal, and you may have to adapt the code to display how you want to format the data, but in most cases the default values ​​should be good.

Answer 2

a) force access to the file or put its own token

No. The export expects to write a completely new file each time and will have problems if the file already exists.

b) export to another file each time, perhaps the file name is a variable, for example, date

Yes, create the path / file name as a string and add the date / time to the end

c) overcome this behavior through external manipulation

Well, you can upload what's new for the 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 new entries, it is best to process the dummy file, instead try to find the last marker and process everything below it.

Answer 3

You can also use VBScript to execute the query that you use to export, and add these entries to an existing file.