How To: Format exported data in a text file

[Pistol]

I am tryinng to export one record at a time from a query into a text file that has to be in a certain format in order to be used by another program. Here is the example: When I export I need the column headers to be the row, and the txt file to be in a list format. Instead of having the following:

NameFirst NameLast Phone
John Doe 5555555555

or comma seperated values, or tab deliminated values. I need to have the following:

NameFirst: John
NameLast: Doe
Phone: 5555555555

I have tried messing with the "schema.ini" file, and have tried exporting a formatted report with no luck. Any help would be appreciated.

If it is easier to understand. I really want to specifically format a text file like a template. As an example if you were programming in COBOL you could format the output based on the input. You can declare how many lines accross, and how many lines down the page will hold. You can also define the lables, and where the data should appear. When you compile the program it merges the data into the format of the report, and you get exactly what you told it to do. Is this at all possible in Access with or without the help of VBA.

Thanks

Peter McLernon

 

[THoey]

I think you are going to need to do this manually. Look up "Writing Data to Files" in help. Then in code you could write out your headings, concatenated with your data and then put a CR/LF at the end of each line. Terry M. Hoey

 

[MichaelRed]

I vote with Terry.

On the otherhand, this is to trivial to not go ahead and 'just do it'.

The following accepts a table name in the current db and ~~ prints the output described by Peter McLernon. I included a default output path which should exist on all Win platforms.

Public Function basSpecExport(tblName As String, _
                              Optional DestPath = "C:\My DOcuments\") _
                              As Boolean

    'Usage: ? basSpecExport("tblProd", "C:\MsAccess")

    'Print the table field names and the records in column Order
    'Michael Red 10/24/2001


    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset

    Dim Idx As Integer
    Dim fldNames() As String
    Dim dtaVals() As Variant
    Dim fldCount As Integer
    Dim filNum As Integer
    Dim MaxFldLen As Integer

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(tblName)
    Set rst = dbs.OpenRecordset(tblName, dbOpenDynaset)
    
    'Collect Field Names from the Table Def
    ReDim fldNames(0)
    For Idx = 0 To tdf.Fields.Count - 1
        fldNames(Idx) = tdf.Fields(Idx).Name
        ReDim Preserve fldNames(Idx + 1)
    Next Idx
    fldCount = Idx

    'Find Longest field Name (for pretty print)
    For Idx = 0 To fldCount
        If (Len(fldNames(Idx)) > MaxFldLen) Then
            MaxFldLen = Len(fldNames(Idx))
        End If
    Next Idx

    If (right(DestPath, 1) <> &quot;\&quot;) Then
        DestPath = DestPath & &quot;\&quot;
    End If

    filNum = FreeFile
    Open DestPath & tblName & &quot;.txt&quot; For Output As #filNum
    
    Do While Not rst.EOF
        For Idx = 0 To fldCount - 1
            Print #filNum, Left(fldNames(Idx) & Space(MaxFldLen), MaxFldLen) & &quot;: &quot; & rst.Fields(Idx)
        Next Idx
        Print #filNum, vbCrLf
        rst.MoveNext
    Loop

    Close #filNum
    Set rst = Nothing
    Set tdf = Nothing
    Set dbs = Nothing

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over