string format error when reading CSVs to object with LINQ

Question

I am reading data from CVS file into C# list object . I can only read string values. When I try to read a numerical value (double in this case) I get input string was not in correct format exception. Anyone can show me how to overcome this? Here is the code:

class :

 public class Loan
    {
        string applicationDT;
        string employeeID;
        string employeeName;
        double amount;
        string lonType;

        public void printLoan()
        {
            Console.WriteLine($" employeeName {employeeName} employee Id {employeeID}" +
                $"applicationDate {applicationDT} \namount {amount} loanType {lonType}");
        }

        public static Loan fromCVS(string csvLine)
        {
            string []values = csvLine.Split(',');
            Loan loanRecord = new Loan();
            loanRecord.employeeName = (values[3]);
            loanRecord.employeeID = (values[4]);
            loanRecord.applicationDT = (values[5]);
            loanRecord.amount = Convert.ToDouble(values[6]);
            loanRecord.lonType = (values[7]);
            return loanRecord;
        }
    }

Main:

static void Main()
{
    List<Loan> loans = File.ReadAllLines(@"C:\Users\0300Test.csv")
                                   .Skip(1)
                                   .Select(v => Loan.fromCVS(v))
                                   .ToList();
    foreach(Loan aLoan in loans)
    {
        aLoan.printLoan();
    }
}

CSV file :

"APPROVAL_OFFICER_NAME","APPROVAL_OFFICER_ID","RELATIONSHIP_MANAGER_NAME","EMPLOYEE_NAME","EMPLOYEE_ID","APPLICATION_DATE_TIME","AMOUNT","TYPE_OF_LOAN"
"SAMPLNAME","988803","SAMPLNAME","SAMPLNAME","776667","1/22/2019 11:05:43 AM","321146.00","Top Up With Settlement"
"SAMPLNAME","988803","SAMPLNAME","SAMPLNAME","776667","1/22/2019 9:34:13 AM","90230.00","Top Up With Settlement"
"SAMPLNAME","988803","SAMPLNAME","SAMPLNAME","776667","1/22/2019 12:00:22 AM","5230.00","Top Up without Settlement"

Answer 1

Apparently your problem is when you want to assign a value to amount

string []values = csvLine.Split(',');
double amount = Convert.ToDouble(values[6]);

Of course you debugged the code. What did you see as value for values[6]?

From your csv line examples it seems something like: "\"90230.00\""

So it is a string starting and ending with a string quote. It is not possible to convert this value to a double.

The solution is simple: remove the string quotes at the beginning and the end of this value.

  amount = Double.Parse(value[6].SubString(1, value[6].Length-2));

By the way, do you want employeeName (and all others) to be the string SAMPLENAME or should it be surrounded by quotes: "SAMPLENAME" (So internally "\"SAMPLENAME\"")?

If you want to remove these start-and-end string quotes from all your values, consider creating an extension function for strings. See extension methods demystified

public static IEnumerable<string> SplitAndRemoveQuotes(this string string)
{
    // TODO: handle null string
    var splitValues = string.Split(',');
    foreach(string splitValue in splitValues)
    {
         // if you are certain every split value starts and ends with string quote
         // TODO: throw exception if not start/end with string quote?
         yield return splitValue.SubString(1, splitValue.Length-2);
    }
}

So if your string has format "\"\"\"", (in readable format: """) the return value will be a string with only one quote (")

Usage:

public static Loan fromCVS(string csvLine)
{
    // TODO: exception if csvLine null or empty

    var splitWithoutQuotes = csvLine.SplitAndRemoveQuotes()
        .Skip(2)        // we don't need the first two values
        .Take(5)        // we only need the next five values
        .ToList();
    // TODO: exception if result not 5 items

    return new Loan
    {
        employeNames = splitWithoutQuotes[0],
        ...
        amount = Double.Parse(values[4]);
        ...
    };
}

Answer 2

If you look at your data

"SAMPLNAME","988803","SAMPLNAME","SAMPLNAME","776667","1/22/2019 11:05:43 AM","321146.00","Top Up With Settlement"

Each one of your parameters is surrounded with quotes.

The value of values[6] is actually "321146.00" with the "'s surrounding it so it cant convert it.

 var x = values[6].Replace("\"", string.Empty);