C# - Separate/cut string by commas (but with monetary value included in the string)

Question

I have a problem reading a txt file that has its columns separated by comma but also has monetary values that are being cut and should not, because they are in quotes. Follow example of the line:

Nome, Idade, Valor a receber, Valor pendente, descricao
Teste, 25, "1.234,30", "987,90", teste

after having read the file

string[] todasLinhas = File.ReadAllLines(arquivo, Encoding.GetEncoding("iso-8859-1"));

I try to cut by comma

foreach (string linha in todasLinhas)
{
   string[] colunas = linha.Split(',');
}

the result is this way:

Teste
25
"1234
30"
"987
90"
teste

Is there any way to make Split not cut when the character is in quotes? or if there is any other solution that you can share?

Answer 1

For your case of your exeplo I recommend doing split comma with space:

string[] colunas = linha.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries);

If you prefer to use Regex, can split by commas not followed by number or letter (if words also have comma), but will be less efficient and for your example the result is equal:

string[] colunas = new System.Text.RegularExpressions.Regex(@",[^\d\w]").Split(linha);

If the columns are separated by comma only, you can give a split comma and then concatenate the positions starting with double quotes:

var colunas = new List<string>();
var linhasSplit = linha.Split(',');
for (int i = 0; i < linhasSplit.Length; i++)
{
    if(linhasSplit[i][0] == '"')
        colunas.Add(linhasSplit[i] + linhasSplit[++i]);
    else
        colunas.Add(linhasSplit[i]);
}