Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: DTS Only Brings in Half of Text File Records

    All rows of a 1500+ record text file we have at work should be 512 spaces. Some are not and seem to be causing problems. I get half the records I should be getting on an import. When the DTS task comes across one of these records it takes it and appends them together and makes two rows one row. Any ideas? I tried making a table with one field of datatype char(1000) and tried to import into this table. I figured this would add any missing trailing spaces but no go.

    ddave

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is the text file format? CSV? Pipe-delimited? Tab delimited? I'd suspect that you need to specify the end-of-line delimiter for DTS.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    It doesn't appear to have a delimiter, just fixed width. I am suspecting that there may not be a carriage return/line feed at the end of every other row. Is this a possibility? Aren't there text editors that can view this type of thing?

    ddave

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dolfandave
    I am suspecting that there may not be a carriage return/line feed at the end of every other row.
    Then the problem is with your file format, not with DTS. Who generates this file for you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    It is our client and we KNOW that the client's extraction process is absolutlely infallable, don't we?. I confirmed it is fixed length and the fact that the records are of varying length is throwing the process off somehow. We just found this out as we took our text editing software, UltraEdit, and appended trailing spaces to all files less than the 512 character standard set by the client. When we appended these characters manually the job ran correctly. Therefore the question becomes is there a way to get the text file into a table with one field and then append trailing spaces? I can handle the query part but I am having a tough time getting it into a table.

    ddave

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    When I try to import the flat file w/o manually appending spaces it only brings in the first 50 or so characters. I have tried importing into a table that has one column using enterprise manager. I tried changing it from char(1000) to nvarchar(1000) but still had problems.

    ddave

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    What happens if you import it into a spreadsheet first? DO you get all 1500+ rows?

    If so, then you could DTS it into SQL Server from the spreadsheet.

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

  8. #8
    Join Date
    Feb 2004
    Posts
    193
    Well I didn't want to say it but my supervisor put it into MS Access first (again manually) and he gets all 1500+ records and all are the correct length. The closest thing we could probably do is to have the DTS job bring it into Access and then into SQL Server. M-I-C........K-E-Y.............

    ddave

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Access has an excelent file import utility, but DTS should be able to do anything Access can do. There has to be a setting in DTS that affects this.
    You say your file is fixed-width, but it still has to have record delimiters if not column delimiters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Posts
    193
    I think I resolved it. In the DTS job we originally have the text file import step as a fixed width. It is a fixed width file but for some reason it is truncating around 50 columns, which is where some blank spaces begin. There is ensuing data further to the right but anyway DTS seems to be reading it as the end of the record for some reason. I changed the import to delimited and selected tab. There are no tab delimiters but in this case DTS is bringing in the whole record through 512 characters into a single field table. The datatype is char(512). This seems to have solved it. Thanks.

    ddave

Posting Permissions

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