Solved

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

Posted on 2007-11-27
6
2,035 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
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:deroby
Comment Utility
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:ScottPletcher
Comment Utility
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:
ScottPletcher earned 250 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:cacklebunny
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now