Writing a query to a text file

[spet]

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

 

[theDBguy]

Hi. Have you tried using the OutputTo method?

DoCmd.OutputTo method (Access)

Access VBA reference

docs.microsoft.com

 

[Gasman]

https://www.access-programmers.co.uk/forums/threads/access-to-text-file-export.67574/

 

[spet]

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]

Also, I have multiple queries I want to send to that file. Is there anyway to open it and append after creating?

 

[theDBguy]

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]

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.

 

[spet]

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]

****My queries don't all consist of the same amount of fields or the same data****

 

[theDBguy]

****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?

XX XXX XXXXXX X XXXX
YY YYY Y YYYY

Or something more like this?

XX XXX XXXXXX X XXXX
YY YYY        Y YYYY

 

[theDBguy]

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]

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]

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]

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]

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.

 

[spet]

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.