locked
replace datatable column name RRS feed

  • Question

  • User-73514677 posted

    Hi,

    I have a datatable which contains 25 columns.

    Most of the column names have some characters which need to be replaced with another character.

    Example:

    string[] BadCharacters = { "_x007e_", "_x0021_", "_x0040_", "_x0023_", "_x0024_", "_x0025_", "_x005e_", "_x0026_", "_x002a_", "_x0028_", "_x0029_", "_x002b_", "_x002d_", "_x003d_", "_x007b_", "_x007d_", "_x003a_", "_x0022_", "_x007c_", "_x003b_", "_x0027_", "_x005c_", "_x003c_", "_x005e_", "_x003e_", "_x003f_", "_x002c_", "_x002e_", "_x002f_", "_x0060_", "_x0020_" };
                         
    string[] GoodCharacters = {"~"      ,  "!"     , "@"      ,   "#"    , "$"      , "%"      , "^"      , "&"      , "*"      , "("      ,  ")"     ,  "_"     , "+"      , "–"      , "="      , "{"      , "}"     , ":"       , "“"      , "|"      , ";"      , "‘"      , "\\"     , "<"      , ">"      , "?"      , ","      , "."      , "/"      , "`"      ," "};
                        
    

    I want to loop through the datatable column names and check if it contain any of the "BadCharacters" and then replace it with the corresponding Good characters.

    How to achieve this in datatable?

    Thanks

    Tuesday, March 1, 2016 9:18 AM

Answers

  • User1577371250 posted

    Hi,

    Instead of string[], you can sue the Dictionary as follows

    DataTable dt = new DataTable();
                dt.Columns.Add("Column_x007e_1");
                dt.Columns.Add("Column_x0021_2");
                dt.Columns.Add("Column_x0040_3");
                dt.Columns.Add("Column_x0023_4");
                dt.Columns.Add("Column_x0024_5");
                dt.Columns.Add("Column6");
                dt.Columns.Add("Column7");
                dt.Columns.Add("Column8");
    
                Dictionary<string, string> badCharacters = new Dictionary<string, string>();
    
                badCharacters.Add("_x007e_", "~");
                badCharacters.Add("_x0021_", "!");
                badCharacters.Add("_x0040_", "@");
                badCharacters.Add("_x0023_", "#");
                badCharacters.Add("_x0024_", "$");
    
                if (dt.Columns.Count > 0)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        var item = badCharacters.Where(c => col.ColumnName.Contains(c.Key)).FirstOrDefault();
                        if (item.Key != null)
                        {
                            col.ColumnName = col.ColumnName.Replace(item.Key, item.Value);
                        }
                    }
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 1, 2016 9:52 AM
  • User-986267747 posted

    Hi venkatzeus,

    I want to loop through the datatable column names and check if it contain any of the "BadCharacters" and then replace it with the corresponding Good characters.

    According to your description, you'd like to change the column base on some conditions, i create a sample and it works fine, please try with the code below.

        string[] BadCharacters = { "_x007e_", "_x0021_", "_x0040_", "_x0023_", "_x0024_", "_x0025_", "_x005e_", "_x0026_", "_x002a_", "_x0028_", "_x0029_", "_x002b_", "_x002d_", "_x003d_", "_x007b_", "_x007d_", "_x003a_", "_x0022_", "_x007c_", "_x003b_", "_x0027_", "_x005c_", "_x003c_", "_x005e_", "_x003e_", "_x003f_", "_x002c_", "_x002e_", "_x002f_", "_x0060_", "_x0020_" };
    
                string[] GoodCharacters = { "~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "_", "+", "–", "=", "{", "}", ":", "“", "|", ";", "‘", "\\", "<", ">", "?", ",", ".", "/", "`", " " };
    
                DataTable dt = new DataTable();
                dt.Columns.Add("Column_x007e_1");
                dt.Columns.Add("Column_x0021_2");
                dt.Columns.Add("Column_x0040_3");
                dt.Columns.Add("Column_x0023_4");
                dt.Columns.Add("Column_x0024_5");
    
                DataRow dr = dt.NewRow();
                dr["Column_x007e_1"] = "1";
                dr["Column_x0021_2"] = "2";
                dr["Column_x0040_3"] = "3";
                dr["Column_x0023_4"] = "4";
                dr["Column_x0024_5"] = "5";
                dt.Rows.Add(dr);
    
                foreach (DataColumn column in dt.Columns)
                {
                    for (int i = 0; i < BadCharacters.Length; i++)
                    {
                        if (column.ColumnName.Contains(BadCharacters[i]))
                        {
                            dt.Columns[column.ColumnName].ColumnName = column.ColumnName.Replace(BadCharacters[i],GoodCharacters[i]);
                        }
                    }
                }

    I hope it's helpful to you.

    Best Regards,

    Klein zhang

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

All replies

  • User1577371250 posted

    Hi,

    Instead of string[], you can sue the Dictionary as follows

    DataTable dt = new DataTable();
                dt.Columns.Add("Column_x007e_1");
                dt.Columns.Add("Column_x0021_2");
                dt.Columns.Add("Column_x0040_3");
                dt.Columns.Add("Column_x0023_4");
                dt.Columns.Add("Column_x0024_5");
                dt.Columns.Add("Column6");
                dt.Columns.Add("Column7");
                dt.Columns.Add("Column8");
    
                Dictionary<string, string> badCharacters = new Dictionary<string, string>();
    
                badCharacters.Add("_x007e_", "~");
                badCharacters.Add("_x0021_", "!");
                badCharacters.Add("_x0040_", "@");
                badCharacters.Add("_x0023_", "#");
                badCharacters.Add("_x0024_", "$");
    
                if (dt.Columns.Count > 0)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        var item = badCharacters.Where(c => col.ColumnName.Contains(c.Key)).FirstOrDefault();
                        if (item.Key != null)
                        {
                            col.ColumnName = col.ColumnName.Replace(item.Key, item.Value);
                        }
                    }
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 1, 2016 9:52 AM
  • User1633621018 posted

    You can do something like below:

    DataTable dt = new DataTable();
                dt.Columns.Add("Id");
                dt.Columns.Add("Name");
                dt.Columns.Add("Age");
    
                var str = new string[] { "Name" };           
    
                foreach (DataColumn col in dt.Columns)
                {
                    if (str.Any(a => a.Contains(col.ColumnName)))
                    {
                        col.ColumnName = "FName";
                    }
                }

    Tuesday, March 1, 2016 10:01 AM
  • User-1716253493 posted

    AFAIK, you don't need to check if any or not. Simply do replace, if not any will not replace.

    Tuesday, March 1, 2016 10:08 AM
  • User-986267747 posted

    Hi venkatzeus,

    I want to loop through the datatable column names and check if it contain any of the "BadCharacters" and then replace it with the corresponding Good characters.

    According to your description, you'd like to change the column base on some conditions, i create a sample and it works fine, please try with the code below.

        string[] BadCharacters = { "_x007e_", "_x0021_", "_x0040_", "_x0023_", "_x0024_", "_x0025_", "_x005e_", "_x0026_", "_x002a_", "_x0028_", "_x0029_", "_x002b_", "_x002d_", "_x003d_", "_x007b_", "_x007d_", "_x003a_", "_x0022_", "_x007c_", "_x003b_", "_x0027_", "_x005c_", "_x003c_", "_x005e_", "_x003e_", "_x003f_", "_x002c_", "_x002e_", "_x002f_", "_x0060_", "_x0020_" };
    
                string[] GoodCharacters = { "~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "_", "+", "–", "=", "{", "}", ":", "“", "|", ";", "‘", "\\", "<", ">", "?", ",", ".", "/", "`", " " };
    
                DataTable dt = new DataTable();
                dt.Columns.Add("Column_x007e_1");
                dt.Columns.Add("Column_x0021_2");
                dt.Columns.Add("Column_x0040_3");
                dt.Columns.Add("Column_x0023_4");
                dt.Columns.Add("Column_x0024_5");
    
                DataRow dr = dt.NewRow();
                dr["Column_x007e_1"] = "1";
                dr["Column_x0021_2"] = "2";
                dr["Column_x0040_3"] = "3";
                dr["Column_x0023_4"] = "4";
                dr["Column_x0024_5"] = "5";
                dt.Rows.Add(dr);
    
                foreach (DataColumn column in dt.Columns)
                {
                    for (int i = 0; i < BadCharacters.Length; i++)
                    {
                        if (column.ColumnName.Contains(BadCharacters[i]))
                        {
                            dt.Columns[column.ColumnName].ColumnName = column.ColumnName.Replace(BadCharacters[i],GoodCharacters[i]);
                        }
                    }
                }

    I hope it's helpful to you.

    Best Regards,

    Klein zhang

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