Scenario: Download Script
You are working as C# or dot Net Developer, you need to write a program that should create a Table if not exists per Excel File and load all sheet's data to it. As we are going to load all the sheets from single Excel file to a table, sheets should have same columns on each Excel file.
Here are our sample files.
How to create Table for each Excel file and load data from all sheets in C#
Customer_TechBrothersIT1.xlsx has two sheets in our case with columns Id, name and dob.
Create Table Dynamically for each Excel File in C# and Import All Sheets to Table
Customer_TechBrothersIT2.xlsx has two sheets with columns id and name.
How to load Multiple Sheets to SQL Server Table from a Excel File in C#
I have created Console Application by using below C# Code, it will create new table if does not exists for a excel file and load all the excel sheet's data to it.
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)"; //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) { string filename = ""; fileFullPath = FolderPath + "\\" + file.Name; filename = file.Name.Replace(".xlsx", ""); //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=1\""; OleDbConnection cnn = new OleDbConnection(ConStr); //Get Sheet Name 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 DataTable with Sheet Data OleDbCommand oconn = new OleDbCommand("select * from [" + sheetname + "]", cnn); OleDbDataAdapter adp = new OleDbDataAdapter(oconn); DataTable dt = new DataTable(); adp.Fill(dt); // Create Table if does not exists string tableDDL = ""; tableDDL += "IF Not EXISTS (SELECT * FROM sys.objects WHERE object_id = "; tableDDL += "OBJECT_ID(N'[dbo].[" + filename + "]') AND type in (N'U'))"; tableDDL += "Create table [" + filename + "]"; tableDDL += "("; for (int i = 0; i < dt.Columns.Count; i++) { if (i != dt.Columns.Count - 1) tableDDL += "[" + dt.Columns[i].ColumnName + "] " + "NVarchar(max)" + ","; else tableDDL += "[" + dt.Columns[i].ColumnName + "] " + "NVarchar(max)"; } tableDDL += ")"; SQLConnection.Open(); SqlCommand myCommand = new SqlCommand(tableDDL, SQLConnection); myCommand.ExecuteNonQuery(); //Load the data from DataTable to SQL Server Table. SqlBulkCopy blk = new SqlBulkCopy(SQLConnection); blk.DestinationTableName = "[" + filename + "]"; blk.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# code and it created tables from sample files as shown below and load the data from multiple sheet's to them.
Create SQL Table Dynamically from Excel file and load data in C#
Lets run query on created tables and take a look if data is loaded from Excel files correctly.
Table created form Customer_TechBrothersIT.xlsx Excel Sheet and Data Loaded from Sheets.
No comments:
Post a Comment