Importing Data from Excel to Database Using C#

Importing Data from Excel to Database Using C#

Introduction

Whether you're looking to analyze data, perform complex computations, or simply maintain an organized database, importing data from an Excel sheet into a database is a frequently needed task. In this guide, we will explore how to achieve this using C#. We will be utilizing the OleDbConnection, SqlCommand, and SqlBulkCopy classes to read and import data from an Excel spreadsheet into a SQL Server database. The article includes a detailed example code snippet and table creation steps.

Preparing the Excel Data

To begin, prepare an Excel file that contains the data you wish to import. Make sure to have the appropriate columns in your Excel sheet that match the columns in your database table definition. This process can be done using any spreadsheet software like Microsoft Excel, Google Sheets, or even an automated script if you plan to import a large dataset frequently.

Setting Up the Database Table

Before importing data, you'll need to ensure that your SQL Server database has a corresponding table with the necessary columns. The following SQL script shows how to create a table with the required structure:

SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Table1]( [student] [varchar](50) NULL,[rollno] [int] NULL,[course] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO

In the above script, the table name is Table1, and it contains three columns: student, rollno, and course.

The Importing Process Using C#

Here’s a step-by-step guide to writing the C# code to import data from an Excel file into your SQL Server database:

Step 1: Setup Your Environment

Ensure that your C# project has the necessary references to the and namespaces. Additionally, ensure that the SQL Server Native Client is installed and properly configured in your environment.

Step 2: Create the C# Code

The following is the C# method to import data from the Excel file to the SQL Server database:

public void ImportDataFromExcel(string excelFilePath){    // Declare variables - Edit these based on your particular situation    string ssqlTable  Table1; // Make sure your sheet name is correct (sheet1 in this case)    string myExcelDataQuery  SELECT * FROM [Sheet1$];    try    {        // Create our connection strings        string excelConnectionString  Provider;Data Source    excelFilePath   ;Extended Properties Excel 12.0;HDRYES;IMEX1;;        string SqlConnectionString  Serverlocalhost;DatabaseSyncDB;Integrated SecurityTrue;;        // Create connection to the database        using (SqlConnection Sqlconnection  new SqlConnection(SqlConnectionString))        {            SqlCommand SqlCommand  new SqlCommand(TRUNCATE TABLE    ssqlTable, Sqlconnection);            SqlCommand.ExecuteNonQuery();        }        // Create a connection to Excel        using (OleDbConnection Oledbconn  new OleDbConnection(excelConnectionString))        {            // Execute a select command to get the data            ();            using (OleDbCommand OleDbcmd  new OleDbCommand(myExcelDataQuery, Oledbconn))            {                using (OleDbDataReader OleDbReader2  OleDbcmd.ExecuteReader())                {                    // Set up the bulk copy                    using (SqlBulkCopy bulkCopy  new SqlBulkCopy(, ))                    {                          ssqlTable;                        ();                        bulkCopy.WriteToServer(OleDbReader2);                    }                }            }            Label1.Text  Successfully Imported Data;        }    }    catch (Exception ex)    {        // Handle the exception        Console.WriteLine();    }}

In this code, the excelFilePath is the path to your Excel file. The excelConnectionString is the connection string needed to connect to your Excel file. Similarly, the SqlConnectionString is the connection string to your SQL Server database. The TRUNCATE TABLE command is used to clear the existing data in the table before importing the new data.

Conclusion

By following the steps outlined in this guide, you can easily import data from an Excel spreadsheet to your SQL Server database using C#. This method provides a powerful and efficient way to manage data, whether you have a small or large dataset. Ensuring that both the data and the database schema are correctly defined is key to a successful import operation.

Keywords

C# Excel Import, Data Import C#, Database Import Excel