title: Replacing Special Characters in a CSV file
body: <p>After reading <a href="https://stackoverflow.com/questions/2785805/hyphen-vs-dash-replace-dash-with-hyphen">this</a> StackOverflow article, I realized that I was having the same issue with my CSV file, namely, where someone copy and pasted a dash/hypen (-) character from Word into an Excel. </p>
  
  <p>I was creating my own CSV file with data read from an Excel spreadsheet and I was noticing strange characters like â€ appearing in Excel that did not appear when viewing in Notepad. When I used SSIS to transfer the CSV file to a SQL Server table, the weird â€ persisted there too. After examining the ASC value of each, I decided to replace ASC 150 (Dash) with and ASC 45 (hyphen) character and this corrected the problem and the hypen appeared normal when viewing in in Excel also. </p>
  
  <p>This led me to question what other characters might also need to be replaced and if there was a general routine that could be employed to safeguard my CSV file from having similar issues.</p>
  
  <p>This is what I am currently doing for every value that I want to write to a CSV file. Note that my getCharacterString function is similar to VB's CHR function in returning the ASC charact associated with an ASCII value.</p>
  
  <pre><code>    /// &lt;summary&gt;
      /// Locates occurrences of targeted special characters found in the input string and replaces each with a space.
      /// &lt;/summary&gt;
      /// &lt;param name="inputString"&gt;The input string.&lt;/param&gt;
      /// &lt;returns&gt;The updated inputString.&lt;/returns&gt;
      private string ReplaceSpecialCharacters(string inputString)
      {
          StringBuilder stringBuilder = new StringBuilder(inputString);
  
          const string doubleQuoteCharacter = "\"";
  
          stringBuilder.Replace("\r\n", " "); // Carriage Return/Line Feed characters replaced with single space
          stringBuilder.Replace("\r", " "); // Carriage Return replaced with one space if only \r is found
          stringBuilder.Replace("\n", " "); // Likewise, Line Feed with a single space           
          stringBuilder.Replace(this.columnSeparator, " "); // Tab            
          stringBuilder.Replace(Character.GetCharacterString(150), Character.GetCharacterString(45)); // Replace Dash with Hypen
          stringBuilder.Replace(Character.GetCharacterString(147), doubleQuoteCharacter); // Replace angled left quote, “, with simple double quote, ".
          stringBuilder.Replace(Character.GetCharacterString(148), doubleQuoteCharacter); // Replace angled left quote, “, with simple double quote, ".
  
          return stringBuilder.ToString();
      }         
  </code></pre>
  
  <p>Here are the conversion functions I found:</p>
  
  <pre><code>// -----------------------------------------------------------------------
  // &lt;copyright file="Character.cs" company="Joes bar and grill"&gt;
  // TODO: Update copyright text.
  // &lt;/copyright&gt;
  // -----------------------------------------------------------------------
  
  namespace JoesBarAndGrill.FinanceIT.HhsSweeper
  {
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
  
      /// &lt;summary&gt;
      /// TODO: Update summary.
      /// &lt;/summary&gt;
      public static class Character
      {
          /// &lt;summary&gt;
          /// See http://bytes.com/topic/c-sharp/answers/273734-c-chr-asc-function-equivalents-undocumented-truth.
          /// &lt;/summary&gt;
          /// &lt;param name="asciiValue"&gt;&lt;/param&gt;
          /// &lt;returns&gt;&lt;/returns&gt;
          public static string GetCharacterString(int asciiValue)
          {
              if ((asciiValue &lt; 0) || (asciiValue &gt; 255))
              {
                  throw new ArgumentOutOfRangeException("asciiValue", asciiValue, "Must be between 0 and 255.");
              }
              byte[] bytBuffer = new byte[] { (byte)asciiValue };
              return Encoding.GetEncoding(1252).GetString(bytBuffer);
          }
  
          public static int GetAsciiValue(string character)
          {
              if (character.Length != 1)
              {
                  throw new ArgumentOutOfRangeException("character", character, "Must be a single character.");
              }
              char[] chrBuffer = { Convert.ToChar(character) };
              byte[] bytBuffer = Encoding.GetEncoding(1252).GetBytes(chrBuffer);
              return (int)bytBuffer[0];
          }
      }
  }
  </code></pre>
  
  <p>Again, my question is this:</p>
  
  <p>What do I have to do to come up with a general approach to identify all characters that may end up having conversion issues like this? I think I may have identified the common ones only. I would also be interested in having people help me come up with a more complete list of targetcharacters to replace and proposed substitution characters.</p>
  
  <p>I am not sure if this is related, but in case someone suggests that I use a text delimiter in the CSV file, I am not using a text qualifier because I am convinced that SSIS 2008 does not handle them correctly (see <a href="https://stackoverflow.com/questions/13239530/using-excel-to-create-a-csv-file-with-special-characters-and-then-importing-it-i">a previous question of mine</a>)</p>