Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Unanswered: Fixed length file output

    I need to write data into a fixed column length file and was wondering the best (most efficient) way to tackle this. For example, the first few pieces of the report I'm working on now would be:

    PacketID - Starting position 1, Field length 9
    TransactionID - Starting position 10, Field length 9
    Group number - Starting position 19, field length 10
    PID/SSN - Starting position 29, field length 10

    For the PID/SSN, if I have a PID it'll be 10 digits and fill the field length, if I don't I use SSN which is only 9 digits and enter a space as the 10th digit. Obviously if I don't have certain pieces of information I'll just need spaces of the specified length to satisfy the file format. I'm using SQL 2005. Thanks in advance for any help provided.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Use fixed character strings

    Code:
     
    /*
    PacketID - Starting position 1, Field length 9
    TransactionID - Starting position 10, Field length 9
    Group number - Starting position 19, field length 10
    PID/SSN - Starting position 29, field length 10
    */
    set nocount on
    set ansi_nulls on
    declare @pid char(9), @tid char(9), @grp char(10), @pisdssn char(10), @str char(38)
    select @pid = '123', @tid = '4567', @grp = 'Zero', @pisdssn = '1234567890'
    select @str = @pid + @tid + @grp + @pisdssn
    select @str
    select @str = ''
    select @str = '123456789012345678901234567980123456789'
    select @str
    Code:
     
    results
     
    -------------------------------------- 
    123      4567     Zero      1234567890
     
    -------------------------------------- 
    12345678901234567890123456798012345678

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    May 2011
    Posts
    2
    Hi

    Your code was a great help to me but I need a bit more help.

    I am pulling the data from a table in SQL 2008 but have no idea to get it to give me more than one line. I am so new to SQL - I am going to search but I have a deadline in a few hours so am taking this route first.

    I also need to add 0000s in front of numeric fields rather than spaces after - any idea how to do this? Or would declaring the field to be int be enough?

    Thanks
    Cathy

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    In that case I make a table with a CHAR column per value, and an extra column that holds the total string (you could use a calculated column for that)
    Code:
    CREATE TABLE DaTable(
    	PacketID	CHAR(9),  -- Starting position 1, Field length 9
    	TransactionID	CHAR(9),  -- Starting position 10, Field length 9
    	Group_number	CHAR(10), -- Starting position 19, field length 10
    	PID_SSN		CHAR(10), -- Starting position 29, field length 10
    	DataLine	CHAR(38) 
    )
    To create left-000 aligned numbers, I use:
    Code:
    SELECT RIGHT('000000000' + CAST(MyTable.PacketID as VARCHAR(9)), 9) as PacketID
    FROM MyTable
    To fill the final result string 'DataLine', you can add the different columns together or you could use a Calculated Column (but I haven't used that myself on SQL Server ).
    Code:
    UPADATE DaTable
    SET DataLine = PacketID + TransactionID + Group_number + PID_SSN
    Then I export it to a file using BCP.
    With all the separate values stored in separate columns, debugging and fine-tuning is easy.
    Last edited by Wim; 05-27-11 at 09:10. Reason: Removed erronious NOT NULLs in table definition
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    May 2011
    Posts
    2

    Thumbs up

    Thank you that was so useful. Got it working perfectly. Now I just need to work out how to run the query from Access

Posting Permissions

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