Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Unanswered: Dropped lines when NULL ascii value in text file

    Hello,

    SQL SERVER 2000:
    My problem is that I have to process a special text file every day which contains 0 ASCII values to separate fields. The DTS import program drops everything after the ascii 0 value in the row, but of course I need the entire row with all fields.
    So how can I prevent the text file import task from dropping everything after the 0 ascii value?

    Could you help me in this?

    thank you

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since NUL (a zero byte) is an EOF (End Of File) marker, I would expect the import to stop at that point. You mean that the import continues with the next line in the file ?!?!

    -PatP

  3. #3
    Join Date
    Aug 2006
    Posts
    4
    Yes, the importing process continues..and the DTS import task just drops the remaining part of the line after the ASCII 0 character.
    So all the rows that contain a 0 ASCII character at some point will be just partly imported. I need to somehow ignore this 0 ASCII character..

    any ideas?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're losing me on the ASCII 0 thing....can you present us a select statement as to what the value is?

    And where is this file coming from?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Weird...
    Code:
    select char(65) 
    	+ char(0) 
    	+ char(66)
    Commenting out the second line you get to see the "B".

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't know of any way to get DTS to process a file containing NUL values. You'll probably need to pre-process those into some other character (I'd suggest using TAB or 0x09) using a client-side tool (I'd pick Perl as my first choice, and C as my second, but I'm a geek).

    -PatP

  7. #7
    Join Date
    Aug 2006
    Posts
    4
    ok, so the only way is to write an ActiveX script that replaces the NUL (ASCII 0) chars in the text file, saves it to a temporary file that can be imported by DTS?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is the only way that I know how to make it happen. NUL characters are problematic for just about everything that is written in C, or based on tools that were written in C.

    -PatP

  9. #9
    Join Date
    Aug 2006
    Posts
    4
    ok, thank you for the help! I'll preprocess the text file then before importing.

Posting Permissions

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