Solved Exporting query into multiple txt files (1 Viewer)

niawo

New member
Local time
Today, 02:09
Joined
Jul 5, 2017
Messages
14
Hi. Apologies if this is a basic question. I'm trying to export multiple txt files from a query. I found the attached module, which exports and names the files correctly, but I'm trying to get the contents of the text files to include Field1 and Field2. Field2 is a Long Text field. Is this possible?

Thanks.
 

Attachments

  • Export.mdb
    348 KB · Views: 64

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:09
Joined
Feb 28, 2001
Messages
27,001
You are trying to get the files to include a specific field? The implication is that they don't contain that field. What actually happens? What are the symptoms?
 

niawo

New member
Local time
Today, 02:09
Joined
Jul 5, 2017
Messages
14
You are trying to get the files to include a specific field? The implication is that they don't contain that field. What actually happens? What are the symptoms?
Hi. Currently they're exporting with V1, V2 in the contents (from Field1). I would like the contents to include Field1 and Field2. Thanks
 

niawo

New member
Local time
Today, 02:09
Joined
Jul 5, 2017
Messages
14
Take a look at this link
Thanks for this, but I'm looking to export into multiple files rather than 1 csv. I'm wondering if the module in the attached database can be modified to include Field 2? Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:09
Joined
Sep 21, 2011
Messages
14,048
You are using an export spec, so amend that or create a new one.
Probably need to create a new one. :( or remove the spec from the export line.
1684952450052.png

If you have a lot of fields, then this might be quicker.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 02:09
Joined
Feb 19, 2013
Messages
16,553
but I'm looking to export into multiple files rather than 1 csv.
Thought the issue was exporting a long text
Either way, 1 file or multiple files will be much the same, the latter just requires a loop
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:09
Joined
Feb 28, 2001
Messages
27,001
Looking at that MDB you loaded up, I don't see what is going on.

First, you can export a query as many times as you like to different files, but the mechanisms currently used in Access to export query content will be for one file at a time.

Second, if you want fields X, Y, and Z to be exported to a file, they have to be referenced as fields in the query you used for export. If a field is not showing up, it might be because it wasn't referenced.
 

niawo

New member
Local time
Today, 02:09
Joined
Jul 5, 2017
Messages
14
You are using an export spec, so amend that or create a new one.
Probably need to create a new one. :( or remove the spec from the export line.
View attachment 108108
If you have a lot of fields, then this might be quicker.
Hi thanks for this. So I need to amend qryExport_Spec referenced in the module? I had a look in saved exports but couldn't find it. Apologies - I don't have much experience with this kind of export procedure.
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:09
Joined
Sep 21, 2011
Messages
14,048
Well I could not find one either, so I walked through the export process and recreated it, or thought I did, as one appeared with just field1.
However trying to change mine it would not allow one field, when there there were two in the query.
So just remove jhe export spec as I Amado not sure what it would do if it does not exist.
If you export manually, what do you get?
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:09
Joined
Sep 21, 2011
Messages
14,048
Remove the spec.
Code:
Sub exportIt()
    Dim rs As DAO.Recordset
    Dim strFolder As String
    
    strFolder = myFolder 'set the folder path ONCE not on every iteration !!!

    Set rs = CurrentDb.OpenRecordset("Select distinct Field1 from tblYourTable")
    Do While Not rs.EOF
        CurrentDb.QueryDefs("qryExport").SQL = Replace(CurrentDb.QueryDefs("qryExport_Template").SQL, "<PutCityIn>", rs!Field1)
        Debug.Print CurrentDb.QueryDefs("qryExport").SQL
        DoCmd.TransferText acExportDelim, , "qryExport", strFolder & rs!Field1 & ".txt"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub
1684959519670.png
 

niawo

New member
Local time
Today, 02:09
Joined
Jul 5, 2017
Messages
14
Remove the spec.
Code:
Sub exportIt()
    Dim rs As DAO.Recordset
    Dim strFolder As String
   
    strFolder = myFolder 'set the folder path ONCE not on every iteration !!!

    Set rs = CurrentDb.OpenRecordset("Select distinct Field1 from tblYourTable")
    Do While Not rs.EOF
        CurrentDb.QueryDefs("qryExport").SQL = Replace(CurrentDb.QueryDefs("qryExport_Template").SQL, "<PutCityIn>", rs!Field1)
        Debug.Print CurrentDb.QueryDefs("qryExport").SQL
        DoCmd.TransferText acExportDelim, , "qryExport", strFolder & rs!Field1 & ".txt"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub
View attachment 108109
Many thanks! This works perfectly!
 

Users who are viewing this thread

Top Bottom