Flat File with a Tilde as a field separator & a header/footer record 1

[kathyk]

I have to create some flat files for transfer to a main frame system.
Not only does the file need to have a header and footer record appended
to the data, but it also must use the "tilde" symbol as a field separator.
Any ideas what method to use to append the header and footer record
to the extracted sql data? Also, how do i represent a tilde character?
Anyone know what the hex character for that is?

thanks,
kathyk

 

[Andel]

Tilde is char(126)
Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlbroadbent]

To get the header and footer, you can use a stored procedure.

Create procedure usp_ExportMainframeFile As

set nocount on /* don't display a record count */

Print 'this is the header record.......' /* you can control the format on a print statement. */

Select statement.... /* Make sure that headers are turned off, and the delimiter is set to "~" */

Print 'this is the trailer record.......'

Go

You could run this in Query Analyzer with and save the result as a txt file for transfer to the mainframe. Or if the export will be run on a schedule, setup a job that executes the OSQL utility.

Example:

OSQL -E -Sserver -Q"exec usp_ExportMainframeFile" -s~ -oexport.txt

-E option: open a trusted connection (you may want to use a SQL login and password instead.)
-S option: login to the named server.
-Q option: execute the T-SQL code in the quotes, then exit.
-s option: set the column separator, in this case "~"
-h option: Query header option, -1 means no headers
-o option: output results to the named file

This is one possible solution. Hope you find it helpful. Terry

http://members.home.net/tlbroadbent/index.htm

 

[JNC73]

Hi,

What I would do is make a batch file in DOS.

In the first commands I would export the header to a text file.

The next command would be a BCP export command with the option of the field separator is a ~ and the output redirected to the text file.

THe last commands will export a footer to the text file.

This is by my knowledge the easiest way to get what you want.


JNC73

 

[tlbroadbent]

You can run OSQL in a batch file. Terry

http://members.home.net/tlbroadbent/index.htm