locked
Replace special character in datatable rows RRS feed

  • Question

  • User-73514677 posted

    Hi,

    I have a datatable which contains 20 columns and 400 rows.

    Some of the rows contains data in the format of "700,000.00" and some in "This is a new value 20,000"

    I want to replace the special character "," with empty for numeric.

    example: 700,000.00  should be changed to 700000.00

    This is a new value 20,000  -  No change.

    How to achieve this in C#?

    Thanks

    Wednesday, March 2, 2016 6:15 AM

Answers

  • User-271186128 posted

    Hi venkatzeus,

    From your description, I suppose you want to check whether the column is numeric. If the data is a numeric, change the format. If that is the case, I suggest you could refer to the following code:

    Using double.TryParse() method to check whether the data is a numeric.

    Using Convert.ToDouble( cc.Field<string>("Description")).ToString("F2") method to change the format.

                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[3] { new DataColumn("ID"), new DataColumn("Name"), new DataColumn("Description") });
                dt.Rows.Add(1001, "AAA", "700,000.00");
                dt.Rows.Add(1002, "BBB", "This is a new value 20,000");
                dt.Rows.Add(1003, "CCC", "200,000.00");
                dt.Rows.Add(1004, "DDD", "$20,000");
                dt.Rows.Add(1005, "EEE", "This is a new value 10,000");
                double va;
                var query = from cc in dt.AsEnumerable()
                            where double.TryParse(cc.Field<string>("Description"), out va) == true
                            select new { 
                            ID = cc.Field<string>("ID").ToString(),
                            Name = cc.Field<string>("Name").ToString(),
                            Description = Convert.ToDouble( cc.Field<string>("Description")).ToString("F2")
                            };
    
                GridView1.DataSource = query.ToList();
                GridView1.DataBind();

    The output screenshot:

    Besides, if you want to display all of the records, you could refer to the following code:

            <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">
                <Columns>
                    <asp:BoundField DataField="ID" HeaderText="ID" />
                     <asp:BoundField DataField="Name" HeaderText="Name" />
                    <asp:TemplateField HeaderText="Description">
                        <ItemTemplate>
                            <asp:Label ID="lblDescrption" runat="server" Text='<%# MyFunction(Eval("Description").ToString()) %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>

    Code behind:

            protected void Page_Load(object sender, EventArgs e)
            {
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[3] { new DataColumn("ID"), new DataColumn("Name"), new DataColumn("Description") });
                dt.Rows.Add(1001, "AAA", "700,000.00");
                dt.Rows.Add(1002, "BBB", "This is a new value 20,000");
                dt.Rows.Add(1003, "CCC", "200,000.00");
                dt.Rows.Add(1004, "DDD", "$20,000");
                dt.Rows.Add(1005, "EEE", "This is a new value 10,000");
    
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
    
            public string MyFunction(string str)
            {
                string newvalue = string.Empty;
                double dd;
                if (double.TryParse(str, out dd))
                {
                    newvalue = Convert.ToDouble(str).ToString("F2");
                }
                else
                {
                    newvalue = str;
                }
                return newvalue;
            }

    The output:

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2016 8:34 AM
  • User1633621018 posted

    Hi venkat,

    That code is perfect, just add few more lines like:

    foreach (DataColumn col in dt.Columns)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        row[col]= MyFunction(row[col].ToString());                    
                    }
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2016 9:41 AM

All replies

  • User-271186128 posted

    Hi venkatzeus,

    From your description, I suppose you want to check whether the column is numeric. If the data is a numeric, change the format. If that is the case, I suggest you could refer to the following code:

    Using double.TryParse() method to check whether the data is a numeric.

    Using Convert.ToDouble( cc.Field<string>("Description")).ToString("F2") method to change the format.

                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[3] { new DataColumn("ID"), new DataColumn("Name"), new DataColumn("Description") });
                dt.Rows.Add(1001, "AAA", "700,000.00");
                dt.Rows.Add(1002, "BBB", "This is a new value 20,000");
                dt.Rows.Add(1003, "CCC", "200,000.00");
                dt.Rows.Add(1004, "DDD", "$20,000");
                dt.Rows.Add(1005, "EEE", "This is a new value 10,000");
                double va;
                var query = from cc in dt.AsEnumerable()
                            where double.TryParse(cc.Field<string>("Description"), out va) == true
                            select new { 
                            ID = cc.Field<string>("ID").ToString(),
                            Name = cc.Field<string>("Name").ToString(),
                            Description = Convert.ToDouble( cc.Field<string>("Description")).ToString("F2")
                            };
    
                GridView1.DataSource = query.ToList();
                GridView1.DataBind();

    The output screenshot:

    Besides, if you want to display all of the records, you could refer to the following code:

            <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">
                <Columns>
                    <asp:BoundField DataField="ID" HeaderText="ID" />
                     <asp:BoundField DataField="Name" HeaderText="Name" />
                    <asp:TemplateField HeaderText="Description">
                        <ItemTemplate>
                            <asp:Label ID="lblDescrption" runat="server" Text='<%# MyFunction(Eval("Description").ToString()) %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>

    Code behind:

            protected void Page_Load(object sender, EventArgs e)
            {
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[3] { new DataColumn("ID"), new DataColumn("Name"), new DataColumn("Description") });
                dt.Rows.Add(1001, "AAA", "700,000.00");
                dt.Rows.Add(1002, "BBB", "This is a new value 20,000");
                dt.Rows.Add(1003, "CCC", "200,000.00");
                dt.Rows.Add(1004, "DDD", "$20,000");
                dt.Rows.Add(1005, "EEE", "This is a new value 10,000");
    
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
    
            public string MyFunction(string str)
            {
                string newvalue = string.Empty;
                double dd;
                if (double.TryParse(str, out dd))
                {
                    newvalue = Convert.ToDouble(str).ToString("F2");
                }
                else
                {
                    newvalue = str;
                }
                return newvalue;
            }

    The output:

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2016 8:34 AM
  • User-73514677 posted

    Hi,

    Thanks for the reply.

    Can the below function be used on datatable? I do not have a grid, and I want to run on all rows.

    public string MyFunction(string str)
            {
                string newvalue = string.Empty;
                double dd;
                if (double.TryParse(str, out dd))
                {
                    newvalue = Convert.ToDouble(str).ToString("F2");
                }
                else
                {
                    newvalue = str;
                }
                return newvalue;
            }

    Thanks

    Wednesday, March 2, 2016 8:53 AM
  • User1633621018 posted

    Hi venkat,

    That code is perfect, just add few more lines like:

    foreach (DataColumn col in dt.Columns)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        row[col]= MyFunction(row[col].ToString());                    
                    }
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 2, 2016 9:41 AM
  • User-73514677 posted

    Hi,

    Thanks for the reply. I combined both functions and it is working.

    Thanks

    Wednesday, March 2, 2016 12:29 PM