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"

Please edit your question and add an example of the data (From the csv file) that is failing — Linda Lawton - DaImTo
– Linda Lawton - DaImTo, Commented Jan 23, 2019 at 10:09
Assuming your csv does not have any " this is working for me without any exceptions. Have you debugged which value is throwing the exception? — Mighty Badaboom
– Mighty Badaboom, Commented Jan 23, 2019 at 10:27

Harald Coppoolse · Accepted Answer · 2019-01-23 11:22:09Z

2

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]);
        ...
    };
}

edited Jan 23, 2019 at 11:22

answered Jan 23, 2019 at 10:52

Harald Coppoolse

30.6k7 gold badges78 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Coder Over a year ago

Thanks for clarification. As for employeeName , I changed the data before posting

Coder Over a year ago

Can you show me how to read date and time given the format in the file?

Harald Coppoolse Over a year ago

To translate strings to DateTime: use DateTime.Parse, or DateTime.ParseExact. It is usually better to Parse than to Convert

Linda Lawton - DaImTo · Accepted Answer · 2019-01-23 10:29:34Z

0

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);

answered Jan 23, 2019 at 10:29

Linda Lawton - DaImTo

118k40 gold badges229 silver badges511 bronze badges

2 Comments

Coder Over a year ago

What if I want to avoid quotes in future files - single or double- is there a way to do it ?

Linda Lawton - DaImTo Over a year ago

Probably not. I wouldn't suggest striping all quotes from the full file as its they could be used within the string someplace. "Top Up 'With' Settlement" could be valid. You should only replace them on the date and the number fields. Before doing a conversion as these fields you would not want to see any quotes in.

Collectives™ on Stack Overflow

string format error when reading CSVs to object with LINQ

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related