title: How to remove commas between a piped strings in a csv file in a eg |Ana "ana" Ana|, |adda,adda|
body: <p>I am working on an ssis integration and wrote a script task in c# for automating my imports from csv file to my DBs. It works great but I need help with removing commas from strings in pipes(|aaa,aaa| or |a|,|a|) in the csv. For example "Address, city wide". I want a function that can remove that comma(,). I would  paste a snippet of my code and what I have done so far.</p>
  
  <pre><code>#
  region Namespaces
  using System;
  using System.Data;
  using Microsoft.SqlServer.Dts.Runtime;
  using System.Windows.Forms;
  using System.Data.SqlClient;
  using System.IO;
  //using CsvHelper.Configuration;
  #
  endregion
  
  namespace ST_7ce5ad6fbc104157b534f4eb484a4417 {
   [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
   public partial class ScriptMain: Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {
    public void Main() {
     string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
     try {
      //Declare Variables
      string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();
      string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
      string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
      string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
      string ColumnsDataType = Dts.Variables["User::ColumnsDataType"].Value.ToString();
      string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
      SqlConnection myADONETConnection = new SqlConnection();
      myADONETConnection = (SqlConnection)
       (Dts.Connections["moviesdb"].AcquireConnection(Dts.Transaction) as SqlConnection);
      //Reading file names one by one
      string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
      foreach(string fileName in fileEntries) {
       //Writing Data of File Into Table
       string TableName = "";
       int counter = 0;
       string line;
       string ColumnList = "";
       //MessageBox.Show(fileName);
       System.IO.StreamReader SourceFile =
        new System.IO.StreamReader(fileName);
       while ((line = SourceFile.ReadLine()) != null) {
        if (counter == 0) {
         ColumnList = "[" + line.Replace("\"", "").Replace(FileDelimiter, "],[") + "]";
         //MessageBox.Show(ColumnList);
         //"[" + line.Replace(FileDelimiter, "],[") + "]";
         TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\\", ""));
         string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SchemaName + "].";
         CreateTableStatement += "[" + TableName + "]')";
         CreateTableStatement += " AND type in (N'U'))DROP TABLE [" + SchemaName + "].";
         CreateTableStatement += "[" + TableName + "]  Create Table " + SchemaName + ".[" + TableName + "]";
         CreateTableStatement += "([" + line.Replace("\"", "").Replace(FileDelimiter, "] " + ColumnsDataType + ",[") + "] " + ColumnsDataType + ")";
         SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, myADONETConnection);
         CreateTableCmd.ExecuteNonQuery();
         //MessageBox.Show(CreateTableStatement);
        } else {
         string query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
         //query += "VALUES('" + line.Replace(FileDelimiter, "','").Replace("\"", "") + "')";
         //query += "VALUES('" + line.Replace(FileDelimiter, "','").Replace("\"", "").Replace("\"'\"", "") + "')";
         query += "VALUES('" + line.Replace("'", "").Replace(FileDelimiter, "','").Replace("\"", "") + "')";
         // MessageBox.Show(query.ToString());
         SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
         myCommand1.ExecuteNonQuery();
        }
        counter++;
       }
       SourceFile.Close();
       //move the file to archive folder after adding datetime to it
       File.Move(fileName, ArchiveFolder + "\\" + (fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "") + "_" + datetime + FileExtension);
       Dts.TaskResult = (int) ScriptResults.Success;
      }
     } catch (Exception exception) {
      // Create Log File for Errors
      using(StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() +
       "\\" + "ErrorLog_" + datetime + ".log")) {
       sw.WriteLine(exception.ToString());
       Dts.TaskResult = (int) ScriptResults.Failure;
      }
     }
    }#
    region ScriptResults declaration
    enum ScriptResults {
     Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
      Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };#
    endregion
   }
  }
  </code></pre>
  
  <p>It actually does what I want and imports successfully, but I need further assistance in tweaking the code to replace commas between strings in pipes in the csv file.</p>