none
C# - Error while adding Data Header column in data table RRS feed

  • Question

  • I am trying to convert excel to data table. Refer attached Excel file ==> Sample ExcelData File

    I am getting error "Additional information: A column named 'F' already belongs to this DataTable."

    I have posted similar thread, but, it will not exactly match my requirement. that's why posted again here.

    Similar post : How to add top header column on top of the data table...?

    i have written the below code with adding of new header column like "A","B","C",.... (like excel column header). This will overwrite the existing column and through error.

    But, i don't want to overwrite the exiting column. On top the data table column should come like "A","B","C",...

    Error screenshot below.

    Note : This logic i have to apply in many places in my project and it is critical one.

            private void button1_Click_1(object sender, EventArgs e)
            {
    
                string sheetName = "sheet1";
                string tempdt = "DTtemp";
                string path = "C:\\Proj\\Test\\ExcelData.xlsx"; // Attached sample excel file
                DataTable dttable = new DataTable();
                dttable = XLtoDT(path, Shtname, tempdt);
    
            }
    
            public static DataTable XLtoDT(string XLpath, string sheetName, string dtSource)
            {
    
                DataTable dt2 = new DataTable();
                DataTable dtResult = null;
                int totalSheet = 0; //No of sheets on excel file  
                using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLpath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
                {
                    objConn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    DataSet ds = new DataSet();
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    //string sheetName = string.Empty;
                    if (dt != null)
                    {
                        var tempDataTable = (from dataRow in dt.AsEnumerable()
                                             where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                             select dataRow).CopyToDataTable();
                        dt = tempDataTable;
                        totalSheet = dt.Rows.Count;
                        //sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }
                    cmd.Connection = objConn;
                    cmd.CommandType = CommandType.Text;
                    sheetName = string.Concat(sheetName, "$");
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                    oleda = new OleDbDataAdapter(cmd);
                    oleda.Fill(ds, dtSource);
    
                    dtResult = ds.Tables[dtSource];
    
    
                    //for removing empty rows
                    dtResult = dtResult.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field =>
                                field is System.DBNull || string.Compare((field as string).Trim(),
                                string.Empty) == 0)).CopyToDataTable();
    
                    dt2 = dtResult;
                    StripEmptyRows(dt2);
    
    
                    for (int index = 0; index < dt2.Columns.Count; index++)
                    {
                        var colIndex = index + 1;
                        dt2.Columns[index].ColumnName = ExcelColumnNameFromNumber(colIndex).
    						    ToString().Trim(); // Error : Additional information: A column named 'F' already belongs to this DataTable.
                    }
    
                    objConn.Close();
                    return dt2; //Returning Data table  
                }
    
            }
    
            public static string ExcelColumnNameFromNumber(int pIndex)
            {
                var chars = Enumerable.Range(0,26).Select((i) =>
                    ((char)(Convert.ToInt32('A') + i)).ToString()).ToArray();
    
                pIndex -= 1;
    
                string columnName = null;
                var quotient = pIndex / 26;
    
                if (quotient > 0)
                {
                    columnName = ExcelColumnNameFromNumber(quotient) + chars[pIndex % 26];
                }
                else
                {
                    columnName = chars[pIndex % 26].ToString();
                }
    
                return columnName;
    
            }
    
    
    
            public static DataTable StripEmptyRows(DataTable dt)
            {
                List<int> rowIndexesToBeDeleted = new List<int>();
                int indexCount = 0;
                foreach (var row in dt.Rows)
                {
                    var r = (DataRow)row;
                    int emptyCount = 0;
                    int itemArrayCount = r.ItemArray.Length;
                    foreach (var i in r.ItemArray) if (string.IsNullOrWhiteSpace(i.ToString())) emptyCount++;
    
                    if (emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);
    
                    indexCount++;
                }
    
                int count = 0;
                foreach (var i in rowIndexesToBeDeleted)
                {
                    dt.Rows.RemoveAt(i - count);
                    count++;
                }
    
                return dt;
            }

    Final output would be like below in data table format.

    Waiting for favorable solution...pls. help.




    • Edited by Gani tpt Sunday, October 18, 2020 4:40 PM
    Sunday, October 18, 2020 2:18 AM

Answers

  • Hi Gani tpt,

    Thank you for posting here.

    If you want the first row of Excel to be data instead of headers, you can modify HDR=Yes in the connection string to HDR= No.

    But the exception you mentioned:

    Error: Additional information: A column named'F' already belongs to this DataTable.

    I can't reproduce it. Did I miss something, or is this problem resolved?

    The final result:

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Monday, October 19, 2020 6:50 AM
    Monday, October 19, 2020 5:29 AM
  • Awesome Timon...one small changes, will make a big impact.

    Yes. HDR=No ==> This will changing the effect.

    Now..the output is perfect....working fine.

    Thanks Timon Yang again and again.

    Final source code below (Convert excel to data table with Header column A,B,C,...)

     private void button1_Click_1(object sender, EventArgs e)
            {
    
                string Sheetname = "Sheet1";
                string tempdt = "DTtemp";
                string path = "C:\\Proj\\Test\\ExcelData.xlsx";
                DataTable dttable = new DataTable();
                dttable = getXLVendRef(path, Sheetname, tempdt);
    
            }
    
            public static DataTable getXLVendRef(string XLTempVSname, string sheetName, string dtSource)
            {
    
                DataTable dt2 = new DataTable();
                DataTable dtResult = null;
                int totalSheet = 0; //No of sheets on excel file  
                using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLTempVSname + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
                {
                    objConn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    DataSet ds = new DataSet();
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    //string sheetName = string.Empty;
                    if (dt != null)
                    {
                        var tempDataTable = (from dataRow in dt.AsEnumerable()
                                             where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                             select dataRow).CopyToDataTable();
                        dt = tempDataTable;
                        totalSheet = dt.Rows.Count;
                        //sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }
                    cmd.Connection = objConn;
                    cmd.CommandType = CommandType.Text;
                    sheetName = string.Concat(sheetName, "$");
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                    oleda = new OleDbDataAdapter(cmd);
                    oleda.Fill(ds, dtSource);
    
                    dtResult = ds.Tables[dtSource];
    
    
                    //for removing empty rows
                    dtResult = dtResult.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field =>
                                field is System.DBNull || string.Compare((field as string).Trim(),
                                string.Empty) == 0)).CopyToDataTable();
    
                    dt2 = dtResult;
                    StripEmptyRows(dt2);
    
    
                    for (int index = 0; index < dt2.Columns.Count; index++)
                    {
                        var colIndex  = index + 1;
                        dt2.Columns[index].ColumnName = ExcelColumnNameFromNumber(colIndex);
    
                    }
    
                    objConn.Close();
                    return dt2; //Returning Data table  
                }
    
            }
    
            public static string ExcelColumnNameFromNumber(int pIndex)
            {
                var chars = Enumerable.Range(0,26).Select((i) =>
                    ((char)(Convert.ToInt32('A') + i)).ToString()).ToArray();
    
                pIndex -= 1;
    
                string columnName = null;
                var quotient = pIndex / 26;
    
                if (quotient > 0)
                {
                    columnName = ExcelColumnNameFromNumber(quotient) + chars[pIndex % 26];
                }
                else
                {
                    columnName = chars[pIndex % 26].ToString();
                }
    
                return columnName;
    
            }
    
    
    
            public static DataTable StripEmptyRows(DataTable dt)
            {
                List<int> rowIndexesToBeDeleted = new List<int>();
                int indexCount = 0;
                foreach (var row in dt.Rows)
                {
                    var r = (DataRow)row;
                    int emptyCount = 0;
                    int itemArrayCount = r.ItemArray.Length;
                    foreach (var i in r.ItemArray) if (string.IsNullOrWhiteSpace(i.ToString())) emptyCount++;
    
                    if (emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);
    
                    indexCount++;
                }
    
                int count = 0;
                foreach (var i in rowIndexesToBeDeleted)
                {
                    dt.Rows.RemoveAt(i - count);
                    count++;
                }
    
                return dt;
            }


    • Edited by Gani tpt Monday, October 19, 2020 6:49 AM Thanks Timon....
    • Marked as answer by Gani tpt Monday, October 19, 2020 6:49 AM
    Monday, October 19, 2020 6:45 AM

All replies

  • any update...
    Sunday, October 18, 2020 1:52 PM
  • You must have a 1 based int e.g.

    for (int index = 0; index < dt.Columns.Count; index++)
    {
        var colIndex = index + 1;
        clone.Columns[index].ColumnName = colIndex.ExcelColumnNameFromNumber();
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, October 18, 2020 2:28 PM
    Moderator
  • It seems to work with shown sample file. But what Shtname is?


    • Edited by Viorel_MVP Sunday, October 18, 2020 2:42 PM
    Sunday, October 18, 2020 2:41 PM
  • Extremely sorry.

    But what Shtname is? ==> sheetName

     string sheetName = "sheet1";
                string tempdt = "DTtemp";
                string path = "C:\\Proj\\Test\\ExcelData.xlsx"; // Attached sample excel file
                DataTable dttable = new DataTable();
                dttable = XLtoDT(path, sheetname, tempdt);

    the sample below screenshot is a final data table value. But, i am getting error. run the complete code with sample output attached excel file.

    Sample ExcelData file : Sample Test File

    Sunday, October 18, 2020 4:00 PM
  • checked and found error.

    Sunday, October 18, 2020 4:05 PM
  • The problem was not reproduced with the shown code and file.

    Sunday, October 18, 2020 4:10 PM
  • What output you will be getting...?

    string sheetName = "Sheet1";
                string tempdt = "DTtemp";
                string path = "C:\\Proj\\Test\\ExcelData.xlsx"; // Attached sample excel file
                DataTable dttable = new DataTable();
                dttable = XLtoDT(path, sheetName, tempdt);

    • Edited by Gani tpt Sunday, October 18, 2020 4:17 PM string sheetName = "Sheet1";
    Sunday, October 18, 2020 4:13 PM
  • What output you will be getting...?

    The dttable variable that contains 6 rows. The column names are ‘A’..’F’.


    Sunday, October 18, 2020 4:19 PM
  • No. The The totaol rows suppose to be come as "7". (while converting excel to data table, the first row of excel data, will be treating as column. But, it should not like that.).

    All the Excel data should consider as data rows only. it means "7" rows is a data rows  and Header column contains like "A"...."F".

    Highlighted Yellow ==> Header column

    Highlighted Red ==> Data Rows

    below is the final output.

    the complete code is below.

            private void button1_Click_1(object sender, EventArgs e)
            {
    
                string Sheetname = "Sheet1";
                string tempdt = "DTtemp";
                string path = "C:\\Proj\\Test\\ExcelData.xlsx";
                DataTable dttable = new DataTable();
                dttable = getXLVendRef(path, Sheetname, tempdt);
    
            }
    
            public static DataTable getXLVendRef(string XLTempVSname, string sheetName, string dtSource)
            {
    
                DataTable dt2 = new DataTable();
                DataTable dtResult = null;
                int totalSheet = 0; //No of sheets on excel file  
                using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLTempVSname + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
                {
                    objConn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    DataSet ds = new DataSet();
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    //string sheetName = string.Empty;
                    if (dt != null)
                    {
                        var tempDataTable = (from dataRow in dt.AsEnumerable()
                                             where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                             select dataRow).CopyToDataTable();
                        dt = tempDataTable;
                        totalSheet = dt.Rows.Count;
                        //sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }
                    cmd.Connection = objConn;
                    cmd.CommandType = CommandType.Text;
                    sheetName = string.Concat(sheetName, "$");
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                    oleda = new OleDbDataAdapter(cmd);
                    oleda.Fill(ds, dtSource);
    
                    dtResult = ds.Tables[dtSource];
    
    
                    //for removing empty rows
                    dtResult = dtResult.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field =>
                                field is System.DBNull || string.Compare((field as string).Trim(),
                                string.Empty) == 0)).CopyToDataTable();
    
                    dt2 = dtResult;
                    StripEmptyRows(dt2);
    
    
                    for (int index = 0; index < dt2.Columns.Count; index++)
                    {
                        var colIndex  = index + 1;
                        dt2.Columns[index].ColumnName = colIndex.ExcelColumnNameFromNumber(); //Error Line : 	'int' does not contain a definition for 'ExcelColumnNameFromNumber' and no extension method 'ExcelColumnNameFromNumber' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?)	
    
                    }
    
                    objConn.Close();
                    return dt2; //Returning Data table  
                }
    
            }
    
            public static string ExcelColumnNameFromNumber(int pIndex)
            {
                var chars = Enumerable.Range(0,26).Select((i) =>
                    ((char)(Convert.ToInt32('A') + i)).ToString()).ToArray();
    
                pIndex -= 1;
    
                string columnName = null;
                var quotient = pIndex / 26;
    
                if (quotient > 0)
                {
                    columnName = ExcelColumnNameFromNumber(quotient) + chars[pIndex % 26];
                }
                else
                {
                    columnName = chars[pIndex % 26].ToString();
                }
    
                return columnName;
    
            }
    
    
    
            public static DataTable StripEmptyRows(DataTable dt)
            {
                List<int> rowIndexesToBeDeleted = new List<int>();
                int indexCount = 0;
                foreach (var row in dt.Rows)
                {
                    var r = (DataRow)row;
                    int emptyCount = 0;
                    int itemArrayCount = r.ItemArray.Length;
                    foreach (var i in r.ItemArray) if (string.IsNullOrWhiteSpace(i.ToString())) emptyCount++;
    
                    if (emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);
    
                    indexCount++;
                }
    
                int count = 0;
                foreach (var i in rowIndexesToBeDeleted)
                {
                    dt.Rows.RemoveAt(i - count);
                    count++;
                }
    
                return dt;
            }


    • Edited by Gani tpt Monday, October 19, 2020 2:43 AM Highlighted Explained.
    Sunday, October 18, 2020 4:30 PM
  • can anyone help to provide the working correct source code with above mentioned output(data table). ?

    otherwise it is waste of time to sending and receiving mails.

    i am working this logic past 3 to 4 days. but, i am unable to form the correct logic.

    If this will work, i have to apply many places in my many project(s).

    pls. guide us..

    Monday, October 19, 2020 12:05 AM
  • Any update regarding my final output...?
    Monday, October 19, 2020 2:44 AM
  • Hi Gani tpt,

    Thank you for posting here.

    If you want the first row of Excel to be data instead of headers, you can modify HDR=Yes in the connection string to HDR= No.

    But the exception you mentioned:

    Error: Additional information: A column named'F' already belongs to this DataTable.

    I can't reproduce it. Did I miss something, or is this problem resolved?

    The final result:

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Monday, October 19, 2020 6:50 AM
    Monday, October 19, 2020 5:29 AM
  • Awesome Timon...one small changes, will make a big impact.

    Yes. HDR=No ==> This will changing the effect.

    Now..the output is perfect....working fine.

    Thanks Timon Yang again and again.

    Final source code below (Convert excel to data table with Header column A,B,C,...)

     private void button1_Click_1(object sender, EventArgs e)
            {
    
                string Sheetname = "Sheet1";
                string tempdt = "DTtemp";
                string path = "C:\\Proj\\Test\\ExcelData.xlsx";
                DataTable dttable = new DataTable();
                dttable = getXLVendRef(path, Sheetname, tempdt);
    
            }
    
            public static DataTable getXLVendRef(string XLTempVSname, string sheetName, string dtSource)
            {
    
                DataTable dt2 = new DataTable();
                DataTable dtResult = null;
                int totalSheet = 0; //No of sheets on excel file  
                using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLTempVSname + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
                {
                    objConn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    DataSet ds = new DataSet();
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    //string sheetName = string.Empty;
                    if (dt != null)
                    {
                        var tempDataTable = (from dataRow in dt.AsEnumerable()
                                             where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                             select dataRow).CopyToDataTable();
                        dt = tempDataTable;
                        totalSheet = dt.Rows.Count;
                        //sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }
                    cmd.Connection = objConn;
                    cmd.CommandType = CommandType.Text;
                    sheetName = string.Concat(sheetName, "$");
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                    oleda = new OleDbDataAdapter(cmd);
                    oleda.Fill(ds, dtSource);
    
                    dtResult = ds.Tables[dtSource];
    
    
                    //for removing empty rows
                    dtResult = dtResult.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field =>
                                field is System.DBNull || string.Compare((field as string).Trim(),
                                string.Empty) == 0)).CopyToDataTable();
    
                    dt2 = dtResult;
                    StripEmptyRows(dt2);
    
    
                    for (int index = 0; index < dt2.Columns.Count; index++)
                    {
                        var colIndex  = index + 1;
                        dt2.Columns[index].ColumnName = ExcelColumnNameFromNumber(colIndex);
    
                    }
    
                    objConn.Close();
                    return dt2; //Returning Data table  
                }
    
            }
    
            public static string ExcelColumnNameFromNumber(int pIndex)
            {
                var chars = Enumerable.Range(0,26).Select((i) =>
                    ((char)(Convert.ToInt32('A') + i)).ToString()).ToArray();
    
                pIndex -= 1;
    
                string columnName = null;
                var quotient = pIndex / 26;
    
                if (quotient > 0)
                {
                    columnName = ExcelColumnNameFromNumber(quotient) + chars[pIndex % 26];
                }
                else
                {
                    columnName = chars[pIndex % 26].ToString();
                }
    
                return columnName;
    
            }
    
    
    
            public static DataTable StripEmptyRows(DataTable dt)
            {
                List<int> rowIndexesToBeDeleted = new List<int>();
                int indexCount = 0;
                foreach (var row in dt.Rows)
                {
                    var r = (DataRow)row;
                    int emptyCount = 0;
                    int itemArrayCount = r.ItemArray.Length;
                    foreach (var i in r.ItemArray) if (string.IsNullOrWhiteSpace(i.ToString())) emptyCount++;
    
                    if (emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);
    
                    indexCount++;
                }
    
                int count = 0;
                foreach (var i in rowIndexesToBeDeleted)
                {
                    dt.Rows.RemoveAt(i - count);
                    count++;
                }
    
                return dt;
            }


    • Edited by Gani tpt Monday, October 19, 2020 6:49 AM Thanks Timon....
    • Marked as answer by Gani tpt Monday, October 19, 2020 6:49 AM
    Monday, October 19, 2020 6:45 AM
  • Hi Gani,

    You're welcome.

    I am glad this helps. 

    Please click on the "Mark as answer" option of the reply that solved your question, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 19, 2020 6:49 AM