Split does not work as expected with commas

Question

I need to write a CSV Parser I am now trying to separat the fields to manipulate them.

Sample CSV: mitarbeiter^tagesdatum^lohnart^kostenstelle^kostentraeger^menge^betrag^belegnummer 11005^23.01.2018^1^^31810020^5,00^^ 11081^23.01.2018^1^^31810020^5,00^^

As you can see, there a several empty cells.

I am doing the following:

 using (CsvFileReader reader = new CsvFileReader(path))
        {
            CsvRow row = new CsvRow();                
            while (reader.ReadRow(row))
            {
                foreach (string s in row)
                {
                    csvROW.Add(new aCSVROW());
                    string[] items = s.Split(new char[] { '^' }, StringSplitOptions.None);
                    csvROW[0].mitarbeiter = items[0];
                    csvROW[0].tagesdatum = items[1];
                    csvROW[0].lohnart = items[2];
                    csvROW[0].kostenstelle = items[3];
                    csvROW[0].kostentraeger = items[4];
                    csvROW[0].menge = items[5];
                    csvROW[0].betrag = items[6];
                    csvROW[0].belegnummer = items[7];
                }                   
            }
        }

Problem:

It seems that Split stops after the comma (5,00). The separator is ^ ... is there a reason why? I tried several things without success...

Thank you so much!

Could `CsvRow.ReadRow` split on comma because it is assuming a csv? — Davin Tryon, Mar 12 '18 at 14:17
Looks like it's splitting fields on a `,` then you're splitting the first field on a `^`, the second `s in row` will probably get you `00^^ 11081^23.01.2018^1^^31810020^5` — phuzi, Mar 12 '18 at 14:17
is that one line or 2? theres only 5 with words.. so if the csv splitter is picking those up as titles it would explain why it only gets 5 values — BugFinder, Mar 12 '18 at 14:20
@DavinTryon: Thank you! That could do the trick ... I am trying to fix it and will give you feedback. Update: Jup, ReadRow is splitting on comma ... thank you so much! — SpiDey, Mar 12 '18 at 14:20
Um. Is `CsvRow` meant to be _one_ row, or _all_ rows? Because you seem to always just fill the first object inside it, despite adding new `aCSVROW` objects all the time. — Nyerguds, Mar 12 '18 at 15:26

Paul Kertscher · Answer 1 · 2018-03-12T14:30:08.723

CsvFileReader reads rows from a CSV file and then strings within that row. What else do you expect the CsvFileReader to do than separating the row?

After reading the second line, row will have the contents

11005^23.01.2018^1^^31810020^5

and

00^^

When you split the first row by ^, the last entry of the resulting array will be "5". Anyway, your code will throw, because you are trying to access items exceeding the bounds of the array.

I don't know CsvFileReader. Maybe you can pass ^ as a separator and spare the splitting of the string. Anyway, you could use a StreamReader, too. This will work much more like you expected.

using (StreamReader reader = new StreamReader(path))
{              
    while (!reader.EndOfStream)
    {
        var csvLine = reader.ReadLine();
        csvROW.Add(new aCSVROW());
        string[] items = csvLine.Split(new char[] { '^' }, StringSplitOptions.None);
        csvROW[0].mitarbeiter = items[0];
        csvROW[0].tagesdatum = items[1];
        csvROW[0].lohnart = items[2];
        csvROW[0].kostenstelle = items[3];
        csvROW[0].kostentraeger = items[4];
        csvROW[0].menge = items[5];
        csvROW[0].betrag = items[6];
        csvROW[0].belegnummer = items[7];
    }                   
}

Nyerguds · Answer 2 · 2018-03-12T15:34:26.300

Is CsvRow meant to be the data of all rows, or of one row? Because as it is, you keep adding a new aCSVROW object into csvROW for each read line, but you keep replacing the data on just csvROW[0], the first inserted aCSVROW. This means that in the end, you will have a lot of rows that all have no data in them, except for the one on index 0, that had its properties overwritten on each iteration, and ends up containing the data of the last read row.

Also, despite using a CsvReader class, you are using plain normal String.Split to actually separate the fields. Surely that's what the CsvReader class is for?

Personally, I always use the TextFieldParser, from the Microsoft.VisualBasic.FileIO namespace. It has the advantage it's completely native in the .Net framework, and you can simply tell it which separator to use.

This function can get the data out of it as simple List<String[]>:

A: Using C# to search a CSV file and pull the value in the column next to it

Once you have your data, you can paste it into objects however you want.

List<String[]> lines = SplitFile(path, textEncoding, "^");
// I assume "CsvRow" is some kind of container for multiple rows?
// Looks like pretty bad naming to me...
CsvRow allRows = new CsvRow();
foreach (String items in lines)
{
    // Create new object, and add it to list.
    aCSVROW row = new aCSVROW();
    csvROW.Add(row);
    // Fill the actual newly created object, not the first object in allRows.
    // conside adding index checks here though to avoid index out of range exceptions.
    row.mitarbeiter = items[0];
    row.tagesdatum = items[1];
    row.lohnart = items[2];
    row.kostenstelle = items[3];
    row.kostentraeger = items[4];
    row.menge = items[5];
    row.betrag = items[6];
    row.belegnummer = items[7];
}
// Done. All rows added to allRows.

score -2 · Answer 3 · answered Mar 12 '18 at 14:23

 CsvRow row = new CsvRow();                
            while (reader.ReadRow(row))
            {
                foreach (string s in row)
                {
                    csvROW.Add(new aCSVROW());
                    s.Split("^","");
csvROW[0].mitarbeiter = items[0];
                    csvROW[0].tagesdatum = items[1];
                    csvROW[0].lohnart = items[2];
                    csvROW[0].kostenstelle = items[3];
                    csvROW[0].kostentraeger = items[4];
                    csvROW[0].menge = items[5];
                    csvROW[0].betrag = items[6];
                    csvROW[0].belegnummer = items[7];
                }                   
            }
        }

A good answer will explain what it's doing, not just provide bare code. In addition, all you changed is the call to Split (unlikely to be the problem by itself), but you never assign `items` (which you reference in the next lines). So this won't even compile. — Kevin Fichter, Mar 12 '18 at 14:32

Split does not work as expected with commas

3 Answers3