Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: Exporting to flat file with fixed locations

    HTML Code:
    Hello,  
    
    I am trying to export the output from a query (SQL SERVER 2005) into fixed space delimiters.  The goal is to bring the data into the mainframe as a flat file for additional processing.
    
    is this possible without doing a manual PAD for each column?
    
    Current output looks like this:
    ACCT#     STATUS       DATE
    12345      active         2008/09/10
    11223       cancel     2008/03/15
    22255      lead       2011/05/10 
    
    trying to line up each column:
    
    ACCT#     STATUS          DATE
    12345      active          2008/09/10
    11223      cancel          2008/03/15
    22255      lead            2011/05/10  

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    If you are using BCP then create a format file. Should work for you. Linky

  3. #3
    Join Date
    Aug 2011
    Posts
    34
    My company is actually moving away from BCP and I tried using SSIS but I cant seem to line up the columns.

    Any help with SSIS would be great!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many ways to create an SSIS package, so there isn't a "one size fits all" solution for this problem.

    If you create an export package from scratch using SSMS (SQL Server Managment Studio) just select the "Fixed Width" option instead of the default "Delimited" from the Flat File drop down box.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Another method is to use ASCII characters:

    Example from BOL:

    SET TEXTSIZE 0
    -- Create variables for the character string and for the current
    -- position in the string.
    DECLARE @position int, @string char(8)
    -- Initialize the current position and the string variables.
    SET @position = 1
    SET @string = 'New Moon'
    WHILE @position <= DATALENGTH(@string)
    BEGIN
    SELECT ASCII(SUBSTRING(@string, @position, 1)),
    CHAR(ASCII(SUBSTRING(@string, @position, 1)))
    SET @position = @position + 1
    END
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •