Answered by:
Replace special character in datatable rows

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