Writing a query to a text file (1 Viewer)

spet

Registered User.
Local time
Today, 03:47
Joined
Oct 5, 2018
Messages
38
Good Morning,

I am looking to write the results of a query to a text file (first creating the text file) I have never done this before and am very confused with some of the stuff I'm finding online.

Can anyone help me with the basic format of this?

Thank you in advance
 

spet

Registered User.
Local time
Today, 03:47
Joined
Oct 5, 2018
Messages
38
Oh man, this is way easier than what I was trying to do. Is there anyway to export the results with fixed width to keep my current formatting? Another program will be reading and uploading this information? Thank you both!
 

spet

Registered User.
Local time
Today, 03:47
Joined
Oct 5, 2018
Messages
38
Also, I have multiple queries I want to send to that file. Is there anyway to open it and append after creating?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:47
Joined
Oct 29, 2018
Messages
21,358
Oh man, this is way easier than what I was trying to do. Is there anyway to export the results with fixed width to keep my current formatting? Another program will be reading and uploading this information? Thank you both!
You could try creating an export specification to define the fixed width of each column.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:47
Joined
Oct 29, 2018
Messages
21,358
Also, I have multiple queries I want to send to that file. Is there anyway to open it and append after creating?
The easiest approach I could think of for doing something like that is to combine the results of all the queries into one query or table and then output that into a text file.

Otherwise, another approach is to export each query into separate text files and then use code to combine them into one text file.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:47
Joined
Feb 19, 2002
Messages
42,981
Use TransferText. Export the file ONCE manually to build the export spec. Then once the spec is built, you can automate by using the TransferText method.

If you want to send multiple data sets to the same file, create a union query that unions all the selected queries. As long as they are all in the same format, this will work fine.

If you want to append manually, you have to write the records manually using a vba loop to pick up each record and VBA code to format each record and write it.

To create the export spec, click on the Advanced button while you're in the wizard so you can save it and specify the correct date format.
 

spet

Registered User.
Local time
Today, 03:47
Joined
Oct 5, 2018
Messages
38
So I've created the export spec to use with the TransferText. See below:
strFile = "C:\TestFile.Txt"

DoCmd.TransferText acExportFixed, "specACHFileHeaderRecord", "NACHAFileHeaderRecord", strFile

BUT I have multiple header records that need to be wrote (that are produced off of different queries). So from here, I created the following code to append my next Query results, but I loose my fixed formatting of the fields. I need !RecordType to be in position 1 and then !PriorityCode to be in positions 2-6, etc.

Any thoughts?


Dim MyDB As DAO.Database
Dim rstFileHeader As DAO.Recordset
Dim intFileNum As Integer
intFileNum = FreeFile()
Open "C:\TestFile.txt" For Append As #intFileNum
Set MyDB = CurrentDb()
Set rstFileHeader = MyDB.OpenRecordset("NACHACompanyHeaderRecord", dbOpenSnapshot)
With rstFileHeader
Do While Not .EOF
Print #intFileNum, !RecordType, !PriorityCode
.MoveNext
Loop
End With
Close #intFileNum
rstFileHeader.Close
Set rstFileHeader = Nothing
 

spet

Registered User.
Local time
Today, 03:47
Joined
Oct 5, 2018
Messages
38
****My queries don't all consist of the same amount of fields or the same data****
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:47
Joined
Oct 29, 2018
Messages
21,358
****My queries don't all consist of the same amount of fields or the same data****
Hi. Not having the same data is understandable, but not having the same amount of fields, how did you want that to translate into the text file. Since you want to have fixed-width columns would that look something like this?
Code:
XX XXX XXXXXX X XXXX
YY YYY Y YYYY
Or something more like this?
Code:
XX XXX XXXXXX X XXXX
YY YYY        Y YYYY
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:47
Joined
Oct 29, 2018
Messages
21,358
I need !RecordType to be in position 1 and then !PriorityCode to be in positions 2-6, etc.
Hi. It would probably be easier for us to understand that if you could post a screenshot or an Excel mockup of what you're getting and what you want to get instead.

In other words, can you show us an example of the source data and a mockup of the result you want to get out of it?

Thanks!
 

spet

Registered User.
Local time
Today, 03:47
Joined
Oct 5, 2018
Messages
38
More like this: (My Header Records and Control Records will be different) BUT, my detail will be the same. So, I'm think creating 3 different queries to populate the information.
EX:

01 123 XXX 456 YYY
01 234 FFF 789 XXX
John Smith 0123456789 01 987654321
Bob Marley 2345678912 12 987456512
02 456 AAA 234 BBB
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:47
Joined
Oct 29, 2018
Messages
21,358
More like this: (My Header Records and Control Records will be different) BUT, my detail will be the same. So, I'm think creating 3 different queries to populate the information.
EX:

01 123 XXX 456 YYY
01 234 FFF 789 XXX
John Smith 0123456789 01 987654321
Bob Marley 2345678912 12 987456512
02 456 AAA 234 BBB
Hi. You can probably see that your post, more than likely, did not come out as you intended it to be in reference to formatting. For example, it shows one space character after Smith, but I know you meant to show more than one space there, correct?

That is why I suggest either post an image instead or an Excel mockup to help us understand you better.

If you expect the text file to have fixed width columns, how does 01 and John relate in your sample data.

Unless, are you saying you don't care about fixed width columns between each query result?
 

spet

Registered User.
Local time
Today, 03:47
Joined
Oct 5, 2018
Messages
38
1604962515408.png



Here is a screen shot. So you can see on my Header records, I want position 01 to consist of "01". On my Detail Records I want Position 0 - 3 to consist of "FirstName" and positions 4-9 to consist of "LastName".

I hope I'm making sense here.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:47
Joined
Feb 28, 2001
Messages
27,001
The most common method for merging text files is to open an output file, then open each input file in turn.

In a loop, read one line via the VBA verb LINE INPUT which gives you a single string variable. Write that line to the output file including the line-terminating control codes. Loop until end of file. Close the input file.

Wash, rinse, repeat until you have no more input files. Close the output file. You are done.

Note that if you are using LINE INPUT, the input MUST be text-oriented and the output of necessity WILL be text-oriented.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:47
Joined
Feb 19, 2002
Messages
42,981
I hope you don't ever send me data. Putting multiple headers in a fixed width layout is not rational. Nor is putting multiple layouts rational unless you like to write code..

This is a completely custom file. You're on your own. It's all VBA. No Access tools. Those only work with normalized structures which this is not.
 

spet

Registered User.
Local time
Today, 03:47
Joined
Oct 5, 2018
Messages
38
Thank you all for your help! I sure do find this site helpful!

@Pat Hartman, I hope I don't have to either. Yes, this is very custom and what is being requested.
 

Users who are viewing this thread

Top Bottom