You are working as C# developer or Dot Net Developer. You need to write a program that should be able to skip some rows from an Excel Sheet and load the remaining records to SQL Server Table. The program should be able to handle to load multiple Excel files. Show Here is my sample excel sheet with some data. How to Skip Rows in Excel Sheet and Export actual records to SQL Server Table in C#As you can see that I have first 5 rows as company name. In your case you might have logo or any information. In my case, I need to start reading from A7 and load the data to dbo.Customer table. We also validate the columns against our Table columns in database, let's say if there is a column in excel and it does not exists in database table, the program is going to ignore that column and insert the data for the columns which are present in excel and database table. Let's create customer table first and then write C# code to skip the rows and load the data to table. CREATE TABLE [dbo].[Customer]( [Id] [int] NULL, [Name] [varchar](100) NULL, [Dob] [date] NULL ) I created Console Application by using below C# code.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; //added below name spaces using System.IO; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; namespace TechBrothersIT.com_CSharp_Tutorial { class Program { static void Main(string[] args) { //the datetime and Log folder will be used for error log file in case error occured string datetime = DateTime.Now.ToString("yyyyMMddHHmmss"); string LogFolder = @"C:\Log\"; try { //Provide the path where Excel files exists string FolderPath = @"C:\Source\"; //Provide the table name in which you want to load the data string TableName = "Customer"; //Provide the schema of table string SchemaName = "dbo"; //Provide the starting column for read actul records string StartingColumn = "A"; //Provide the end column till which you want to read string EndingColumn = "C"; //Provide the row number from which you like to start reading string StartReadingFromRow = "7"; //Provide the Database Name in which table or view exists string DatabaseName = "TechbrothersIT"; //Provide the SQL Server Name string SQLServerName = "(local)"; var directory = new DirectoryInfo(FolderPath); FileInfo[] files = directory.GetFiles(); //Declare and initilize variables string fileFullPath = ""; //Get one Book(Excel file at a time) foreach (FileInfo file in files) { fileFullPath = FolderPath + "\\" + file.Name; //Create Excel Connection string ConStr; string HDR; HDR = "YES"; ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\""; OleDbConnection cnn = new OleDbConnection(ConStr); //Get Sheet Name, it can handle multiple sheets cnn.Open(); DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetname; sheetname = ""; foreach (DataRow drSheet in dtSheet.Rows) { if (drSheet["TABLE_NAME"].ToString().Contains("$")) { sheetname = drSheet["TABLE_NAME"].ToString(); //Load the DataTable with Sheet Data so we can get the column header OleDbCommand oconn = new OleDbCommand("select top 1 * from [" + sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn); OleDbDataAdapter adp = new OleDbDataAdapter(oconn); DataTable dt = new DataTable(); adp.Fill(dt); cnn.Close(); //Prepare Header columns list so we can run against Database to get matching columns for a table. //If columns does not exists in table, it will ignore and load only matching columns data string ExcelHeaderColumn = ""; string SQLQueryToGetMatchingColumn = ""; for (int i = 0; i < dt.Columns.Count; i++) { if (i != dt.Columns.Count - 1) ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'" + ","; else ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'"; } SQLQueryToGetMatchingColumn = "select STUFF((Select ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" + TableName + "' and Table_SChema='" + SchemaName + "'" + "and Column_Name in (" + @ExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList"; //Create Connection to SQL Server Database from which you like to export tables to Excel SqlConnection SQLConnection = new SqlConnection(); SQLConnection.ConnectionString = "Data Source = " + SQLServerName + "; Initial Catalog =" + DatabaseName + "; " + "Integrated Security=true;"; //Get Matching Column List from SQL Server string SQLColumnList = ""; SqlCommand cmd = SQLConnection.CreateCommand(); cmd.CommandText = SQLQueryToGetMatchingColumn; SQLConnection.Open(); SQLColumnList = (string)cmd.ExecuteScalar(); SQLConnection.Close(); //Use Actual Matching Columns to get data from Excel Sheet OleDbConnection cnn1 = new OleDbConnection(ConStr); cnn1.Open(); OleDbCommand oconn1 = new OleDbCommand("select " + SQLColumnList + " from [" + sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn1); OleDbDataAdapter adp1 = new OleDbDataAdapter(oconn1); DataTable dt1 = new DataTable(); adp1.Fill(dt1); cnn1.Close(); SQLConnection.Open(); //Load Data from DataTable to SQL Server Table. using (SqlBulkCopy BC = new SqlBulkCopy(SQLConnection)) { BC.DestinationTableName = SchemaName + "." + TableName; foreach (var column in dt1.Columns) BC.ColumnMappings.Add(column.ToString(), column.ToString()); BC.WriteToServer(dt1); } SQLConnection.Close(); } } } } catch (Exception exception) { // Create Log File for Errors using (StreamWriter sw = File.CreateText(LogFolder + "\\" + "ErrorLog_" + datetime + ".log")) { sw.WriteLine(exception.ToString()); } } } } } I executed above program and it was able to skip the rows in excel sheet and loaded the actual data according as by variable values I have provided at start of program. How to import data from Excel to SQL table in C#?Bulk Upload Excel to SQL Server in C#.Net. Create your form for the upload.. Create your DB Structure.. Create your connection string to communicate with the DB. ... . Create the Form structure to handle the excel reading as well sending the data to the DAO class for the bulk Upload.. How to read data from Excel sheet and insert into database table in C#?Create a function to read and insert an Excel File into the database as:. private void InsertExcelRecords(string FilePath). ExcelConn(FilePath);. Query = string. ... . OleDbCommand Ecom = new OleDbCommand(Query, Econ);. Econ. ... . DataSet ds=new DataSet();. OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);. How copy data from Excel to DataTable in C?Convert Excel to DataTable in C#. Create an object of the Workbook class and load the source XLSX file.. Access the Worksheet that you desire to export to Datatable.. Invoke the ExportDataTable method to export the Excel sheet to the Datatable.. How do I insert data into a SQL table from an Excel spreadsheet?Import and Export Wizard. In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine.. Expand Databases.. Right-click a database.. Point to Tasks.. Choose to Import Data or Export Data:. |