locked
How to handle non printable ascii characters in ssis RRS feed

  • Question

  • Hi I have excel as a source and i want to lad that data in sql table using ssis

    someone can explain me how to handle non printable ascii character in that

     

     

    Thanks

    Tuesday, September 20, 2011 3:00 PM

Answers

  • Hi banty1,

    Thanks for your question and Aamir’s reply. Based on my research, Uri had handled one similar thread with T-SQL query, please reference to: How to write a sql query to remove non-printable characters in a column but keeping the carriage returns

    declare @string varchar(200)

    set @string = 'this $%^^&is%^& s2342om23&&({}e c76l232e+_+a#n/ c][#o''d#e'

    select cast(cast((select substring(@string,n,1)

    from numbers

    where n <= len(@string)

    and substring(@string,n,1) like '[a-z ]' for xml path('')) as xml)as varchar(max))

    Thanks,
    Sharp


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Edited by Brent Serbus Friday, September 23, 2011 12:27 AM edit
    • Marked as answer by Eileen Zhao Wednesday, September 28, 2011 2:09 AM
    Thursday, September 22, 2011 5:29 AM

All replies

  • How about loading your Excel Data into staging table and then remove non printable ascii characters and load into destination after

    that. 

     

    Please visit below link

    http://wardyit.com/blog/blog/archive/2005/09/20/45.aspx

     

    Or use Scripting in SSIS

    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/d490653d-479c-4a40-90ff-76870309c801/

     

    Thanks

     


    http://sqlage.blogspot.com/
    Tuesday, September 20, 2011 3:15 PM
  • Hi banty1,

    Thanks for your question and Aamir’s reply. Based on my research, Uri had handled one similar thread with T-SQL query, please reference to: How to write a sql query to remove non-printable characters in a column but keeping the carriage returns

    declare @string varchar(200)

    set @string = 'this $%^^&is%^& s2342om23&&({}e c76l232e+_+a#n/ c][#o''d#e'

    select cast(cast((select substring(@string,n,1)

    from numbers

    where n <= len(@string)

    and substring(@string,n,1) like '[a-z ]' for xml path('')) as xml)as varchar(max))

    Thanks,
    Sharp


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Edited by Brent Serbus Friday, September 23, 2011 12:27 AM edit
    • Marked as answer by Eileen Zhao Wednesday, September 28, 2011 2:09 AM
    Thursday, September 22, 2011 5:29 AM
  • You can check what is in a string with binary translation as follows:

    -- Check for non-printable characters in a string
    DECLARE @string varchar(256) = 'New'+char(21)+'York'+char(5)+'City'
    SELECT @string -- NewYorkCity
    SELECT CONVERT(binary(13), @string) -- 0x4E657715596F726B0543697479
    
    

    The hex 15 and 05 are the non-printable characters.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Edited by Brent Serbus Friday, September 23, 2011 12:54 AM edit
    Thursday, September 22, 2011 11:57 PM
  • You can check what is in a string with binary translation as follows:

    -- Check for non-printable characters in a string
    DECLARE @string varchar(256) = 'New'+char(21)+'York'+char(5)+'City'
    SELECT @string -- NewYorkCity
    SELECT CONVERT(binary(13), @string) -- 0x4E657715596F726B0543697479
    
    

    The hex 15 and 05 are the non-printable characters.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Edited by Brent Serbus Friday, September 23, 2011 12:54 AM restored html
    Thursday, September 22, 2011 11:57 PM