Post reply

Export nvarchar(1000) fields with carriage returns

  • March 1, 2009 at 4:35 pm

    #128107

    We have a web form that uses a lot of multi-line text boxes to gather user text input. Here is a simple example:

    Question:

    Describe your occupation?

    Answer:

    I work in administration .......etc,etc,etc....

    .....and i am looking for a new position.....etc...etc...

    Issue: Since these are open ended free text fields, some of the data contains carriage returns which are not shown in the SQL tables. When exporting these fields using SSIS packages (even with Data Conversions) the .CSV flat file (or Excel) columns do not align correctly from the export.

    Is this related to ASCII return characters and can i export to CSV?

  • March 2, 2009 at 7:11 am

    #951841

    Carriage Return is ASCII 13 so in your query you could do:

    Replace(CHAR(13), ' ', [column with carriage return]

    This will remove the carriage return.

    You just need to be careful because sometimes it is a carriage return and line feed (ASCII 10).

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 2, 2009 at 3:13 pm

    #952143

    Sounds like i need to find and replace ASCII coded character for return codes as well as line feeds?

    Does this mean adding Derived Column transformations (i think that's what they are called????)?

    So the steps might be:

    1) OLEDB Source - mytablename

    2) Derived column Transformation (Copy column with new expression as mentioned above for find and replace ASCII characters)

    3) Flat File (CSV) destination

    I'll give this a try anyway and post back with my results.

  • March 2, 2009 at 3:29 pm

    #952149

    The expression I gave above is in T-SQL syntax which means I'd do it as part of my OLE DB source query, if your source is SQL Server. Another note is that I forgot the closing parenthesis in the earlier post.

    You could do it in the dataflow with a derived column but I think it will work better in the query if your source is SQL Server.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 2, 2009 at 3:53 pm

    #952166

    Thanks Jack.

    I understand using the OLEDB source T-SQL statements would probably be better and more efficient. I might give them both a try and see what works. Then test the performance.

    Good tip!

  • March 3, 2009 at 3:19 pm

    #952750

    Perfect! It worked exactly as i needed using T-SQL REPLACE function. One correction to the format:

    Replace(Replace([ColumnName],CHAR(13), ' '),CHAR(10),'')

    There was in fact both scenarios where Line Breaks (CHAR(10)) as well as Return Codes (CHAR(13)) existed in some column data.

    Executing a replace command corrected all exported data from OLEDB SQL into a Flat CSV File. Now it is easily imported into Excel by the client with proper column formatting (even for columns with >255 characters).

    Thanks for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

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