Remove commas between double quotes in a CSV file

Question

I have a CSV file that contains several lines like this one :

"bar","foo, bar","18","07/09/2012 02:08:16","payments, recent","payments, all"

Some values contain commas, and I need to remove these commas in order to obtain this result :

"bar","foo bar","18","07/09/2012 02:08:16","payments recent","payments all"

I started with this regex "^(\".+\"\\,?)+$" but it becomes too much complicated for me.

The final goal is to split that string :

string content = reader.ReadToEnd();

string[] lignes = contenu.Split(new[] { Environment.NewLine }, StringSplitOptions.None);

for (int i = 1; i < lignes.Length; i++)
{
    // REMOVE COMMAS

    string[] values = csv.Split(new[] {','});

    // do something
}

reader.Close();

Thanks.

this might help...keep commas preceeded by a double quote and succeeded by a double quote, remove all other commas — dannyrosalex, Oct 08 '12 at 15:35
This may help: http://stackoverflow.com/questions/5202005/regex-how-to-remove-comma-which-is-between-and — Saurabh R S, Oct 08 '12 at 15:42

score 6 · Accepted Answer · answered Oct 08 '12 at 15:33

6

Instead of hand parsing your valid CSV file (commas are allowed within quoted fields), you should be using a CSV parser that knows how to handle these.

A popular library is FileHelpers and there is the TextFieldParser in the Microsoft.VisualBasic.FileIO namespace.

answered Oct 08 '12 at 15:33

Oded

489,969
99
883
1,009

Thanks. I will use a TextFieldParser. – Anthony Simmon Oct 08 '12 at 16:13

score 1 · Answer 2 · answered Oct 08 '12 at 15:36

1

Use the TextFieldParser class. It can handle commmas inside quotation marks.

http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser.aspx

answered Oct 08 '12 at 15:36

Chris Dunaway

10,974
4
36
48

score 1 · Answer 3 · answered Oct 08 '12 at 16:01

You could do some simple massaging of the data first, something like this maybe:

   string content = "\"bar\",\"foo, bar\",\"18\",\"07/09/2012 02:08:16\",\"payments, recent\",\"payments, all\"";

    content = content.Replace("\",\"", "~");
    content = content.Replace(",", ""); // Safe to remove commas now.
    content = content.Replace("\"", ""); // Get rid of left over double quotes.

    string[] values = content.Split(new[] { '~' });

score 0 · Answer 4 · answered May 31 '14 at 05:54

You can use this bellow please ignore method name and import file to your table

  private void ImportCSV(string filePath = @"E:\nucc_taxonomy_140.csv", string tableName = "TempTaxonomyCodes")
    {
        string tempPath = System.IO.Path.GetDirectoryName(filePath);
        string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
        OdbcConnection conn = new OdbcConnection(strConn);
        OdbcDataAdapter da = new OdbcDataAdapter("Select * from " + System.IO.Path.GetFileName(filePath), conn);
        DataTable dt = new DataTable();
        da.Fill(dt);

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationSettings.AppSettings["dbConnectionString"]))
        {
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = 50;
            bulkCopy.WriteToServer(dt);
        }

    }

Remove commas between double quotes in a CSV file

4 Answers4