how to fetch the only column data from excel which is there in database table column in c#

Question

I have an excel file of 150 columns with specific column headings. In my database,have a table 'ExcelImport ' with column ExcelHead (contains data of column headings which have same name as excel column heading).

create table ExcelImport( id int primary key,ExcelHead varchar(max))

insert into ExcelImport values(1,'Role'),(2,'Manager')

this table will get data based on another rule. What my requirement is that i need to fetch the only column data from excel which is there in ExcelImport table and insert into another temp table. From the example i need to fetch only 'Role' and 'Manager' excel column and need to insert into temp table.

Note: Every time ExcelImport table data will truncated and new values for ExcelHead will be inserted.

Can any one suggest the coding for doing the same??

Answer 1

            DataSet ExcelDataset = DBcom.GetDataset();
            //add logic for getting excel imported data in dataset format.
            string[] columnsNames = new string[100]; int loop = 0;

            //Comparing the Imported Excel Column With Database Table Column
            foreach (DataColumn column in ExcelDataset.Tables[0].Columns)
            {
                string clnameExcel = column.ColumnName;                
                int Exist = 0;
                foreach (DataTable table in DataBaseDataset.Tables)
                {
                    foreach (DataRow dr in table.Rows)
                    {
                        string ColnamesDB = dr["DBColumn"].ToString();//DBColumn is the name of database table column name


                        if (CompclnameExcel == CompColnamesDB)
                        {
                            Exist = 1;
                            break;
                        }

                    }
                }
                if (Exist == 0)
                {                    
                    columnsNames[loop] = clnameExcel;
                    loop++;
                }
            }
            //Deleting Imported Excel Columns which is not there in Database
            foreach (string cNames in columnsNames)
            {
                if (!string.IsNullOrEmpty(cNames))
                {
                    ExcelDataset.Tables[0].Columns.Remove(cNames);
                }
            }

Answer 2

First you will have to read your column header from your excel file. Try looking here to understand how to read column header from the excel file. Column header are usually first row of the excel file so it will be fine if you just read the first row of the excel file for column header. I will usually used a Dictionary to keep track of column header and it column index. Then you will need to use a sql to query ExcelImport table to get all the column name that you wanted to used as filter. And I will cross reference the two data (ExcelImport and Dictionary) to get all the row index of the excel file that needed to get.Then I will iterate all the row to get all the data of the excel file.Finally I will delete all the data from the ExcelImport and insert new data into ExcelImport table. You can look at pseudo code below.

    public Dictionary<string,int> GetDictionary(string fullExcelPath)
    {
           //Dictionary<Column Header,Column Index>

           Dictionary<string,int> columns=new  Dictionary<string,int>();

             ExcelWorkbook excelWorkbook = ExcelWorkbook.ReadXLSX(fullExcelPath);

             ExcelWorksheet excelWorkSheet = excelWorkbook.Worksheets[0];

             DataTable dataTable = excelWorkSheet.WriteToDataTable();

             DataTable columns=dataTable.Rows[0];

             //iterate to get the column header

             for(int i=0;;i++)
             {
                string header=(string )row[i];

                if(!string.IsNullOrWhiteSpace(header))
                {
                       columns.Add(header,i);
                }
                else
                {
                   break;
                }
             }
           return columns;

    }

  public void DoWork()
  {
      Dictionary<string,int> columnsFromExcel=GetDictionary(excelPath);

      List<string> columnToFilter=GetFromDatabase();

      int[] columnIndex=CrossReferenceData(columnsFromExcel,columnToFilter);

      //column index=index we wanted to get data from
      //GetDataFromExcel =basically same as GetDictionary but it read from //second row instead of first row and used columnIndex to get data from //dataTable (look GetDictionary method).
      List<string> dataFromExcel=GetDataFromExcel(columnIndex);

      DeleteExcelImportTable();

      // dynamically created your insert sql

      string insertSQL=GetInsertSQL(dataFromExcel);

      InsertExcelImportTable(insertSQL);

  }