How to export to Fixed Field ASCII format with header and trailer records?

I have a vendor who is requesting we export our data in an "ASCII fixed field, fixed length" format with header and trailer records.

I've never dealt with header or trailer records before and am not clear how I'd insert these.

First, how would I export in this format, and what kind of query would I need to insert these "header" and "trailer" records?  By the way, the columns requested in the header and trailer records do not match the number of columns in the "Detail" records.
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
Rats, thought that bcp had an overwrite/append file option, but it does not.  You may have to convert all results to one long string, and bcp that result to a file all at once; a sample query would be:

SELECT 'Header Rec' + ... + '=' + ...
--FROM tableName
SELECT col1 + RIGHT(REPLICATE('0', 10) + CAST(col2 AS VARCHAR(10)), 10) + col3 + ...
FROM tableName
SELECT 'Trailer Rec:' + col1 + CAST(SUM(...) AS VARCHAR(20))
FROM tableName
The way we did this is by simply creating a results table that holds a sequence number (IDENTITY column) and a large string per record
First create the header(s) somehow and store it as a string in the table
Then, either record by record or in bulk, add the data
Finally add the footer(s)

In the end, simply export the table, make sure to ORDER BY on the identity field.

What you'll need is :
* table to store the result
* stored procedure to clear the table and fill it up again with the new results
* some program to extract the result. (BCP.exe would work, DTS OR SSIS would be great too...)
Scott PletcherSenior DBACommented:
The best solution would be to get your vendor out of the 1970s style of processing, but obviously that isn't going to happen :-) .

So, I suggest this:

1) Write the header record to a file specifying "overwrite".
2) Write the data rows to the same file using a standard SELECT query specifying "append".
3) Write the trailer record the the same file specifying "append".
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

cacklebunnyAuthor Commented:
Thanks for the replies, gentlemen.

Scott, I went ahead with your suggestions and that seems to work!  

Thanks so much for your help.
Keep in mind that this solution might turn the sequence of the lines 'upside-down' ...

You'll need to add some kind of ORDER BY for this to work !
Scott PletcherSenior DBACommented:
UNION ALL shouldn't change the order of lines w/i each section.  If you need to, add an ORDER BY to the middle query.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.