Exporting to a Text File - but not a delimited file

[wob]

Hi.

I'd like to export a query as a text file, but it's not a delimited file.
Let's say it's a query called QryData and it looks like this:

ID Food Quantity
1 Apple 50
5 Tea 25
9 Cake 12


I want a QryData.txt file that has:

ID;1
Food;Apple
Quantity;50
ID;5
Food;Tea
Quantitiy;25
ID;9
Food;Cake
Quantity;12

Basically, each field is getting it's own row in the text file.
Any ideas on how to do this?

 

[alansidman]

Suggest you create a report in Access formatting it as you have shown, using the query as the record source. You can then export the report or just print it.

Alan

 

[wob]

No. The file format is going to be very specific (and more complicated than the example).

What I need to understand is how can I export a text file from a query and what methods I can use to to 'loop around' the data.

If this was excel, I would count the columns and rows and move around my data set by offsetting the active cell contents and writing them out. I don't know the equivalent in Access.

Unfortunately, this query has too many rows for me to export to excel...

 

[Phildaburn]

You could perhaps adapt this:

Sub a()
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array("c:\File1.txt", "c:\File2.txt")
Open "c:\file3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1
End Sub

Phil...

 

[wob]

Phil. That macro looks like it's merging two text files into one.

I need to understand how I can produce a text file, in the format above, using a query as a source.

 

[wob]

Ok. If I could do this in Excel, I'll be doing something like this.
Assume the data set in the first post was in columns A1:C3

This is the macro

==================================================================
Public EmpData, DataStringA, ImportFileName As String
Public FirstRow, LastRow, TotalRows, As Integer
Public FirstCol, LastCol As Integer

Sub ExportData()

'Application.ScreenUpdating = False

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Parameters - Need to change these if file format changes
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
FirstCol = 1
LastCol = 3
FirstRow = 2
TitleRow = 1
LastRow = 4

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'creates import file
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
ImportFileName = "H:\temp\qrytext.txt"

Open ImportFileName For Output As #1

For i = FirstRow To LastRow
DataStringA = ""
For j = FirstCol To LastCol
EmpData = Cells(TitleRow, j) & ";" & Cells(i, j)
Print #1, EmpData
Next

Next

Close #1

End Sub
==================================================================

When run, it produces a text file:

ID;1
Food;apple
Amt;50
ID;5
Food;tea
Amt;25
ID;9
Food;cake
Amt;12

So any ideas on how this could be applied to a Access query?

 

[Phildaburn]

The following is untested but I've adapted something I use.

Dim rsTxt As DAO.Recordset

Set rsTxt = CurrentDb.OpenRecordset("QryData")<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim fs, TextFile<o></o>
Set fs = CreateObject("Scripting.FileSystemObject")<o></o>
Set TextFile = fs.createtextfile("C:\path&FileName.txt", True)
<o>Dim vId, vFood, vAmt</o>
<o></o>
rsTxt.MoveFirst<o></o>
Do While Not rsTxt.EOF<o></o>
'Get the source data
vId = rsTxt("ID")
vFood = rsTxt("Food")
vAmt = rsTxt("Amt")
TextFile.writeline ("ID;" & vId)
TextFile.writeline ("Food;" & vFood)
TextFile.writeline ("Amt;" & vAmt)<o></o>
rsTxt.MoveNext<o></o>
Loop<o></o>
TextFile.Close<o></o>
<o></o>

 

[james_lankford]

'***************************************
Sub do_example()
       
    example "myfile.txt"
    
End Sub
'***************************************
Private Sub example(filename As String)
    
    Dim i As Integer
    Dim filenumber As Integer
    Dim qdf As QueryDef
    Dim rs As DAO.Recordset
    
    Set qdf = CurrentDb.QueryDefs("name_of_query")
    Set rs = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
    
    filenumber = FreeFile
    
    Open filename For Output As #filenumber
    
    Do While Not rs.EOF And Not rs.BOF
        For i = 0 To rs.Fields.Count - 1
            Print #filenumber, rs.Fields(i).Name & ";" & rs.Fields(i).Value
        Next
        rs.MoveNext
    Loop
    
    Close #filenumber
    
    rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    
End Sub
'***************************************

 

[wob]

Both work!

Thanks Both!