Click here to Skip to main content
15,880,427 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have a SQL table with column name and no data. i have another excel sheet with column name and data. i need to import the data from excel to sql table. Before that i need to compare sql column names and excel column names. if thats ok then only it needs to be imported.


Please Help me with clear coding. code in c#



What I have tried:

got succes in bulk upload from excel to sql database. but couple of modifications should be done for my target
Posted
Updated 14-May-16 3:24am
Comments
Karthik_Mahalingam 14-May-16 9:06am    
are you converting the excel to datatable ?

1 solution

Use this Generic Code for Validating the Excel columns with the Database table Columns

C#
private static bool IsColumnsEqual(DataTable excel, SqlConnection con, string tableName)
      {
          SqlCommand cmd = new SqlCommand("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tableName", con);
          cmd.Parameters.AddWithValue("@tableName", tableName);
          SqlDataAdapter da = new SqlDataAdapter(cmd);
          DataTable dtColumns = new DataTable();
          da.Fill(dtColumns);
          List<string> lstColumnNames = new List<string>();
          foreach (DataRow row in dtColumns.Rows)
              lstColumnNames.Add(row["COLUMN_NAME"].ToString());

          string[] excelColumnNames = excel.Columns.OfType<DataColumn>().Select(k => k.ColumnName).ToArray();
          bool equals = excelColumnNames.Intersect(lstColumnNames).Count() == excelColumnNames.Union(lstColumnNames).Count();
          return equals;

      }


Usage:
C#
bool isEqual = IsColumnsEqual(dtExcel, con, "Your_Table_Name");
          if (isEqual) {
              // do upload
          }
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900