Use decimal in a column in a datatable

Question

I can make a datatable fine as long as I use strings and as long as my int is an even number but when I want to use a number with a period in it I get errors.

        static DataTable GetTable()
    {            
        DataTable table = new DataTable();

        table.Columns.Add("Date", typeof(string));
        table.Columns.Add("Item Name", typeof(string));
        table.Columns.Add("Buyer", typeof(string));
        table.Columns.Add("Quantity", typeof(int));
        table.Columns.Add("Price", typeof(string)); //I want this to be a decimal or whatever


        using (TextFieldParser parser = new TextFieldParser("c:\\folder\\sold.csv"))
        {
            parser.CommentTokens = new string[] { "#" };
            parser.SetDelimiters(new string[] { "," });
            parser.HasFieldsEnclosedInQuotes = true;

            parser.ReadLine();

            while (!parser.EndOfData)
            {
                string[] fields = parser.ReadFields();

                table.Rows.Add(fields[0], fields[1], fields[2], fields[3], fields[4]);
             }
        }
        return table;
    }

The "Quantity" don't mind being an int since it's a "1" or a "2" but the "Price" gives me errors when it's "21.56" and so on. What am I missing?

I'm also having problems getting the "Date" to be a "DateTime" when it's "8/31/16" but I'm assuming that's because it actually needs to be three ints instead of a string...

Edit It seemed it was a culture problem, I'm from Sweden and we use commas instead of decimals. Here's the code in case anyone is curious, I'm sure there's a more elegant way of doing this though.

        static DataTable GetTable()
    {            
        DataTable table = new DataTable();

        table.Columns.Add("Date", typeof(DateTime));
        table.Columns.Add("Item Name", typeof(string));
        table.Columns.Add("Buyer", typeof(string));
        table.Columns.Add("Quantity", typeof(int));
        table.Columns.Add("Price", typeof(decimal));

        using (TextFieldParser parser = new TextFieldParser("c:\\folder\\sold.csv"))
        {
            parser.CommentTokens = new string[] { "#" };
            parser.SetDelimiters(new string[] { "," });
            parser.HasFieldsEnclosedInQuotes = true;

            parser.ReadLine();

            while (!parser.EndOfData)
            {
                string[] fields = parser.ReadFields();

                table.Rows.Add(DateTime.ParseExact(fields[0], "MM/dd/yy", null), fields[1], fields[2], int.Parse(fields[3]), Convert.ToDecimal(fields[4], new CultureInfo("en-US")));
            }
        }
        return table;
    }

Set your column with the right type, then parse the CXV value with to the right type. Beware of [culture](http://stackoverflow.com/a/5238137/4625305), — AXMIM, Sep 12 '16 at 15:46

Shyju · Accepted Answer · 2016-09-12T15:50:47.500

Do not use string for every type of data.(I made the same mistake when i started writing code in C# few years back) . Decimal, Double and DateTime types are created to handle this kind of data.

You should use the decimal type for price

table.Columns.Add("Price", typeof(decimal));

For date, you should use DateTime

table.Columns.Add("Date", typeof(DateTime));

Columns.Add takes an an object array. So as long as your string values can be safely converted to these types, your code should work.

For example, The below code will work fine.

DataTable table = new DataTable();

table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("Item Name", typeof(string));
table.Columns.Add("Buyer", typeof(string));
table.Columns.Add("Quantity", typeof(int));
table.Columns.Add("Price", typeof(decimal));

string[] fields = {"12/12/2013","test","Hello","3","345.45"};
table.Rows.Add(fields[0], fields[1], fields[2], fields[3], fields[4]);

Even though the items in the fields array is string type, they can be safely converted to the DateTime and decimal (For Date and Price column).

score 0 · Answer 2 · answered Sep 12 '16 at 16:03

You are reading your fields as strings - you need to convert the final parameter to Decimal. It would be a good idea for all fields that are not strings. For the DateTime field you can add a formatting parameter, to deal with month/day/year order.

table.Rows.Add(DateTime.Parse(fields[0]), fields[1], fields[2], 
     int.Parse(fields[3]), Decimal.Parse(fields[4]));

NOTE - I am assuming your parser is checking the format of the fields - otherwise you will need to wrap the code in a try - catch for badly formatted data.

Use decimal in a column in a datatable

2 Answers2