C# How to remove extra comma form a specific line in a comma delimited csv file before the csvline.split(',')

Question

Greeting I have asked help before about parsing and it has helped a lot. I need help once again after several days finding a logical way to solve this.

Bellow the lines im parsing

 5/13/2019,"PHONE SERVICE,/GUIA:54 REF:0000000005556 CIE:041",872,,

on this line -->(5/13/2019,"PHONE SERVICE,/GUIA:54 REF:0000000005556 CIE:041",872,,) <-- after Service that is the extra comma that is making my code throw an error

here is the code

      class Program
        {
         static void Main(string[] args)
        {
        ParserCSV();
        Console.WriteLine("...");
        Console.ReadLine();
    }


    private static void ParserCSV()
    {

        Console.WriteLine("Parsing the csv file");

        List<clsEstadoCuenta> resp = new List<clsEstadoCuenta>();
        var csvLines = File.ReadAllLines("D:\\ztemp\\New 
        folder\\ParserTestMX.csv");
        clsEstadoCuenta nR = new clsEstadoCuenta();


        foreach (var lineas in csvLines.Skip(1))
        {

            var campos = lineas.Split(',');

            campos[3] = campos[2] + campos[3];
            campos[1] = campos[1] + campos[2];
            string nuevaLinea = "";
            for (int i = 3; i < campos.Count(); i++)
            {
                if (!string.IsNullOrEmpty(campos[i]))
                {
                    if (campos[i][0] == '\"' || campos[i][0] == '"')
                    {
                        campos[i] = campos[i] + campos[i + 1];
                        campos[i + 1] = "";
                        campos[i] = campos[i].Replace("\"", "");
                    }
                }
                nuevaLinea += $"{campos[i]} \t";
            }
            //campos[3] = campos[2];
            var tmpFecha = campos[0].Split('/');
            nR.FechaTransaccion = new 
            DateTime(Convert.ToInt32(tmpFecha[2]), 
            Convert.ToInt32(tmpFecha[0]), Convert.ToInt32(tmpFecha[1]));
            var tmpDesc = campos[1].Split('/');
            nR.Descripcion = (String.IsNullOrEmpty(tmpDesc[0])) ? "" : 
            tmpDesc[0];
            nR.Referencia = (String.IsNullOrEmpty(tmpDesc[1])) ? "" : 
             tmpDesc[1];

            nR.Debito = (String.IsNullOrEmpty(campos[3])) ? 0 : 
             Convert.ToDouble(campos[3]);
            nR.Credito = (String.IsNullOrEmpty(campos[4])) ? 0 : 
             Convert.ToDouble(campos[4]);

            resp.Add(nR);
            if (nR.FechaTransaccion==null)
            {
                break;
            }



            }

         Console.WriteLine("Parsing has ended, we have {0} rows \n", 
         resp.Count);
         foreach (var item in resp)
         {
            Console.WriteLine($"{item.FechaTransaccion} |  
          {item.Descripcion} | {item.Referencia}|{item.Debito}| 
          {item.Credito} ");
         }


       }


    class clsEstadoCuenta
    {
        private string _NumeroCuenta;

        public string NumeroCuenta
        {
            get { return _NumeroCuenta; }
            set { _NumeroCuenta = value; }
        }
        private int _CodigoPais;

        public int CodigoPais
        {
            get { return _CodigoPais; }
            set { _CodigoPais = value; }
        }

        private string _Banco;

        public string Banco
        {
            get { return _Banco; }
            set { _Banco = value; }
        }

        private string _Moneda;

        public string Moneda
        {
            get { return _Moneda; }
            set { _Moneda = value; }
        }

        private double _TasaCambio;

        public double TasaCambio
        {
            get { return _TasaCambio; }
            set { _TasaCambio = value; }
        }

        private double _Debito;

        public double Debito
        {
            get { return _Debito; }
            set { _Debito = value; }
        }

        private double _Credito;

        public double Credito
        {
            get { return _Credito; }
            set { _Credito = value; }
        }

        private DateTime _FechaTrasaccion;

        public DateTime FechaTransaccion
        {
            get { return _FechaTrasaccion; }
            set { _FechaTrasaccion = value; }
        }

        private string _Payee;

        public string Payee
        {
            get { return _Payee; }
            set { _Payee = value; }
        }

        private string _Descripcion;

        public string Descripcion
        {
            get { return _Descripcion; }
            set { _Descripcion = value; }
        }

        private string _Referencia;

        public string Referencia
        {
            get { return _Referencia; }
            set { _Referencia = value; }
        }

        private string _CodigoBancario;

        public string CodigoBancario
        {
            get { return _CodigoBancario; }
            set { _CodigoBancario = value; }
        }

        private string _Categoria;

        public string Categoria
        {
            get { return _Categoria; }
            set { _Categoria = value; }
        }

        private string _Sector;

        public string Sector
        {
            get { return _Sector; }
            set { _Sector = value; }
        }

        private double _ValorLocal;

        public double ValorLocal
        {
            get
            {
                _ValorLocal = Credito - Debito;
                return _ValorLocal;
            }
            //set { _ValorLocal = value; }
        }

        private double _ValorDolares;

        public double ValorDolares
        {
            get
            {
                _ValorDolares = ValorLocal / TasaCambio;
                return _ValorDolares;
            }
            // set { _ValorDolares = value; }
        }

        private string _NombreEmpresa;

        public string NombreEmpresa
        {
            get { return _NombreEmpresa; }
            set { _NombreEmpresa = value; }
        }

      }
    }
   }

Please Help, I have been trying to find the best logic way to ignore the comma on that line. But I cant since the line is already splited before I can do anything

If screenshots are necessary, please at least crop them. – 15ee8f99-57ff-4f92-890c-b56153 Jun 20 '19 at 18:52 — 15ee8f99-57ff-4f92-890c-b56153, Jun 20 '19 at 18:52
sorry, I will remember that – CSharpRookieEd Jun 20 '19 at 19:05 — CSharpRookieEd, Jun 20 '19 at 19:05

score 1 · Accepted Answer · answered Jun 20 '19 at 18:50

1

Iterate Over Everyline and parse it using

string currentRow = eachLineToParse;
Regex CSVParser = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
String[] rowish = CSVParser.Split(currentRow);

rowish should return an array with your values.

answered Jun 20 '19 at 18:50

Digit Plays

79
10

I was able to implement this suggestion. Thank you so much for your help – CSharpRookieEd Jun 21 '19 at 16:34

score 0 · Answer 2 · answered Jun 20 '19 at 19:46

In the line with the extra comma, the value that contains the comma is enclosed in quotation marks.

 5/13/2019,SPEI ENVIADO SANTANDER/03GASOLINA MAYO (3),"1,000.00",,
 5/13/2019,"TELEFONOS DE MEXICO,/GUIA:54 REF:0000000005556 CIE:041",872,,
           ^                                                      ^

That's a standard way to indicate that whatever is inside the quotes is a single value and shouldn't be split up according any commas inside it.

That means you need to write your code to split based on commas except when they're inside quotation marks. To do that... don't. Just use an existing library that handles that for you. Try CsvHelper.

First define a class that defines the data contained in each row:

public class Data
{ 
    [Name("D?")]
    public DateTime Date { get; set; }

    [Name("Concepto / Referencia")]
    public string Conceptio { get; set; }

    [Name("cargo")]
    public string Cargo { get; set; }

    [Name("Abono")]
    public string Abono { get; set; }
}

If the property name matches the column header we don't need the CsvHelper.Configuration.Attributes.NameAttribute, but we can't name a property D? so we need the attribute.

Then the code to read it looks like this:

public Data[] ReadDate(string fileName)
{
    using (var file = File.OpenText(fileName))
    {
        using (var reader = new CsvReader(file))
        {
            return reader.GetRecords<Data>().ToArray();
        }
    }
}

Now we've got our CSV data parsed into an array of Data and we can do whatever we want with that.

I didn't even add any special handling for the quotation makes because that's the default behavior of CsvHelper. It doesn't try to split values inside quotes. If you need to you can specify a different character, but in this case we're all set.

If this helps, It would be good to edit your question to take out the pictures and all the extra code. — Scott Hannen, Jun 20 '19 at 19:47

C# How to remove extra comma form a specific line in a comma delimited csv file before the csvline.split(',')

2 Answers2