Post reply

Importing Text Files with abnormal characters

  • April 21, 2003 at 2:47 pm

    #175588

    I have to import a text file that is generated by legacy systems. There are abnormal characters, i.e. ASCII values 5, 7, etc.

    DTS would translate those characters as end of file and therefore drop the remaiming records.

    Any advice on how to import all records is appreciated.

  • April 21, 2003 at 5:24 pm

    #454850

    What is the size of these files?

    How about filtering / dropping the funny characters before importing the data. Will result in reading the file twice but will make life much easier.

  • April 21, 2003 at 5:34 pm

    #454851

    Look at article at

    http://www.sqldts.com/default.aspx?6,101,292,0,1

  • April 21, 2003 at 5:39 pm

    #454853

    If uploading into a tmp file and then strip with TSQL. See discussion:

    http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=11457&FORUM_ID=19&CAT_ID=3&Forum_Title=Data+Transformation+Services+%28DTS%29&Topic_Title=Uploading+a+Binary+flat+file+into+a+SQL+server

  • April 21, 2003 at 6:21 pm

    #454855

    Thanks for your replies.

    The file size is small for our discussions. I have also tried to do things in VB to clean up the records. For example, I open the file, read line by line ( Open File and then use Line Input) and then write to a different file. I can then cleanse the line input. One problem is that whenever an abnormal character is encountered, EOF (end of file) returns true and the program fails to finish reading the remaining records.

    I also tried to use LOF and LOC to attempt to reach further into the file but have failed so far.

    Is there any way to read the whole file?

  • April 22, 2003 at 11:01 am

    #454935

    What is the text file format? Delimited, fixed lenght?

    To import the text file with "abnormal" characters it has to be in fixed lenght or delimited with some custom delimiters that won't be mistaken for a part of the data. For instance we use {|} for field terminator and {~END~} for row terminator.

  • April 23, 2003 at 2:04 am

    #454999

    If you are using VB to read/write and cleanse the file then open the file in binary mode and use the get command. This will retrieve all the characters in the file (including tabs,eof,CR,LF etc) and then you can cleanse it accordingly

    Far away is close at hand in the images of elsewhere.
    Anon.

  • May 2, 2003 at 10:08 am

    #456026

    Thanks for your help.

    I used David's solution to Open the file in Binary mode.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply