Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Well, normally a CSV does not contain blank lines. AFAIK the blank line means the end of the data... This way I wonder if you have any better possibility than script it somehow. –  dezso Sep 11 '13 at 7:50
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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."

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.