Remove any special characters from CSV file import

• berki

  Mr or Mrs. 500

  Points: 510

  More actions

  July 21, 2014 at 8:10 am

  #291345

  Hi,

  I'm trying to import a CSV file however it contains the OBJ character 

  http://www.fileformat.info/info/unicode/char/fffc/index.htm

  I've created a script task to remove this but it doesn't seem to be working:

  using System;

  using System.Data;

  using System.Text.RegularExpressions;

  using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

  using Microsoft.SqlServer.Dts.Runtime.Wrapper;

  [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

  public class ScriptMain : UserComponent

  {

  public override void PreExecute()

  {

  base.PreExecute();

  }

  public override void PostExecute()

  {

  base.PostExecute();

  }

  string toreplace = "[\uFFFC]";

  string replacewith = "";

  public override void Input0_ProcessInputRow(Input0Buffer Row)

  {

  Regex reg = new Regex(toreplace);

  Row.CSVFileRows = reg.Replace(Row.CSVFileRows, replacewith);

  }

  }

  Is there anyway to only allow standard ASCII characters and remove any non printable ASCII characters?

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  July 21, 2014 at 8:28 am

  #1731034

  Not sure whether it will work, but I think your regex may be incorrect.

  string toreplace = "[\uFFFC]";

  Can you try removing the square brackets?

  string toreplace = "\uFFFC";

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel