Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012
    Posts
    8

    Unanswered: Fixed length Text File

    Hello All,

    I've been working on this code all day. I have approximately 13 columns. Each Column has a start position and end position (see below). I created this in a table and defined the position, it's still not working for me. What am I doing wrong?

    FiceCode char(6), -- starting position 1, field length 6
    StateStudID char(10), -- starting position 7, field length 10
    CampusStudID char(10), -- starting position 17, field length 10
    LastName char(25), -- starting position 27, field length 25
    FirstName char(15), -- starting position 52, field length 15
    MiddleIName char(15), -- starting position 67, field length 15
    Suffix char(3), -- starting position 82, field length 3
    DateofBirth char(8), -- starting position 85, field length 8
    Filler1 char(2), -- starting position 93, field length 2
    Gender char(1), -- starting position 95, field length 1
    Filler2 char(6), -- starting position 96, field length 6
    SSN char(9), -- starting position 102, field length 9
    FieldNum char(1), -- starting position 111, field length 1
    DataLine char(111)

    I need a text output file that will define each start position.

    I also used:
    right(replicate('0',25) + cast(last_name as char(25)), 25) in my sql statement. However, when I add the first_name, I can't get it to start in position 52.

  2. #2
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Are you trying to output your table into the format you have specified, or are you trying to import a text file with that specification.

    If it is the former, you could probably do something like:

    SELECT FiceCode + Space(6-len(FiceCode)) + StateStudID+ Space(10-len(StateStudID)) + etc.

    This will output your fields and pad output with spaces depending on character length within the field. You would swap the function to be before the field name if you needed leading spaces instead of trailing spaces.

Posting Permissions

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