Parsing commas in field with TextFieldParser causes extra column

Question

Following on a previous thread Object reference on array with regex.replace()

I have few columns in cpath which contain fields such as street,""test,format"", casio . Unfortunately,

csvReader.SetDelimiters(new string[] { "," });

adds empty extra columns to the DataTable csvData because of the comma between test and format. Hence the the following interrogation:

Does a trick exist to remove the comma from """test,format""" so as to get street,""test format"", casio in csvData?

Thanks in advance

EDIT

private static List<string[]> RemoveComaDataFromCSVFile(string csv_file_path)
    {
        List<string[]> allLineFields = new List<string[]>();

        try
        {
            using (TextReader sr = new StringReader(csv_file_path))
            using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
            {
                csvReader.Delimiters=new[] { "," };
                csvReader.HasFieldsEnclosedInQuotes = true;
                while (!csvReader.EndOfData)
                {

                    string[] fieldData = csvReader.ReadFields();
                    string pattern = "\"";
                    string replacement = "";
                    Regex rgx = new Regex(pattern);
                    //Making empty value as null
                    for (int i = 0; i < fieldData.Length; i++)
                    {
                        //Remove quotation marks on Fields
                        fieldData[i] = Regex.Replace(fieldData[i], pattern, replacement);
                        if (fieldData[i] == "")
                        {
                            fieldData[i] = null;
                        }
                    }
                    //csvData.Rows.Add(fieldData);
                    allLineFields.Add(fieldData);
                }
            }
        }
        catch (Exception ex)
        {
        }
        return allLineFields;
    }

`TextFieldParser` has a property `HasFieldsEnclosedInQuotes` which you should set to `true`. Then the comma in `"test,format"` doesn't matter. You should also show the code that generates the `DataTable`. — Tim Schmelter, Apr 14 '16 at 09:44
@TimSchmelter: because i receive csv `data` containing the `coma delimiter` and unfortunately i cannot change it. however i've never expected that some fields could contain `,` messing everything up. so I am really stuck... — dark.vador, Apr 14 '16 at 09:47
Have you read the rest of my comment ;) `TextFieldParser` has no problems with comma if the field is wrapped in quotes — Tim Schmelter, Apr 14 '16 at 09:48
@dark.vador Did you try the flag? If the comma is *not* quoted, it *is* a new column. If a field *is* quoted, it shouldn't matter whether it contains a comma or not — Panagiotis Kanavos, Apr 14 '16 at 09:49

Tim Schmelter · Answer 1 · 2016-04-14T09:59:33.847

2

TextFieldParser has a property HasFieldsEnclosedInQuotes which you should set to true. Then the comma in "test,format" doesn't matter because it will be interpreted as a single value.

Example:

List<string[]> allLineFields = new List<string[]>();
string sampleLine = @"street,""test,format"", casio";
using (TextReader sr = new StringReader(sampleLine))
using (TextFieldParser parser = new TextFieldParser(sr))
{
    parser.HasFieldsEnclosedInQuotes = true;
    parser.Delimiters = new[] { "," };
    while (!parser.EndOfData)
    {
        string[] fields = parser.ReadFields();
        allLineFields.Add(fields);
    }
}

Result is a single string[] with three fields:

    [0] {string[3]}     string[]
    [0] "street"        string
    [1] "test,format"   string
    [2] "casio"         string

edited Apr 14 '16 at 09:59

answered Apr 14 '16 at 09:50

Tim Schmelter

450,073
74
686
939

Thanks Tim, will try this by hoping it won't have an incidence on function `public static void InsertDataSQLBulkCopy(csvData)` using this `Datable` as argument. Best, – dark.vador Apr 14 '16 at 09:58
@dark.vador: could only be a positive impact if the `DataTable` contains correct data. But i dont know how you generate the `DataTable`, so there might be still an issue. – Tim Schmelter Apr 14 '16 at 10:01
sure Tim. Thanks for your promptness and help on this. – dark.vador Apr 14 '16 at 10:04
following your suggestion i 've edited the post with function `RemoveComaDataFromCSVFile(string csv_file_path)` but the issue is still persisting... – dark.vador Apr 14 '16 at 11:14
@dark.vador: so your method reads a csv file and removes additional leading and tralining quotes? You don't need regex, use: `string newData=fieldData[i].Trim().Trim('"'); fieldData[i]=newData.Length==0?null:newData;`. But maybe you need to show your `DataTable` code that has the issue. – Tim Schmelter Apr 14 '16 at 11:39
fair enough, thanks Tim for the tip. Unfortunately the the edited method does not read correctly rows from `Main()` (e.g `Console.WriteLine(allLineFields[192]`), outputting weird `System.String[]` and I was wondering if everything was correct from your suggestion. As i was not familiar with `List` class as opposed to `Datatable`, i will therefore keep working on it... – dark.vador Apr 14 '16 at 11:52
@dark.vador: i see another possible issue if your field contains double quotes like `street,""test,format"", casio` as opposed to `street,"test,format", casio`. If that's the case it should throw a `MalformedLineException` which you should handle in the loop. You should never have empty catch blocks since it's just swallowing it, so you won't even recognize that something isn't working as desired. – Tim Schmelter Apr 14 '16 at 12:07
sure Tim, was hoping the field could contain a single quote, then i would have been quickly sorted even using a small `VBA macro`: e.g replace `"`, by `'` and keep this as `text` should not have been affected by the delimiter. cheers. – dark.vador Apr 14 '16 at 12:13
@TimSchmelter I have the similar problem but with a double quote in it. `"EquipDesc[14]","SMU Scanner"Program","PV1500"` Look at the second element. This is the exact csv value I open in notepad. I am using the exact method as in the question but getting `MalFormedLine` exception. Any way to solve this? – Hari Mar 16 '18 at 06:50

Parsing commas in field with TextFieldParser causes extra column

1 Answers1