Hi, I need to find an easier to way to export data from a single table to a fixed with .txt file than using SSIS. I found this really nice function which purports to export do what I need but I can only save the file as a tab-delimitted file which isn't what I need.
What I need is this file layout: https://dl.dropboxusercontent.com/u/20388755/file%20layout%20sample.png
Here's how to call this function
SELECT
dbo.CharPad (SSN,9,'LEFT', '')
,dbo.CharPad (DOB,8,'LEFT', '')
,dbo.CharPad (LastName,26,'LEFT', '')
,dbo.CharPad (FirstName,20,'LEFT', '')
,dbo.CharPad (CustomerRecordID,28,'LEFT', '')
,dbo.CharPad (ADDate,10,'LEFT', '')
,dbo.CharPad (MiddleName,20,'LEFT', '')
FROM [All Borrowers 201511]
CREATE FUNCTION [dbo].[CharPad] (
@Input VARCHAR(255)
,@OutputWidth INT
,@OutputAlign VARCHAR(5)
,@PadCharacter CHAR(1) )
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Output VARCHAR(255)
DECLARE @InputWidth INT
SET @InputWidth = LEN(@Input)
IF @InputWidth > @OutputWidth
BEGIN
IF @OutputAlign = 'LEFT'
BEGIN
SET @Output = LEFT(@Input,@OutputWidth)
END
IF @OutputAlign = 'RIGHT'
BEGIN
SET @Output = RIGHT(@Input,@OutputWidth)
END
END
IF @InputWidth < @OutputWidth
BEGIN
IF @OutputAlign = 'RIGHT'
BEGIN
SET @Output = REPLICATE(@PadCharacter, @OutputWidth - @InputWidth ) + @Input
END
IF @OutputAlign = 'LEFT'
BEGIN
SET @Output =@Input+ REPLICATE(@PadCharacter, @OutputWidth - @InputWidth )
END
END
IF @InputWidth = @OutputWidth
SET @Output = @Input
RETURN (@Output)
END