Export each row from a table to a separate txt file

[greg88rx]

I have a database with information describing the contents of a group of MIDI files. I need to export the data from this database into an individual txt file for each row in the table. I need to do this because my ID3 tagging software requires a specific format in order to tag MP3 files created from the MIDI files.

My column headers are as follows:
MIDI, TIT2, TIT3, TCOM, TPE1, TALB, TCON, TYER
where MIDI is the filename without the ".mid" file extension.

The text file must be named
[MIDI].tags.txt

The contents of the file must be formatted as follows:
TIT2=[TIT2]
TIT3=[TIT3]
TCOM=[TCOM]
TPE1=[TPE1]
TALB="Peter's MIDI AMPICO Popular"
TCON="AMPICO Popular"
TYER=[TYER]

Where text shown in [] is the name of the column header in my database from which the text should be printed in the txt file for the individual row.

Any help/ideas are appreciated.

 

[arnelgp]

Use transfertext method of docmd object.

 

[The_Doc_Man]

If you meant that you need one file for EACH ROW of output, you need to write some VBA code to manage this.

In essence, you would open a recordset to your source. You would move to the first record of that source.

In a loop, you would create a blank string for output. Create a file of the required name, which you say is in the first field of each record - hint: OPEN <filename> FOR OUTPUT AS FILE n. Then use PRINT n, "TIT2="; recordset![TIT2] (and so on) for each field in the recordset. When you are finished with the record, close the file first. Then check to see if you are now at the end of the file (test for recordset.EOF = TRUE). If not at End of File, continue the loop. If at end of file, close the recordset. You already closed the individual text files.

 

[arnelgp]

here is a subroutine that you can use.
paste it in a Standard Module.

Public Sub subRecordsToTextFile(Optional ByVal strPathToSave As String, _
                                Optional ByVal strFileName As String, _
                                Optional ByVal strExtension As String = ".txt", _
                                Optional ByVal intSequence As Integer = 0)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' paramaters (optional)
'
'   strPathToSAve           the path where the export will be placed (eg. "d:\Output\"). default folder of the db
'   strFileName             the filename to create without extension. default, "Text"
'   strExtension            extension for filename, default ".txt"
'   intSequence             sequence of files to create, default 0, (eg Text00.txt, Text01.txt, Text02.txt, ...)
'
'
' NOTE:
'
'    Put the Table name to the TABLE_NAME constant variable
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim lngFile As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim FileName As String
    ' put the name of the table to export below
    Const TABLE_NAME As String = "TableHere"
    
    ' use default values
    If strPathToSave = "" Then strPathToSave = CurrentProject.path & "\"
    If strFileName = "" Then strFileName = "Text"
    
    ' open table
    Set db = CurrentDb
    Set rs = db.OpenRecordset(TABLE_NAME, dbOpenSnapshot)

    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            
            ' build filename here
            FileName = strPathToSave & strFileName & Format(intSequence, "00") & strExtension
            
            ' check if file already exists and overwrite it without warning
            If Dir(FileName) <> "" Then Kill FileName
            
            ' get a handle for a new text file
            lngFile = FreeFile
            
            ' open new file for output
            Open FileName For Output As #lngFile
            
            ' put the record to the file
            Print #lngFile, "T1T2=" & Chr(34) & .Fields("TIT2").Value & Chr(34)
            Print #lngFile, "TIT3=" & Chr(34) & .Fields("TIT3").Value & Chr(34)
            Print #lngFile, "TCOM=" & Chr(34) & .Fields("TCOM").Value & Chr(34)
            Print #lngFile, "TPE1=" & Chr(34) & .Fields("TPE1").Value & Chr(34)
            Print #lngFile, "TALB=" & Chr(34) & "Peter's MID AMPICO Popular" & Chr(34)
            Print #lngFile, "TCON=" & Chr(34) & "AMPICO Popular" & Chr(34)
            Print #lngFile, "TYER=" & Chr(34) & .Fields("TYER").Value & Chr(34)
            Close #lngFile
            
            ' increase sequence number
            intSequence = intSequence + 1
            
            ' move to next record
            .MoveNext
        Wend
        ' close recordset
        .Close
    End With

CleanUp:
    'clean up
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
ErrSub:
    MsgBox Err.Number & " : " & Err.Description
    Resume CleanUp
End Sub

 

[greg88rx]

Thanks for the replies. I appreciate the time and effort.

Assuming I can determine how to implement this in Access, I have a question for arnelgp.
It looks like that will name the files with a numerical sequence. I need to name the files using the data in the MIDI column in my table with the extension ".tags.txt" I can see where to alter the code to change the extension in Line 3. To alter the file name, in Line 29 would I enter ".Fields("MIDI").Value" where you've typed "Text"?

I don't want any numbering sequence to be included in the file names, so should I omit Lines 4 and 61, 62?

I acknowledge I'm a fledgeling coder at best and again, I really appreciate the help.

 

[arnelgp]

hello, you insert the filename here:

    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
	    
            strFileName = .Fields("MIDI").Value
            
            ' build filename here
            FileName = strPathToSave & strFileName & strExtension


	...
	...