Export nvarchar(1000) fields with carriage returns

Question

Post reply

Export nvarchar(1000) fields with carriage returns

bkirk

SSCrazy

Points: 2824
More actions
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?

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

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

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

bkirk SSCrazy Points: 2824 More actions · Answer 2

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.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

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

bkirk SSCrazy Points: 2824 More actions · Answer 4

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!

bkirk SSCrazy Points: 2824 More actions · Answer 5

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.