Exporting to a Text File - but not a delimited file

wob

Board Regular
Joined
May 21, 2002
Messages
105
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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,193
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 21, 2002
Messages
105
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

Board Regular
Joined
Feb 4, 2011
Messages
146
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

Board Regular
Joined
May 21, 2002
Messages
105

ADVERTISEMENT

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

Board Regular
Joined
May 21, 2002
Messages
105
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

Board Regular
Joined
Feb 4, 2011
Messages
146

ADVERTISEMENT

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-com:eek:ffice:eek:ffice" /><o:p></o:p>
Dim fs, TextFile<o:p></o:p>
Set fs = CreateObject("Scripting.FileSystemObject")<o:p></o:p>
Set TextFile = fs.createtextfile("C:\path&FileName.txt", True)
<o:p>Dim vId, vFood, vAmt</o:p>
<o:p></o:p>
rsTxt.MoveFirst<o:p></o:p>
Do While Not rsTxt.EOF<o:p></o:p>
'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:p></o:p>
rsTxt.MoveNext<o:p></o:p>
Loop<o:p></o:p>
TextFile.Close<o:p></o:p>
<o:p></o:p>
 
Last edited:

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,181
Code:
'***************************************
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
'***************************************
 

Watch MrExcel Video

Forum statistics

Threads
1,130,327
Messages
5,641,541
Members
417,218
Latest member
dchapman17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top