Scenario: Download Script
You are working as C# or dot net developer in insurance company. You need to write a C# code that should read the data from Excel Sheet and load to SQL Server Table.
How to Import Excel Sheet Data to SQL Server Table in C# |
Let's create the table in database first after studying our data for Customer Sheet.
CREATE TABLE [dbo].[tblCustomer]( [id] int NULL, [name] varchar(100) NULL, [dob] date NULL )
I created Console Application by using below C# code that will be able to import data from Customer Excel sheet to our dbo.tblCustomer table.
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 Database Name string DatabaseName = "TechbrothersIT"; //Provide the SQL Server Name string SQLServerName = "(local)"; //Provide the table name in which you want to load excel sheet's data String TableName = @"tblCustomer"; //Provide the schema of table String SchemaName = @"dbo"; //Provide Excel file path string fileFullPath = @"C:\Source\Customer_1.xlsx"; //Provide Sheet Name you like to read string SheetName = "Customer"; //Create Connection to SQL Server Database SqlConnection SQLConnection = new SqlConnection(); SQLConnection.ConnectionString = "Data Source = " + SQLServerName + "; Initial Catalog =" + DatabaseName + "; " + "Integrated Security=true;"; //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 data from Excel Sheet to DataTable OleDbConnection Conn = new OleDbConnection(ConStr); Conn.Open(); OleDbCommand oconn = new OleDbCommand("select * from [" + SheetName + "$]", Conn); OleDbDataAdapter adp = new OleDbDataAdapter(oconn); DataTable dt = new DataTable(); adp.Fill(dt); Conn.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 dt.Columns) BC.ColumnMappings.Add(column.ToString(), column.ToString()); BC.WriteToServer(dt); } 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 Console application and it was able to read the data from Customer sheet from Excel file and imported into SQL server Table.
How to import data from Excel Sheet to SQL Server table in C# |
This comment has been removed by the author.
ReplyDeleteHello, it appears there is a syntax error, where, the closing quote in the code line below is actually escaped and not ending the string literal correctly, causing the script to be thrown off.
ReplyDeletestring LogFolder = @"C:\Log\";
I want to import data from all the folders and subfolders in the specified path what changes i can do in this .. please help
ReplyDeletewhile traversing the sub-folder, it is able to pick up the file name from the sub folder but while opening the file it is taking the parent folder path
Deleteexample: file path - d:\test
sub directory : d:\test\test2
inside subdirectory my file is present but when iterating it is reading the path of sub directory file as : d:\test\file_name
string SheetName = "Customer";
ReplyDeletewhat is customer ?