Trying to combine all records in database into a single row in .txt output

[cvillechopper]

I have been reading posts for a couple hours and haven't found a solution to this particular issue. Seems most that post similar questions want to combine similar records (cross tab type function). I am interested in exporting every record in my table as a non-delimited string into a .txt file.

Example:



Table1
Field1
~CLP*123456789
~CLP*122456789
~CLP*133456789

Need output to be
~CLP*123456789~CLP*122456789~CLP*133456789

Thanks
James

[alansidman]

I think that your only options are fixed width or delimited.

[ssanfu]

How many records are to be written?
You can try this:

Option Compare Database
Option Explicit

'requires a reference to
'Microsoft DAO 3.6 Object Library

'uses DAO 3.6
Public Sub ExportData()
    Dim r As DAO.Recordset

    Dim k As Integer
    Dim sSQL As String


    k = FreeFile
    Open "OutputAllRecords.txt" For Output As #k

    sSQL = "SELECT Field1 FROM Table1"

    Set r = CurrentDb.OpenRecordset(sSQL)
    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst

        Do While Not r.EOF
            Print #k, r("field1");
            r.MoveNext
        Loop
        Close #k
        r.Close
        Set r = Nothing
    End If

    MsgBox "done"

End Sub

No error handling
Text file name hard coded
Saves to a default directory.

Disclaimer: This code works for a recordset of 5 records. Your mileage may vary.

[cvillechopper]

Thanks! This is a big step forward for me. Once I get the database functional I'll end up using it to output a few hundred to several thousand records at a time.
I got that to work with my small test data set but realized I will actually have to concatenate 3 fields from Table 1 ([Combined Segments]) as part of the output since I've run into the 255 limit. Tried to figure out how to concatenate them but not having luck. I haven't used VB in a while. Any ideas? Only things I've changed are in bold below.


Option Compare Database
Option Explicit
Public Sub ExportData()
Dim r As DAO.Recordset
Dim k As Integer
Dim sSQL As String
k = FreeFile
Open "OutputAllRecords.txt" For Output As #k
sSQL = "SELECT [Field 1],[Field 2],[Field 3] FROM [Combined Segments]"
Set r = CurrentDb.OpenRecordset(sSQL)
If Not r.BOF And Not r.EOF Then
r.MoveLast
r.MoveFirst
Do While Not r.EOF
Print #k, r("Field 1",""Field 2","Field3");
r.MoveNext
Loop
Close #k
r.Close
Set r = Nothing
End If
MsgBox "done"
End Sub

How many records are to be written?
You can try this:

Option Compare Database
Option Explicit

'requires a reference to
'Microsoft DAO 3.6 Object Library

'uses DAO 3.6
Public Sub ExportData()
    Dim r As DAO.Recordset

    Dim k As Integer
    Dim sSQL As String


    k = FreeFile
    Open "OutputAllRecords.txt" For Output As #k

    sSQL = "SELECT Field1 FROM Table1"

    Set r = CurrentDb.OpenRecordset(sSQL)
    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst

        Do While Not r.EOF
            Print #k, r("field1");
            r.MoveNext
        Loop
        Close #k
        r.Close
        Set r = Nothing
    End If

    MsgBox "done"

End Sub

No error handling
Text file name hard coded
Saves to a default directory.

Disclaimer: This code works for a recordset of 5 records. Your mileage may vary.

[ssanfu]

After sleeping on it, the semi-colon might put a Tab character between the fields. Try writing a small sample to the text file, then opening the text file using Word. I didn't see any tabs in my tests, but you never know. It's been a long time since I've done this type of thing.

If there are tabs, do a search and replace, searching for the tab character and replacing it with a zero length string ("").
You might have to use a different text editor than Notepad; it sometime runs into a size limit.
I use the free Notepad++ (http://notepad-plus-plus.org/)

Here is the code:

Public Sub ExportData()
   Dim r As DAO.Recordset
   Dim k As Integer
   Dim sSQL As String
   k = FreeFile
   Open "OutputAllRecords.txt" For Output As #k
   sSQL = "SELECT [Field1],[Field2],[Field3] FROM [CombinedSegments]"
   Set r = CurrentDb.OpenRecordset(sSQL)
   If Not r.BOF And Not r.EOF Then
      r.MoveLast
      r.MoveFirst
      Do While Not r.EOF
         Print #k, r("Field1"); r("Field2"); r("Field3");
         r.MoveNext
      Loop
      Close #k
      r.Close
      Set r = Nothing
   End If
   MsgBox "done"
End Sub

You were soooo close....

NOTE that I removed the spaces in the field names and the table name. (in BLUE)
I NEVER use spaces or special characters (except the underscore) in object names.

The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm

[cvillechopper]

Thanks Steve. I really appreciate this. It does appear to work exactly as I need it to for my 10 record test sample. I now have some cleanup to do in my tables so I can actually process the output file but wanted to make sure I could get past the multiple record issue first.

Thanks again
James

[ssanfu]

You're welcome. Good luck with your project.