Solved

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

Posted on 2007-11-27
6
2,080 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:cacklebunny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:deroby
ID: 20358371
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...)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20359502
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".
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 20359601
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
UNION ALL
SELECT col1 + RIGHT(REPLICATE('0', 10) + CAST(col2 AS VARCHAR(10)), 10) + col3 + ...
FROM tableName
UNION ALL
SELECT 'Trailer Rec:' + col1 + CAST(SUM(...) AS VARCHAR(20))
FROM tableName
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 2

Author Comment

by:cacklebunny
ID: 20376590
Thanks for the replies, gentlemen.

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

Thanks so much for your help.
0
 
LVL 11

Expert Comment

by:deroby
ID: 20377062
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 !
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20377161
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.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question