Export from access to multiline text file

[JoRo50]

An outside agency is requiring me to report data for each instance of my dataset on multiple lines. Right now I can export the data in this form:

1-V1, 1-V2, 1-V3, 1-V4, 1-V5, 1-V6, 1-V7, 1-V8, 1-V9
2-V1, 2-V2, 2-V3, 2-V4, 2-V5, 2-V6, 2-V7, 2-V8, 2-V9

where 1-V1 means first instance, variable 1. I need to put the information into a text or CSV file in the form

1-V1, 1-V2, 1-V3
1-V4, 1-V5
1-V6, 1-V7, 1-V8, 1-V9
2-V1, 2-V2, 2-V3
2-V4, 2-V5
2-V6, 2-V7, 2-V8, 2-V9.

Right now the query reads:
SELECT V1, V2, V3, V4...V9
FROM Table
GROUP BY V1, V2, V3, V4...V9.

Is there a way to do this in Access? Thanks for your help.



I'm using a windows 7 version of Access 2010. Both are 32bit.

[ranman256]

What is the line break, 4 columns? 3 columns?
1-V1 you have 3 columns
but 1-V6 has 4 columns on the line.

How many columns do you want?

[JoRo50]

Ranman, the number of columns and the field length for each column is unique for each line. (this wasn't my idea). I'm trying to avoid creating the text file and then manually entering the line breaks.

[ssanfu]

The only way I can think of is to use VBA to read the query and write to a text/CSV file.
You (the code) control which variables are written and when the line breaks.

Is there always 9 variables?

Not hard, just lots of code.

[JoRo50]

There are actually about 45 variables broken into 8 lines. The number of variables and the number of lines are the same for each instance. I just used these 9 as an example to show that the number of variables per line are not the same.
Would I be able to do the VBA coding in access or do I have to use a different mechanism. I'm open to any options here.

[ssanfu]

Yes, the VBA is in Access.

In code, you would need to:

Create the text/CVS file
Write the header if required
Open the query as a recordset
Determine if there are records
Begin a loop
Read the first line
Parse the line (could be an array or into variables
Create the string to write
Write the output string
Loop
Clean up (close the text file, recordsets, etc)

I always have a message box saying "Done" and possible where the file was saved, number of lines read/written, etc.

[JoRo50]

Thanks, Steve. I will give this a shot. I've done a little with VBA but not much so I may have more questions later.

[June7]

Bing: VBA read write text file
http://www.tek-tips.com/faqs.cfm?fid=3767
http://www.excel-easy.com/vba/exampl...text-file.html