CSV Carriage Return Character

Question

Hi

I have a CSV output on one of my applications. This produces a file from of web form data.

In some cases I am getting a carriage return character in my notes field. This causes an error when importing the file. I would like to remove this character.

The issue appears to be happening when users paste information into the form from word documents or holding down the shift key and pressing enter.

The field is ntext and populated in a multi line text box control.

I have been trying to remove this with a replace function but some carriage return characters seem to be getting through.

SQL

REPLACE(Fieldname), CHAR(13) + CHAR(10), ' ') AS new_Fieldname

Answer 1

It may be best to replace the characters separately, as they do not always occur together or in that order:

REPLACE(REPLACE(Fieldname, CHAR(13),' '), CHAR(10), ' ') AS new_Fieldname

Answer 2

In Word, there are different kinds of new-line characters. Maybe you should also search/replace the other ones.

I'm not sure which are all the different possibilities, at least the paragraph mark is one that I know of.

Answer 3

Note that you may have a carriage return + line feed, or just a carriage return (depending on the source platform, the source of the data etc.). So you will probably need to handle both cases.

Answer 4

You can read CSVs with carriage return in them. The carriage return should be in a string represented field (i.e. surrounded by quotes). This allows you to read lines and incldue them in your field. If you are reading your CSV one line at a time, you need to maintain state between lines and append the data as necessary.

In .Net, the easiest way to read a CSV is using the Microsoft.VisualBasic.FileIO.textFileParser object (yes, you can use this in C# if you add a reference). This reads even the nastiest CSVs I've thrown at it with ease.