Reading a comma delimited CSV that contains currency

Question

In the following that reads from a comma delimited CSV file.

The currency field which is the third field in each line [2] is the problem. I need to split the fields at the comma but the currency field may sometimes be large and also have them.

How can you split a comma separated csv file that contains currency.??

The csv is always consistent 5 fields on each line.

The first line in the example below works but the second line would cause the issue.

3,09:29 pm,€20.00,Test,Test

1,02:55 am,€10,000.00,Test,Test

StreamReader fileIn = new StreamReader(path);
        //Read the file
        while (!fileIn.EndOfStream)
        {
           String line = fileIn.ReadLine();
           String[] pieces = line.Split(',');

           csvComplete cEve = new csvComplete (pieces[0], pieces[1], pieces[2], pieces[3], pieces[4]);// assign to class cEve
           entries.Add(cEve);

        }

Any links or suggestions would be appreciated.

Answer 1

Here is a hack:

//Read the file
while (!fileIn.EndOfStream)
{
   String line = fileIn.ReadLine();
   String[] pieces = line.Split(',');
   if(pieces.length > 5){
       String[] newPieces = new String[5];
       newPieces[0] = pieces[0];
       newPieces[1] = pieces[1];
       String currency = "";
       for(int i = 2; i < pieces.length - 2; i++){
           if(i == pieces.length -3)
               currency += pieces[i];
           else{
               currency += pieces[i] + ",";
           }
       }
       newPieces[2] = currency;
       newPieces[3] = pieces[pieces.length-2];
       newPieces[4] = pieces[pieces.length-1];
       csvComplete cEve = new csvComplete (newPieces[0], newPieces[1], newPieces[2], newPieces[3], newPieces[4]);// assign to class cEve
       entries.Add(cEve);
   }
   else{
       csvComplete cEve = new csvComplete (pieces[0], pieces[1], pieces[2], pieces[3], pieces[4]);// assign to class cEve
       entries.Add(cEve);
   }

 }

This should account for bigger currencies (trillions of euros will have more commas).

Hope this helps!