Advanced CSV export formatting in MS Access

Question

I am trying to create a CSV file with the following format (for import into MYOB AccountRight financial software):

InvoiceNum1,Line1,...
InvoiceNum1,Line2,...

InvoiceNum2,Line1,...
InvoiceNum2,Line2,...

InvoiceNum3,Line1,...
....

So basically each invoice is separated by a blank line, and there can be one or more invoice items per invoice. I currently have a query written that does the following format (i.e. without blank lines):

InvoiceNum1,Line1,...
InvoiceNum1,Line2,...
InvoiceNum2,Line1,...
InvoiceNum2,Line2,...
InvoiceNum3,Line1,...

This query is a union query that brings together two normal sub-queries.

Unfortunately the software I am importing into is extremely stupid and treats this all as one invoice. So I need a way to insert the blank lines between each invoice. Before I export the CSV I am applying filters to the query (so I can select a date range) which means that any blank lines I manage to insert in my query get filtered out because they don't contain a date.

The solution I am currently planning is to write a separate program that the user can use to format the CSV file correctly before importing it into MYOB. This is obviously a huge hack and I would prefer a native Access solution if possible.

I'm using Access 2007, but can upgrade/downgrade to a different version if it gets me the functionality I need.

Answer 1

If you're just trying to put a line between every line after you've exported your file then one solution would be to create macros in Excel. By tweaking your auto-generated macros code with VBA in Excel you can automate all the process and use the same solution in the future with a click of a button.

But if you're familiar with programming languages then it might be much more easier to manipulate your files in scripting languages such as Python etc..

*CSV files are seperated by commas hence "comma seperated values."

Answer 2

Unfortunately the only solution I found was to write a program (I used C#, but anything would work) to go through the file and enter more line breaks between the different invoices. I then wrote some VBA code in Access that exported the query to a file and then ran the formatting program over the file, thus making it appear seamless to the end user.