Scenario : Download Script
You are working as C# or dot net developer. You need to write a program that should be able to load single or multiple excel files with single or multiple excel sheets to single SQL Server table from a folder. All the sheets have the same columns.
The Excel file names and excel sheet names are not constant. They can change any time.
Here are couple of sample files those I created for test the below program.
How to import data from multiple excel files with single or multiple excel sheets to SQL Server Table in C# |
Let's create SQL Server Table first before we use it in our C# program.
CREATE TABLE [dbo].[tblCustomer]( [id] int NULL, [name] varchar(100) NULL, [dob] date NULL )
I used below C# code to create Console Application.
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 Source Folder path where excel files are present String FolderPath = @"C:\Source\"; //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"; //Create Connection to SQL Server Database SqlConnection SQLConnection = new SqlConnection(); SQLConnection.ConnectionString = "Data Source = " + SQLServerName + "; Initial Catalog =" + DatabaseName + "; " + "Integrated Security=true;"; 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 cnn.Open(); DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetname; sheetname = ""; //Loop through each sheet foreach (DataRow drSheet in dtSheet.Rows) { if (drSheet["TABLE_NAME"].ToString().Contains("$")) { sheetname = drSheet["TABLE_NAME"].ToString(); //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 above C# Console Application and it loaded data from multiple excel files with multiple sheets to SQL Server Table.
How to import multiple excel files with multiple Sheets to SQL Server Table in C# |
Hi, how to add archive imported excels to archive folder?
ReplyDeleteWhy:
ReplyDeleteSQLConnection.Close();
File.Move(fileFullPath, ArchiveFolder + "\\" + file.Name);
dont working?