Datasets with Excel containing Column Header names which change daily

Question

I am reading a Excel spreadsheet into a Dataset.

Normally I write the Select statement to retrieve just the columns I need. However, this case is different.

The spreadsheet has columns A - AL. The issue I need to resolve is that columns AA - AL are renamed daily by the data provider. I therefore, can't just select the columns I want as I don't know the column header names for those columns (AA - AL).

Option 1: Select *, then delete all the columns I don't need for which I know the name as their name is static. Then deal with the remaining columns by Index and change the Column Header names to my name of choice for Columns AA - AL (index to be determined after the unneeded columns are deleted).

Option 2: This is where I need help.

Best Regards,
Lance

Answer 1

@Lance James , you could try the following code to get columns data from excel file and rename the column names after delete columns.

 private void button1_Click(object sender, EventArgs e)  
        {  
            string path = "test1.xlsx";  
            DataTable dt = new DataTable();  
            using (OleDbConnection conn = new OleDbConnection())  
            {  
                 
                conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";  
                using (OleDbCommand comm = new OleDbCommand())  
                {  
                    comm.CommandText = "Select * from [" + "Sheet1" + "$]";   
                    comm.Connection = conn; using (OleDbDataAdapter da = new OleDbDataAdapter())  
                    {  
                        da.SelectCommand = comm;   
                        da.Fill(dt);   
                    }  
                }  
            }  
            List<string> columns = new List<string>();  
            columns.Add("Age");  
            RemoveColumns(columns, dt);  
            ChangeColumns(dt);  
  
        }  
  
        public void RemoveColumns(List<string>cols,DataTable table)  
        {  
            foreach (var item in cols)  
            {  
                table.Columns.Remove(item);  
            }  
        }  
  
        public void ChangeColumns(DataTable table)  
        {  
            for (int i = 0; i <table.Columns.Count ; i++)  
            {  
                table.Columns[i].ColumnName = "A" + (i + 1).ToString();  
            }  
        }  

Result in excel and show in the app:

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.